Thread: BUG #4238: pg_class.relhasindex not updated by vacuum

BUG #4238: pg_class.relhasindex not updated by vacuum

From
"Lawrence Cohan"
Date:
The following bug has been logged online:

Bug reference:      4238
Logged by:          Lawrence Cohan
Email address:      lawrencec@1shoppingcart.com
PostgreSQL version: 8.2.5
Operating system:   Linux 4.1.1
Description:        pg_class.relhasindex not updated by vacuum
Details:

We rely on this column to build a list of tables restricted to only those
that have indexes to be rebuilt with CONCURRENTLY however the column is not
updated as documentation says by the vacuum. After a successful
analyze/vacuum/analyze against the entire database ALL tables from pg_class
have the pg_class.relhasindex = true even if they don't have any indexes.

Re: BUG #4238: pg_class.relhasindex not updated by vacuum

From
Tom Lane
Date:
"Lawrence Cohan" <lawrencec@1shoppingcart.com> writes:
> We rely on this column to build a list of tables restricted to only those
> that have indexes to be rebuilt with CONCURRENTLY however the column is not
> updated as documentation says by the vacuum. After a successful
> analyze/vacuum/analyze against the entire database ALL tables from pg_class
> have the pg_class.relhasindex = true even if they don't have any indexes.

Works as documented for me ...

regression=# create table foo(f1 int);
CREATE TABLE
regression=# select relhasindex from pg_class where relname = 'foo';
 relhasindex
-------------
 f
(1 row)

regression=# create index fooi on foo(f1);
CREATE INDEX
regression=# select relhasindex from pg_class where relname = 'foo';
 relhasindex
-------------
 t
(1 row)

regression=# drop index fooi;
DROP INDEX
regression=# select relhasindex from pg_class where relname = 'foo';
 relhasindex
-------------
 t
(1 row)

regression=# vacuum foo;
VACUUM
regression=# select relhasindex from pg_class where relname = 'foo';
 relhasindex
-------------
 f
(1 row)

            regards, tom lane

Re: BUG #4238: pg_class.relhasindex not updated by vacuum

From
Tom Lane
Date:
"Lawrence Cohan" <lawrencec@1shoppingcart.com> writes:
> Is it possible that because of the PKEY's we have on the tables that
> flag is still showing "true"?

Uh, well certainly -- a PK is an index.

            regards, tom lane

Re: BUG #4238: pg_class.relhasindex not updated by vacuum

From
"Lawrence Cohan"
Date:
This is what's happening if we add the PK on the table and we were
expecting that ONLY if a user index like fooi was created the
relhasindex should be true.


create table foo(f1 int, id serial, CONSTRAINT foo_pkey PRIMARY KEY
(id));
select relhasindex from pg_class where relname =3D 'foo';=20
-------------
 t
(1 row)

create index fooi on foo(f1);=20
select relhasindex from pg_class where relname =3D 'foo';=20
-------------
 t
(1 row)

drop index fooi;
select relhasindex from pg_class where relname =3D 'foo';=20
-------------
 t
(1 row)

vacuum foo;
select relhasindex from pg_class where relname =3D 'foo';=20
-------------
 t
(1 row)

drop table foo;



Many thanks,
Lawrence Cohan.

-----Original Message-----
From: Lawrence Cohan=20
Sent: Friday, June 13, 2008 11:57 AM
To: 'Tom Lane'
Cc: pgsql-bugs@postgresql.org
Subject: RE: [BUGS] BUG #4238: pg_class.relhasindex not updated by
vacuum=20

Is it possible that because of the PKEY's we have on the tables that
flag is still showing "true"? In that case this is somewhat misleading
as the other flag relhaspkey from pg_class refers to the PK and its own
implicit index is not visible in PGADMIN UI for instance. The pg version
we are on in production is 8.2.5 not 8.3 yet.

Thanks,
Lawrence Cohan.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Friday, June 13, 2008 11:44 AM
To: Lawrence Cohan
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4238: pg_class.relhasindex not updated by
vacuum=20

