> 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