Thread: BUG #18660: information_schema.columns.ordinal_position has gaps when primary key columns are dropped

The following bug has been logged on the website:

Bug reference:      18660
Logged by:          Lukas Eder
Email address:      lukas.eder@gmail.com
PostgreSQL version: 17.0
Operating system:   Linux (in Docker on Windows)
Description:

The problem can be illustrated with this simple reproducer script:

CREATE TABLE t (
  i1 INT NOT NULL,
  i2 INT NOT NULL,
  t TEXT NOT NULL
);

ALTER TABLE t ADD PRIMARY KEY (i1, i2);
ALTER TABLE t DROP COLUMN i1;
ALTER TABLE t ADD COLUMN i3 INT NOT NULL;

SELECT column_name, ordinal_position
FROM information_schema.columns
WHERE table_name = 't'
ORDER BY ordinal_position;

The query results in:

|column_name|ordinal_position|
|-----------|----------------|
|i2         |2               |
|t          |3               |
|i3         |4               |

This is against the SQL standard specification of the
information_schema.columns.ordinal_position column, which has a constraint
as follows:

CONSTRAINT COLUMNS_ORDINAL_POSITION_CONTIGUOUS_CHECK CHECK (0 = ALL (
  SELECT MAX(ORDINAL_POSITION) - COUNT(*)
  FROM COLUMNS
  GROUP BY
  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME 
))

I suspect this is because the internal pg_attribute.attnum leaks into the
information_schema.columns.ordinal_position:

SELECT a.attname, a.attnum
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
WHERE c.relname = 't'
AND attnum > 0
ORDER BY attnum;

Produces this:

|attname                     |attnum|
|----------------------------|------|
|........pg.dropped.1........|1     |
|i2                          |2     |
|t                           |3     |
|i3                          |4     |

PG Bug reporting form <noreply@postgresql.org> writes:
> [ information_schema.columns.ordinal_position is just a copy of attnum ]

> This is against the SQL standard specification of the
> information_schema.columns.ordinal_position column, which has a constraint
> as follows:

> CONSTRAINT COLUMNS_ORDINAL_POSITION_CONTIGUOUS_CHECK CHECK (0 = ALL (
>   SELECT MAX(ORDINAL_POSITION) - COUNT(*)
>   FROM COLUMNS
>   GROUP BY
>   TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME 
> ))

Hm.  I'm not sure if it's worth making that view even slower in order
to clean up the numbering.  Just as an aside, we'd still be violating
the letter of this constraint, because for a zero-column table the
sub-select will produce NULL not 0.

If we ever complete the fabled project to split up logical and
physical attnums, we would presumably be able to fix this without
slowing down the view, since we'd want it to report logical attnums
anyway.  I don't have high hopes for that happening though ...

            regards, tom lane

On 2024-10-17 16:19 +0200, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > [ information_schema.columns.ordinal_position is just a copy of attnum ]
> 
> > This is against the SQL standard specification of the
> > information_schema.columns.ordinal_position column, which has a constraint
> > as follows:
> 
> > CONSTRAINT COLUMNS_ORDINAL_POSITION_CONTIGUOUS_CHECK CHECK (0 = ALL (
> >   SELECT MAX(ORDINAL_POSITION) - COUNT(*)
> >   FROM COLUMNS
> >   GROUP BY
> >   TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME 
> > ))

Same for information_schema.attributes.ordinal_position, although I
don't know if that defines an equivalent constraint.

> Hm.  I'm not sure if it's worth making that view even slower in order
> to clean up the numbering.

But then we should at least fix the docs which say that the count starts
at 1.

> Just as an aside, we'd still be violating the letter of this
> constraint, because for a zero-column table the sub-select will
> produce NULL not 0.

I don't think so.  That sub-select only returns NULL when looking up
that empty table.  That entire CHECK expression with "ALL" will happily
return true.

    regress=# create table t0 ();
    CREATE TABLE
    regress=# select max(ordinal_position) - count(*) from information_schema.columns where table_name = 't0';
     ?column?
    ----------
       <NULL>
    (1 row)

    regress=# select 0 = all (select max(ordinal_position) - count(*) from information_schema.columns group by
table_catalog,table_schema, table_name);
 
     ?column?
    ----------
     t
    (1 row)

-- 
Erik