Re: BUG #9606: pg_class relhaspkey column not updated on removal of primary key

Venkata Balaji Nagothi wrote
> On Tue, Mar 18, 2014 at 12:21 PM, Tom Lane <

> tgl(at)(dot)pa

> > wrote:
>
>> Jeff Frost <

> jeff@

> > writes:
>> > Interestingly, on 9.1.11, I have a table where the pkey was added after
>> the fact, then dropped, but it still shows as relhaspkey even though I
>> manually vacuumed it:
>>
>> IIRC, VACUUM only clears relhaspkey if there are *no* indexes left --- it
>> doesn't bother to check whether there's one calling itself indisprimary.
>> We could possibly change that but it's not clear that it's worth any
>> effort, given that the column would still have to be defined the same
>> way.
>
>
> Apologies if i am jumping into the conversation.
>
> Technically, there is no harm or it does not make any big impact if
> "relhaspkey" column shows "t" even after the column has primary key
> constraint disabled.
>
> Logically, the information in pg_class table can be mis-leading when the
> column is not behaving like a traditional "primary key column". I think,
> It
> is important that, the information showing up in the "relhaspkey" column
> must be based on the "primary key constraint" existence rather than the
> "Index existence".

The field in question is a table flag. If you really care you have to query
the actual indexes for the current reality. Unless it is false, it seems,
in which case you can skip the check.

As to Tom's "defined the same way" - does it truly mean "has index" or is it
"has unique index defined as primary"? I ask because depending on how the
data is being used it may be worth it to a caller to force the flag to be
the correct value to avoid subsequent checks. This only works when you
force it to false since adding an index will make it true while removing one
will go unnoticed. But if the definition doesn't change even if you can
reset the flag while non-primary indexes are present then I am confused as
to why.

At the moment it's a solution waiting for a problem...and removing a primary
index would seem to be infrequent enough to not worry about.

It could be fixed but doing so for the sake of information perfection is a
tough sell to others. Causing a performance regression makes it that much
tougher. Thus someone with a pressing need and an elegant solution is
needed to get this changed. Imperfect data in time is better than perfect
data too late.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9606-pg-class-relhaspkey-column-not-updated-on-removal-of-primary-key-tp5796409p5796526.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.