Thread: Restore relhaspkey in PostgreSQL Version 11 Beta

Melvin Davidson <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

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

Hi,

On 2018-07-30 17:21:25 -0400, Melvin Davidson wrote:
> * >it has never been the case that relhaspkey meant that the table
> *currently* has a primary key. *

> *Hmmm, I guess it's a lot harder to fix "squishy semantics"from      "True
> if the table has (or once had) a primary key"  to    "True if the table has
> a primary key after vacuum"rather than just dropping a column that has
> existed from version 7.2.So now I guess the policy is break code instead of
> fix documention.That meakes sense...NOT!*

A large portion of the system catalogs (i.e. objects within
pg_catalog.*) are essentially internal implementation details and we'll
change them if it makes our live easier. If you want stability use
information_schema which we'll try very hard to not ever break.  Keeping
random atavistic things around, would slow us down, which will be a
price everybody is paying.

Greetings,

Andres Freund

On 07/30/2018 04:11 PM, Melvin Davidson wrote:
> 
> 
> On Mon, Jul 30, 2018 at 6:21 PM, Andres Freund <andres@anarazel.de 
> <mailto:andres@anarazel.de>> wrote:
> 
>     Hi,
> 
>     On 2018-07-30 17:21:25 -0400, Melvin Davidson wrote:
>      > * >it has never been the case that relhaspkey meant that the table
>      > *currently* has a primary key. *
> 
>      > *Hmmm, I guess it's a lot harder to fix "squishy semantics"from 
>          "True
>     > if the table has (or once had) a primary key"  to    "True if the table has
>     > a primary key after vacuum"rather than just dropping a column that has
>      > existed from version 7.2.So <http://7.2.So> now I guess the
>     policy is break code instead of
>      > fix documention.That meakes sense...NOT!*
> 
>     A large portion of the system catalogs (i.e. objects within
>     pg_catalog.*) are essentially internal implementation details and we'll
>     change them if it makes our live easier. If you want stability use
>     information_schema which we'll try very hard to not ever break.  Keeping
>     random atavistic things around, would slow us down, which will be a
>     price everybody is paying.
> 
>     Greetings,
> 
>     Andres Freund
> 
> 
> *> If you want stability use information_schema which we'll try very 
> hard to not ever break.
> *
> *Of course. Would you be so kind as to point out where in the 
> information_schema it
> *
> *indicates if a table has a primary key or not. Oh wait, now I 
> remember...no place.*

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

"constraint_type     character_data     Type of the constraint: CHECK, FOREIGN 
KEY, PRIMARY KEY, or UNIQUE"

> *
> *
> *>Keeping random atavistic things around, would slow us down, which will 
> be a
>  >price everybody is paying.
> *
> *Random atavistic things? I hardly think relhaspkey is random. It's been 
> there since version 7.2.*
> *Exactly how does keeping it around slow you/us down?
> *
> 
> 
> -- 
> *Melvin Davidson**
> Maj. Database & Exploration Specialist**
> Universe Exploration Command – UXC***
> Employment by invitation only!


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

On 2018-07-30 19:11:34 -0400, Melvin Davidson wrote:
> *Of course. Would you be so kind as to point out where in the
> information_schema  it *
> *indicates if a table has a primary key or not. Oh wait, now I
> remember...no place.*

As Adrian pointed out, that's wrong. It's in information_schema.  You're
pretty damn antagonistic while asking for things.


> *>Keeping random atavistic things around, would slow us down, which will be
> a>price everybody is paying. *

> *Random atavistic things? I hardly think relhaspkey is random. It's been
> there since version 7.2.*
> *Exactly how does keeping it around slow you/us down?*

Being old doesn't imply it's not superfluous and/or slows us
down. There've been a number of discussions and bug reports about the
inaccuracy - even though it's documented! - it in the last few
years. That alone costs time. Additionally it's code we need to
maintain.

Greetings,

Andres Freund

On 31 July 2018 at 11:11, Melvin Davidson <melvin6925@gmail.com> wrote:
>> If you want stability use information_schema which we'll try very hard to
>> not ever break.
> Of course. Would you be so kind as to point out where in the
> information_schema  it
> indicates if a table has a primary key or not. Oh wait, now I remember...no
> place.

With all due respect Sir, you're making a fool of yourself here.  I'd
suggest that before you debate or argue with people that you ensure
that you're correct. This can often be hard to do on the spot, but
excuses dwindle a bit more when the communication is asynchronous via
email.

It's not that difficult to find information_schema.table_constraints
and see that constraint_type has "PRIMARY KEY"

>>Keeping random atavistic things around, would slow us down, which will be a
>>price everybody is paying.
> Random atavistic things? I hardly think relhaspkey is random. It's been
> there since version 7.2.
> Exactly how does keeping it around slow you/us down?

