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@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     Melvin Davidson <melvin6925@gmail.com <mailto:melvin6925@gmail.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@2ndquadrant.com


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

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com