From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: lukas.eder@gmail.com
Subject: BUG #18660: information_schema.columns.ordinal_position has gaps when primary key columns are dropped
Date: Thu, 17 Oct 2024 08:05:17 +0000
Message-ID: <18660-9ea9b58cce21a8bb@postgresql.org> (raw)
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 |
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, lukas.eder@gmail.com
Subject: Re: BUG #18660: information_schema.columns.ordinal_position has gaps when primary key columns are dropped
In-Reply-To: <18660-9ea9b58cce21a8bb@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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