Re: BUG #4238: pg_class.relhasindex not updated by vacuum - Mailing list pgsql-bugs

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.