Skip site navigation (1) Skip section navigation (2)

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

From: "Lawrence Cohan" <lawrencec(at)1shoppingcart(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #4238: pg_class.relhasindex not updated by vacuum
Date: 2008-06-13 16:08:12
Message-ID: D125F8AF679AEE4390F3A546AFFA5CB00331A3DA@hermes.1shoppingcart.lan (view raw or flat)
Thread:
Lists: 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 = 'foo'; 
-------------
 t
(1 row)

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

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

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

drop table foo;



Many thanks,
Lawrence Cohan.

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

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(at)sss(dot)pgh(dot)pa(dot)us] 
Sent: Friday, June 13, 2008 11:44 AM
To: Lawrence Cohan
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4238: pg_class.relhasindex not updated by
vacuum 

"Lawrence Cohan" <lawrencec(at)1shoppingcart(dot)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
Attention:
The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may 
contain confidential and/or privileged material.  Any review, retransmission, dissemination or other use of, or taking of any action in 
reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please 
contact the sender and delete the material from any system and destroy any copies.

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-06-13 16:32:38
Subject: Re: BUG #4238: pg_class.relhasindex not updated by vacuum
Previous:From: Lawrence CohanDate: 2008-06-13 15:56:32
Subject: Re: BUG #4238: pg_class.relhasindex not updated by vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group