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 |
