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

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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!

On Mar 17, 2014, at 12:01 PM, Jeff Frost <jeff@pgexperts.com> wrote:

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

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!

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:

\d output way after the pkey and associated column were dropped:

Table "public.resources"
Column | Type | Modifiers
------------+---------+-----------
id | bigint |
project_id | bigint |
name | text |
source_uid | integer |
old_id | bigint |
new_id | bigint |
Indexes:
"index_resources_on_id" btree (id)
"index_resources_on_project_id" btree (project_id)

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

VACUUM resources;

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

Unfortunately, I can't seem to reproduce that behavior with a test case.

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.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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:

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.

Yep, that appears to be the case:

pkey_test=# 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=# create index on foo(baz);
CREATE INDEX
pkey_test=# alter table foo drop constraint foo_pkey;
ALTER TABLE
pkey_test=# vacuum foo;
VACUUM
pkey_test=# select relname, relhaspkey FROM pg_class where relname = 'foo';
relname | relhaspkey
---------+------------
foo | t
(1 row)

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

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

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 &lt;

tgl@.pa

&gt; wrote:

Jeff Frost &lt;

jeff@

&gt; 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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs