pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
BUG #18660: information_schema.columns.ordinal_position has gaps when primary key columns are dropped 3+ messages / 3 participants [nested] [flat]
* BUG #18660: information_schema.columns.ordinal_position has gaps when primary key columns are dropped @ 2024-10-17 08:05 PG Bug reporting form <noreply@postgresql.org> 0 siblings, 1 reply; 3+ messages in thread From: PG Bug reporting form @ 2024-10-17 08:05 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org; +Cc: lukas.eder@gmail.com 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 | ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18660: information_schema.columns.ordinal_position has gaps when primary key columns are dropped @ 2024-10-17 14:19 Tom Lane <tgl@sss.pgh.pa.us> parent: PG Bug reporting form <noreply@postgresql.org> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2024-10-17 14:19 UTC (permalink / raw) To: lukas.eder@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18660: information_schema.columns.ordinal_position has gaps when primary key columns are dropped @ 2024-10-17 15:02 Erik Wienhold <ewie@ewie.name> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Erik Wienhold @ 2024-10-17 15:02 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: lukas.eder@gmail.com; pgsql-bugs@lists.postgresql.org 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-10-17 15:02 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-10-17 08:05 BUG #18660: information_schema.columns.ordinal_position has gaps when primary key columns are dropped PG Bug reporting form <noreply@postgresql.org> 2024-10-17 14:19 ` Tom Lane <tgl@sss.pgh.pa.us> 2024-10-17 15:02 ` Erik Wienhold <ewie@ewie.name>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox