Thread: BUG #9606: pg_class relhaspkey column not updated on removal of primary key
BUG #9606: pg_class relhaspkey column not updated on removal of primary key
From
jeff@pgexperts.com
Date:
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
Re: BUG #9606: pg_class relhaspkey column not updated on removal of primary key
From
Jeff Frost
Date:
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!
Re: BUG #9606: pg_class relhaspkey column not updated on removal of primary key
From
Jeff Frost
Date:
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
Re: BUG #9606: pg_class relhaspkey column not updated on removal of primary key
From
Jeff Frost
Date:
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
Re: BUG #9606: pg_class relhaspkey column not updated on removal of primary key
From
Venkata Balaji Nagothi
Date:
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
Re: BUG #9606: pg_class relhaspkey column not updated on removal of primary key
From
David Johnston
Date:
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.
Re: Re: BUG #9606: pg_class relhaspkey column not updated on removal of primary key
From
Tom Lane
Date:
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