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

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

> Jeff Frost <jeff(at)pgexperts(dot)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.