"Lawrence Cohan" <lawrencec@1shoppingcart.com> writes:
> We rely on this column to build a list of tables restricted to only
those
> that have indexes to be rebuilt with CONCURRENTLY however the column
is not
> updated as documentation says by the vacuum. After a successful
> analyze/vacuum/analyze against the entire database ALL tables from
pg_class
> have the pg_class.relhasindex =3D true even if they don't have any
indexes.

Works as documented for me ...

regression=3D# create table foo(f1 int);
CREATE TABLE
regression=3D# select relhasindex from pg_class where relname =3D 'foo';
 relhasindex=20
-------------
 f
(1 row)

regression=3D# create index fooi on foo(f1);
CREATE INDEX
regression=3D# select relhasindex from pg_class where relname =3D 'foo';
 relhasindex=20
-------------
 t
(1 row)

regression=3D# drop index fooi;
DROP INDEX
regression=3D# select relhasindex from pg_class where relname =3D 'foo';
 relhasindex=20
-------------
 t
(1 row)

regression=3D# vacuum foo;
VACUUM
regression=3D# select relhasindex from pg_class where relname =3D 'foo';
 relhasindex=20
-------------
 f
(1 row)

            regards, tom lane
Attention:
The information contained in this message and or attachments is intended on=
ly for the person or entity to which it is addressed and may =0D
contain confidential and/or privileged material.  Any review, retransmissio=
n, dissemination or other use of, or taking of any action in =0D
reliance upon, this information by persons or entities other than the inten=
ded recipient is prohibited. If you received this in error, please =0D
contact the sender and delete the material from any system and destroy any =
copies.

Re: BUG #4238: pg_class.relhasindex not updated by vacuum

From
"Lawrence Cohan"
Date:
Is it possible that because of the PKEY's we have on the tables that
flag is still showing "true"? In that case this is somewhat misleading
as the other flag relhaspkey from pg_class refers to the PK and its own
implicit index is not visible in PGADMIN UI for instance. The pg version
we are on in production is 8.2.5 not 8.3 yet.

Thanks,
Lawrence Cohan.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Friday, June 13, 2008 11:44 AM
To: Lawrence Cohan
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4238: pg_class.relhasindex not updated by
vacuum=20

"Lawrence Cohan" <lawrencec@1shoppingcart.com> writes:
> We rely on this column to build a list of tables restricted to only
those
> that have indexes to be rebuilt with CONCURRENTLY however the column
is not
> updated as documentation says by the vacuum. After a successful
> analyze/vacuum/analyze against the entire database ALL tables from
pg_class
> have the pg_class.relhasindex =3D true even if they don't have any
indexes.

Works as documented for me ...

regression=3D# create table foo(f1 int);
CREATE TABLE
regression=3D# select relhasindex from pg_class where relname =3D 'foo';
 relhasindex=20
-------------
 f
(1 row)

regression=3D# create index fooi on foo(f1);
CREATE INDEX
regression=3D# select relhasindex from pg_class where relname =3D 'foo';
 relhasindex=20
-------------
 t
(1 row)

regression=3D# drop index fooi;
DROP INDEX
regression=3D# select relhasindex from pg_class where relname =3D 'foo';
 relhasindex=20
-------------
 t
(1 row)

regression=3D# vacuum foo;
VACUUM
regression=3D# select relhasindex from pg_class where relname =3D 'foo';
 relhasindex=20
-------------
 f
(1 row)

            regards, tom lane
Attention:
The information contained in this message and or attachments is intended on=
ly for the person or entity to which it is addressed and may =0D
contain confidential and/or privileged material.  Any review, retransmissio=
n, dissemination or other use of, or taking of any action in =0D
reliance upon, this information by persons or entities other than the inten=
ded recipient is prohibited. If you received this in error, please =0D
contact the sender and delete the material from any system and destroy any =
copies.

Re: BUG #4238: pg_class.relhasindex not updated by vacuum

From
"Lawrence Cohan"
Date:
Isn't a PK a CONSTRAINT and not an INDEX???
Some say "one or more fields" and others "one or more attributes" that
uniquely identifies a record in a table and PG like many other databases
would create a default internal index on that CONSTRAINT that can't be
seen or dropped unless you will drop the Pkey.
In that case the two separate pg_class relhasindex and relhaspkey would
make sense indeed - just a thought nothing else and we'll take it as is.

