Timestamp fileds into index

So I'm not the only person to see a bug like
this?

D.

-----Original Message-----
From: Ricardo J.C.Coelho [mailto:pulsar@truenet-ce.com.br]
Sent: Tuesday, February 09, 1999 8:36 AM
To: 'pgsql-hackers@postgresql.org'
Subject: [HACKERS] Timestamp fileds into index

Hi,

I am new in Pgsql-Hacker mailing list. I didn't have any answer from others
lists.

I tried to create a table with a timestamp field as part of primary key.
Pgsql doesn't have an "ops_name" for timestamp. You will see this when you
use create table. DON'T DO THIS WITH YOUR REGULAR DATABASE. Create a
separate one.
If you create the table without primary key and after create an unique
index with abstime_ops, everything will run well.
However if you use primary key clause, the table can't be dropped or
created again. Look the sequence above.

create table TBL (FLD1 int2, FLD2 timestamp, FLD3 text, primary
key(FLD1,FLD2));
--> Pgsql will not create because FLD2 is timestamp
create table TBL (FLD1 int2, FLD2 timestamp, FLD3 text);
--> Pgsql said: Relation TBL already exist.
drop table TBL;
--> Pgsql said: Relation TBL don't exist. (So strange).

I tried vacuum too, but TBL still was there. The only way was: dump
database, destroydb and createdb it again.

I looked into database files. TBL name appears in pg_type_typname_index,
pg_class_relname_index, pg_type.

Seems to me that PgSQL creates the table, try to create the index, but when
the problems occurs, the "rollback" of create table is not completed.

What do you think about this ? Is Hackers the right place to send this ?

I'm using RedHat 5.2 (Intel) with Pgsql 6.4.2

Thanks.

Ricardo Coelho.

I tried to create a table with a timestamp field as part of primary
key. Pgsql doesn't have an "ops_name" for timestamp. You will see this
when you use create table. DON'T DO THIS WITH YOUR REGULAR DATABASE.

I don't think this creates permanent damage; see below.

If you create the table without primary key and after create an unique
index with abstime_ops, everything will run well.
However if you use primary key clause, the table can't be dropped or
created again. Look the sequence above.

create table TBL (FLD1 int2, FLD2 timestamp, FLD3 text,
primary key(FLD1,FLD2));
--> Pgsql will not create because FLD2 is timestamp
create table TBL (FLD1 int2, FLD2 timestamp, FLD3 text);
--> Pgsql said: Relation TBL already exist.
drop table TBL;
--> Pgsql said: Relation TBL don't exist. (So strange).

postgres=> create table TBL (FLD1 int2, FLD2 timestamp, FLD3 text,
primary key(FLD1,FLD2));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index tbl_pkey
for table tbl
ERROR: Can't find a default operator class for type 1296.
postgres=> drop table tbl;
ERROR: Relation 'tbl' does not exist
postgres=> \q
golem$ psql
Welcome to the POSTGRESQL interactive sql monitor:
postgres=> drop table tbl;
ERROR: Relation 'tbl' does not exist
postgres=> create table TBL (FLD1 int2, FLD2 timestamp, FLD3 text);
CREATE
postgres=> drop table tbl;
DROP

I tried vacuum too, but TBL still was there. The only way was: dump
database, destroydb and createdb it again.

I think you just needed to exit your session and restart. See above.

Seems to me that PgSQL creates the table, try to create the index, but
when the problems occurs, the "rollback" of create table is not
completed. What do you think about this ?

Your analysis is probably correct.

Is Hackers the right place to send this ?

Yes.

- Tom