From: Tom Lane <tgl@sss.pgh.pa.us>
To: lukas.eder@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18660: information_schema.columns.ordinal_position has gaps
 when primary key columns are dropped
In-reply-to: <18660-9ea9b58cce21a8bb@postgresql.org>
References: <18660-9ea9b58cce21a8bb@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Thu, 17 Oct 2024 08:05:17 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <650058.1729174755.1@sss.pgh.pa.us>
Date: Thu, 17 Oct 2024 10:19:15 -0400
Message-ID: <650059.1729174755@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/650059.1729174755%40sss.pgh.pa.us>
Precedence: bulk

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