Best regards,
Lawrence Cohan.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Friday, June 13, 2008 12:33 PM
To: Lawrence Cohan
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4238: pg_class.relhasindex not updated by
vacuum=20

"Lawrence Cohan" <lawrencec@1shoppingcart.com> writes:
> Is it possible that because of the PKEY's we have on the tables that
> flag is still showing "true"?

Uh, well certainly -- a PK is an index.

            regards, tom lane

Re: BUG #4238: pg_class.relhasindex not updated by vacuum

From
Alvaro Herrera
Date:
Lawrence Cohan wrote:
> Isn't a PK a CONSTRAINT and not an INDEX???

Sure, from a logical point of view.  The implementation of that
constraint is an index.

> In that case the two separate pg_class relhasindex and relhaspkey would
> make sense indeed - just a thought nothing else and we'll take it as is.

What would be the point?  If you want to figure out whether a table has
a primary key, you can query the catalogs.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: BUG #4238: pg_class.relhasindex not updated by vacuum

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Lawrence Cohan wrote:
>> In that case the two separate pg_class relhasindex and relhaspkey would
>> make sense indeed - just a thought nothing else and we'll take it as is.

> What would be the point?  If you want to figure out whether a table has
> a primary key, you can query the catalogs.

Note that UNIQUE constraints are implemented by indexes too, so it's not
clear to me that special-casing the pkey would really respond to this
criticism anyway.

But the bottom line is that relhasindex is defined in terms of
possessing physical indexes, not whether those indexes arose from
constraint syntax or CREATE INDEX.

            regards, tom lane

Re: BUG #4238: pg_class.relhasindex not updated by vacuum

From
"Lawrence Cohan"
Date:
Many thanks again. I figured out how to get only the tables that have
indexes created less these PK indexes so I can used the pg_get_indexdef
to rebuild them all through a scheduled Pgagent job in a loop using
CONCURRENTLY as our production assumes DB access 24/7.

Lawrence Cohan.

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]=20
Sent: Friday, June 13, 2008 12:57 PM
To: Lawrence Cohan
Cc: Tom Lane; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4238: pg_class.relhasindex not updated by
vacuum

Lawrence Cohan wrote:
> Isn't a PK a CONSTRAINT and not an INDEX???

Sure, from a logical point of view.  The implementation of that
constraint is an index.

> In that case the two separate pg_class relhasindex and relhaspkey
would
> make sense indeed - just a thought nothing else and we'll take it as
is.

What would be the point?  If you want to figure out whether a table has
a primary key, you can query the catalogs.

--=20
Alvaro Herrera
http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: BUG #4238: pg_class.relhasindex not updated by vacuum

From
"Lawrence Cohan"
Date:
It is not criticism but only my own thought and PG is a really great
database!!! It was all perhaps due to my poor understanding of
relhasindex from pg_class internal catalog and I understand and respect
your view.

Best regards,
Lawrence Cohan.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Friday, June 13, 2008 1:13 PM
To: Alvaro Herrera
Cc: Lawrence Cohan; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4238: pg_class.relhasindex not updated by
vacuum=20

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Lawrence Cohan wrote:
>> In that case the two separate pg_class relhasindex and relhaspkey
would
>> make sense indeed - just a thought nothing else and we'll take it as
is.

> What would be the point?  If you want to figure out whether a table
has
> a primary key, you can query the catalogs.

Note that UNIQUE constraints are implemented by indexes too, so it's not
clear to me that special-casing the pkey would really respond to this
criticism anyway.

But the bottom line is that relhasindex is defined in terms of
possessing physical indexes, not whether those indexes arose from
constraint syntax or CREATE INDEX.

            regards, tom lane
Attention:
The information contained in this message and or attachments is intended on=
ly for the person or entity to which it is addressed and may =0D
contain confidential and/or privileged material.  Any review, retransmissio=
n, dissemination or other use of, or taking of any action in =0D
reliance upon, this information by persons or entities other than the inten=
ded recipient is prohibited. If you received this in error, please =0D
contact the sender and delete the material from any system and destroy any =
copies.