Well, it's been known that some people misuse relhaspkey. For example,
in [1], someone is recommending to use relhaspkey to check for tables
which don't have a PRIMARY KEY constraint.  This was the wrong advice
as the flag could remain set after the primary key was dropped from
the table and before any vacuum took place on the table. The correct
advice should have been the same as what Tom mentioned above, by
checking for a pg_index record for the table with indisprimary as
true.  Alternatively, another useful response would have been to check
information_schema, which would have provided an SQL standard way to
check.

Now, in regards to [1]. I rather hope that you can sympathize with the
decision to remove the column a little as the person who made the
incorrect recommendation in [1] was none other than you yourself.  So
it seems that you've only assisted in contributing to the columns
removal by not only misusing it yourself but also instructing others,
publically to do the same.

Now, in regards to your general tone here. It appears you're under the
assumption that the column was removed for some malicious reason in
order to break people's scripts, but I can assure you, there was no
malicious intent involved. However, the column *was* removed exactly
in order to break queries.  The reason being it was most likely the
queries were already broken and we deemed the problem big enough to
remove the misleading column in order to let people know their queries
were broken.  Your argument to put the column back carries very little
weight, as it appears your script is trying to determine which tables
have no primary key incorrectly. So I'd recommend that, instead of
expending some keystrokes in replying to this email, that instead, you
spend them fixing your broken code. Tom has kindly given you a very
good starting point too.

Personally, if I had been using a query like yours, I'd be thanking
Peter for highlighting it was broken for me.

If you'd like something else to read, please also look at [2]. I
imagine this is the sort of thing that Andres is talking about.

[1] https://www.postgresql.org/message-id/CANu8FiyQsQg7bF3FPT+FU=kK=WJHfewPp+6qE9fxF6YXr+WNCA@mail.gmail.com
[2] https://www.postgresql.org/message-id/flat/20140317185255.20724.49675%40wrigleys.postgresql.org

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 07/31/2018 07:47 AM, Melvin Davidson wrote:
> 
>     [2]
>     https://www.postgresql.org/message-id/flat/20140317185255.20724.49675%40wrigleys.postgresql.org
>     <https://www.postgresql.org/message-id/flat/20140317185255.20724.49675%40wrigleys.postgresql.org>
> 
>     -- 
>       David Rowley http://www.2ndQuadrant.com/
>       PostgreSQL Development, 24x7 Support, Training & Services
> 
> 
>  > 
> https://www.postgresql.org/message-id/flat/20140317185255.20724.49675%40wrigleys.postgresql.org 
> <https://www.postgresql.org/message-id/flat/20140317185255.20724.49675%40wrigleys.postgresql.org> 
> 
> 
> *Wow, so BUG #9606 has been known since 2014-03-17, but no one has 
> bothered to fix it?
> As I've said before, instead of fixing the problem, the resolution seems 
> to be to
> "make it go away". I've seen that logic echoed in other situations 
> outside of the
> IT environment.
> The fact remains, my code works (or worked) because I was only 
> interested in finding
> tables that were newly created without a primary key. While I 
> acknowledge that bug, the
> situation where the primary key is dropped is extremely rare and would 
> only happen
> in the rare case where the primary key needed to be altered or replaced 
> by another
> key, in which case the problem is moot. Since PostgreSQL is a relational 
> database,
> I cannot think of a situation where a DBA would allow the primary key to 
> just be
> dropped/removed altogether.
> I was hoping that at least one other person would see my point of view, 
> but by the
> harsh replies I've been getting, I feel more like a whistle blower that 
> insiders
> think I also should be made to "go away".

That is not the case. You may not like the explanations that where 
provided, that is your right, but the decision has been made. 
Alternatives where provided so there is a migration path. People are 
just questioning why you are getting so worked up over what is a 
relatively minor change. This is nowhere near a disruptive change as say 
the implicit cast changes in 8.3. I for one do not want you to 'go away'.

> Well, you are right. This old Viet Vet shall now end this conversation 
> and his career.
> I just need a way to do so quietly and painlessly.
> The truth is absolute and cannot be changed.
> Perception is not the truth.
> Flerp!*
> *
> *
> **--
> *Melvin Davidson**
> Maj. Database & Exploration Specialist**
> Universe Exploration Command – UXC***
> Employment by invitation only!


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Jul 31, 2018 at 7:47 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
> I was hoping that at least one other person would see my point of view, but
> by the
> harsh replies I've been getting, I feel more like a whistle blower that
> insiders
> think I also should be made to "go away".

You were bellicose from almost the beginning of this thread. And, yes,
that does detract from your argument. Just as it would in almost any
other sphere or arena.

> Well, you are right. This old Viet Vet shall now end this conversation and
> his career.
> I just need a way to do so quietly and painlessly.
> The truth is absolute and cannot be changed.
> Perception is not the truth.
> Flerp!

I cannot imagine what reaction you were expecting to this. In all
sincerity, I suggest reflecting on your words. You don't seem to have
realistic expectations about how the community works, or could ever
work.

-- 
Peter Geoghegan