Index problem with Timestamp fields - Mailing list pgsql-general

From Ricardo J.C.Coelho
Subject Index problem with Timestamp fields
Date
Msg-id 01BE5099.36F692A0.pulsar@truenet-ce.com.br
Whole thread Raw
List pgsql-general
Hi,

I am new in Pgsql mailing list. If here isn't the right place, please
redirect me.

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, 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 tmestamp
    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, destroy and create 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 it 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 ?

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

Thanks.

Ricardo Coelho.

pgsql-general by date:

Previous
From: "Ricardo J.C.Coelho"
Date:
Subject: Postmaster memory problem
Next
From: "Ricardo J.C.Coelho"
Date:
Subject: Postmaster memory problem