Обсуждение: BUG #9606: pg_class relhaspkey column not updated on removal of primary key

The following bug has been logged on the website:

Bug reference:      9606
Logged by:          Jeff Frost
Email address:      jeff@pgexperts.com
PostgreSQL version: 9.2.7
Operating system:   Linux
Description:

pkey_test=# show server_version;
 server_version
----------------
 9.2.7
(1 row)

pkey_test=# create table foo ( bar serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "foo_bar_seq" for serial
column "foo.bar"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey"
for table "foo"
CREATE TABLE

pkey_test=# select relname, relhaspkey FROM pg_class where relname = 'foo';
 relname | relhaspkey
---------+------------
 foo     | t
(1 row)

pkey_test=# alter table foo drop constraint foo_pkey;
ALTER TABLE

pkey_test=# select relname, relhaspkey FROM pg_class where relname = 'foo';
 relname | relhaspkey
---------+------------
 foo     | t
(1 row)

jeff@pgexperts.com writes:
> [ $SUBJECT ]

This is not a bug; please read the description of pg_class:

relhaspkey    bool    True if the table has (or once had) a primary key

The note at the bottom of the page explains why:

Several of the Boolean flags in pg_class are maintained lazily: they are
guaranteed to be true if that's the correct state, but may not be reset to
false immediately when the condition is no longer true. For example,
relhasindex is set by CREATE INDEX, but it is never cleared by DROP
INDEX. Instead, VACUUM clears relhasindex if it finds the table has no
indexes. This arrangement avoids race conditions and improves concurrency.

            regards, tom lane

On Mar 17, 2014, at 11:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> jeff@pgexperts.com writes:
>> [ $SUBJECT ]
>
> This is not a bug; please read the description of pg_class:
>
> relhaspkey    bool    True if the table has (or once had) a primary key
>
> The note at the bottom of the page explains why:
>
> Several of the Boolean flags in pg_class are maintained lazily: they are
> guaranteed to be true if that's the correct state, but may not be reset to
> false immediately when the condition is no longer true. For example,
> relhasindex is set by CREATE INDEX, but it is never cleared by DROP
> INDEX. Instead, VACUUM clears relhasindex if it finds the table has no
> indexes. This arrangement avoids race conditions and improves concurrency.

Sure enough, you're right!

pkey_test=# select relname, relhaspkey FROM pg_class where relname = 'foo';
 relname | relhaspkey
---------+------------
 foo     | t
(1 row)

pkey_test=# vacuum foo;
VACUUM
pkey_test=# select relname, relhaspkey FROM pg_class where relname = 'foo';
 relname | relhaspkey
---------+------------
 foo     | f
(1 row)

Thanks, Tom, i had completely forgotten about that!

Jeff Frost <jeff@pgexperts.com> 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
relhaspkeyeven 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.

            regards, tom lane

On Mar 17, 2014, at 6:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Jeff Frost <jeff@pgexperts.com> 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:
>=20
> 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.
>=20

Yep, that appears to be the case:

pkey_test=3D# create table foo ( bar serial primary key, baz int);
NOTICE:  CREATE TABLE will create implicit sequence "foo_bar_seq" for =
serial column "foo.bar"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index =
"foo_pkey" for table "foo"
CREATE TABLE
pkey_test=3D# create index on foo(baz);
CREATE INDEX
pkey_test=3D# alter table foo drop constraint foo_pkey;
ALTER TABLE
pkey_test=3D# vacuum foo;
VACUUM
pkey_test=3D# select relname, relhaspkey FROM pg_class where relname =3D =
'foo';
 relname | relhaspkey
---------+------------
 foo     | t
(1 row)

pkey_test=3D# drop index foo_baz_idx ;
'DROP INDEX
pkey_test=3D# vacuum foo;
VACUUM
pkey_test=3D# select relname, relhaspkey FROM pg_class where relname =3D =
'foo';
 relname | relhaspkey
---------+------------
 foo     | f
(1 row)

And it's probably not worth the effort to change. =20

On Tue, Mar 18, 2014 at 12:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Jeff Frost <jeff@pgexperts.com> 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".

Regards,

Venkata Balaji N

Sr. Database Administrator
Fujitsu Australia

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

> tgl@.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.

David Johnston <polobo@yahoo.com> writes:
> 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.

AFAIR, the backend does not use relhaspkey at all.  (If it weren't for
client-compatibility worries, we'd probably have removed the field
altogether long ago.)

We do use relhasindex to know whether it's worth looking in pg_index or
not when collecting data about a table.  Thus the definition that
relhasindex *must* be true if there are indexes.  If it's true when there
are not indexes, though, we just waste one indexed search of pg_index
which is not a big deal.

I believe the killer reason why relhasindex is inaccurate in this way is
that if we didn't define it like that, concurrent CREATE INDEXes on the
same table couldn't work.  The update that sets relhasindex true is
nontransactional, meaning it won't roll back if an index creation fails;
but that's needed to avoid having concurrent CREATE INDEXes block each
other while trying to update the pg_class row.

            regards, tom lane