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.

Even restarting the DB cluster doesn't help.

postgres=# create table t ( a int );
CREATE TABLE
postgres=# select relhasindex from pg_class where relname = 't';
relhasindex
-------------
f
(1 row)

postgres=# begin;
BEGIN
postgres=# create index ind_t_a on t(a);
CREATE INDEX
postgres=# select relhasindex from pg_class where relname = 't';
relhasindex
-------------
t
(1 row)

postgres=# \d t
Table "public.t"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
Indexes:
"ind_t_a" btree (a)

postgres=# rollback;
ROLLBACK
postgres=# \d t
Table "public.t"
Column | Type | Modifiers
--------+---------+-----------
a | integer |

postgres=# select relhasindex from pg_class where relname = 't';
relhasindex
-------------
t
(1 row)

postgres=#

Best regards,

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"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:

relhasindex
True if this is a table and it has (or recently had) any indexes.
This is set by CREATE INDEX, but not cleared immediately by DROP
INDEX. 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thanks for the references.

Note for future: should have tried VACUUM too.

On 11/7/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "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:
>
> relhasindex
> True if this is a table and it has (or recently had) any indexes.
> This is set by CREATE INDEX, but not cleared immediately by DROP
> INDEX. 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
>



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