Re: Restore relhaspkey in PostgreSQL Version 11 Beta - Mailing list pgsql-general

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