Обсуждение: Found a bug

Hi All,<br /><br />    Please refer the following session snippet. It seems that creating and dropping an index on a
table,within a transaction, leaves the table marked as having an index. Although, I don't think it's a serious bug,
sincewe always retrieve the list using RelationGetIndexList(), and that list turns out to be empty; but, if possible we
shouldtrack it down. <br /><br />    Even restarting the DB cluster doesn't help.<br /><br /><span style="font-family:
couriernew,monospace;">postgres=# create table t ( a int );</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> CREATE TABLE</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;">postgres=# select relhasindex from pg_class where relname =
't';</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> relhasindex</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">-------------</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> f</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">(1row)</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">postgres=# begin;</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;">BEGIN</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">postgres=# create index ind_t_a on t(a);</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">CREATE INDEX</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">postgres=# select
relhasindexfrom pg_class where relname = 't';</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> relhasindex </span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">-------------</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">  t</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">(1 row)</span><br style="font-family: courier new,monospace;" /><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> postgres=# \d
t</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">       Table
"public.t"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">
 Column|  Type   | Modifiers</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">--------+---------+-----------</span><brstyle="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">  a      | integer |</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;">Indexes:</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;">     "ind_t_a" btree (a)</span><br style="font-family: courier
new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">postgres=#rollback;</span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;">ROLLBACK</span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;">postgres=# \d t</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">       Table "public.t"</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> Column |  Type   | Modifiers</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier
new,monospace;">--------+---------+-----------</span><brstyle="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">  a      | integer |</span><br style="font-family: courier new,monospace;"
/><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">postgres=# select
relhasindexfrom pg_class where relname = 't'; </span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> relhasindex</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> -------------</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> t</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> (1 row)</span><br style="font-family: courier new,monospace;" /><br
style="font-family:courier new,monospace;" /><span style="font-family: courier
new,monospace;">postgres=#                                 <br /><br />Best regards, <br clear="all" /></span><br />--
<br/>gurjeet[.singh]@EnterpriseDB.com<br />singh.gurjeet@{ gmail | hotmail | yahoo }.com<br /><br /> 

Gurjeet Singh wrote:
> Hi All,
> 
>    Please refer the following session snippet. It seems that creating and
> dropping an index on a table, within a transaction, leaves the table marked
> as having an index. Although, I don't think it's a serious bug, since we
> always retrieve the list using RelationGetIndexList(), and that list turns
> out to be empty; but, if possible we should track it down.

Can't reproduce it here:

richardh=# create table t (a int);
CREATE TABLE
richardh=# begin;
BEGIN
richardh=# create index t_idx on t (a);
CREATE INDEX
richardh=# select relhasindex from pg_class where relname = 't'; relhasindex
------------- t
(1 row)

richardh=# rollback;
ROLLBACK
richardh=# select relhasindex from pg_class where relname = 't'; relhasindex
------------- f

PostgreSQL 8.1.3 on i586-pc-linux-gnu

--   Richard Huxton  Archonet Ltd

"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
>     Please refer the following session snippet. It seems that creating and
> dropping an index on a table, within a transaction, leaves the table marked
> as having an index.

This isn't a bug.  Refer to catalogs.sgml:

relhasindexTrue if this is a table and it has (or recently had) any indexes.This is set by CREATE INDEX, but not
clearedimmediately by DROPINDEX. VACUUM clears relhasindex if it finds the table has no indexes. 
 

The flag is only used as a hint that it's worth looking in pg_index to
see what rows there are for the table.

As for "tracking it down", read index_drop().
        regards, tom lane

Thanks for the references.

Note for future: should have tried VACUUM too.




--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com