Re: Restore relhaspkey in PostgreSQL Version 11 Beta

On 07/30/2018 07:42 AM, Melvin Davidson wrote:
>
>
> On Mon, Jul 30, 2018 at 10:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Melvin Davidson <melvin6925(at)gmail(dot)com <mailto:melvin6925(at)gmail(dot)com>>
> writes:
> > In the release notes for Version 11 Beta, under changes, I see these scary
> > remarks:
> > Remove relhaspkey column from system table pg_class (Peter Eisentraut)
> > Applications needing to check for a primary key should consult pg_index.
> >
> > That absolutely breaks my code (and I'm guessing others), as I have a cron
> > job that checks for tables that were created with no pkey.
>
> Well, I'd say your code was broken anyway, because it has never been the
> case that relhaspkey meant that the table *currently* has a primary key.
> We got rid of it on the grounds that its semantics were too squishy to
> be useful.
>
> What you want is something like
>
> select relname from pg_class c where relkind = 'r' and
>   not exists (select 1 from pg_index where indrelid = c.oid and
> indisprimary);
>
> which will give the right answer in all PG versions.
>
>                         regards, tom lane
>
>
> it has never been the
> case that relhaspkey meant that the table *currently* has a primary key.
>
> Tom,
> *
> *
> *>it has never been the case that relhaspkey meant that the table
> *currently* has a primary key.
> *
> *
> *
> *That is a poor excuse, because that is exactly what I am looking for!*
> *squishy semantics or not, dropping columns from system catalogs is
> ridiculous.
> *
> *It appears to me that the developers are going rogue. Why should I, and
> others,
> *
> *I have to change my code ( which absolutely works ), simply because the
> developers*
> *feel it's ok to drop columns from system catalogs based on semantics?*

Use the information_schema then:

https://www.postgresql.org/docs/10/static/infoschema-table-constraints.html

The system catalogs are going to change over time by addition and/or
subtraction. That is a fact of life.

If you are interested in the reasons for the change then:

https://www.postgresql.org/message-id/flat/b1a24c6c-6913-f89c-674e-0704f0ed69db(at)2ndquadrant(dot)com

>
>
>
>
> --
> *Melvin Davidson**

> Maj. Database & Exploration Specialist**
> Universe Exploration Command – UXC***
> Employment by invitation only!

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com