Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Peter Geoghegan]

On Tue, Jul 31, 2018 at 7:47 AM, Melvin Davidson  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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Adrian Klaver]

On 07/31/2018 07:47 AM, Melvin Davidson wrote:


[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


 > 
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.kla...@aklaver.com

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Melvin Davidson]

> [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
>

> 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". 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!

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[David Rowley]

On 31 July 2018 at 11:11, Melvin Davidson  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+w...@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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[David G. Johnston]

On Mon, Jul 30, 2018 at 4:11 PM, Melvin Davidson 
wrote:

> *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?*
>

My recap of the discussion thread:
That this has been around for a long time is not new information that was
unknown at the time the decision was made.  It was made in spite of that
piece of evidence.  It was decided that the leaving a "foot-gun" around for
new people to use was a problem worthy of solving. It this situation I find
the decision (given that no one is willing to make the field work as named)
to be the correct one (by a not-wide margin).

David J.

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Andres Freund]

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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Adrian Klaver]

On 07/30/2018 04:11 PM, Melvin Davidson wrote:



On Mon, Jul 30, 2018 at 6:21 PM, Andres Freund > 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  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.kla...@aklaver.com

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Melvin Davidson]

On Mon, Jul 30, 2018 at 6:21 PM, Andres Freund  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 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.*



*>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!

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Andres Freund]

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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Melvin Davidson]

* >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!*


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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Adrian Klaver]

On 07/30/2018 07:42 AM, Melvin Davidson wrote:



On Mon, Jul 30, 2018 at 10:31 AM, Tom Lane > wrote:


Melvin Davidson mailto:melvin6...@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-0704f0ed6...@2ndquadrant.com







--
*Melvin Davidson**



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



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Melvin Davidson]

On Mon, Jul 30, 2018 at 10:31 AM, Tom Lane  wrote:

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




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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

[Tom Lane]

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

Restore relhaspkey in PostgreSQL Version 11 Beta

[Melvin Davidson]

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.
IE: SELECT n.nspname,
   c.relname as table,
   c.reltuples::bigint
  FROM pg_class c
  JOIN pg_namespace n ON (n.oid =c.relnamespace )
 WHERE relkind = 'r' AND
   relname NOT LIKE 'pg_%' AND
   relname NOT LIKE 'sql_%' AND
   relhaspkey = FALSE
ORDER BY n.nspname, c.relname;

relhaspkey has been in pg_class since the earliest version of PostgreSQL.
AFAIK
there is NO NEED to remove it! In fact, the system catalogs should only be
changed when there is an absolute requirement, not at someone's whim.
Adding a column is fine, but dropping columns that breaks code is
ridiculous.

Please restore that column before the final release!

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