From: | "Thomas G(dot) Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu> |
---|---|
To: | "Ricardo J(dot)C(dot)Coelho" <pulsar(at)truenet-ce(dot)com(dot)br> |
Cc: | "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [HACKERS] Timestamp fileds into index |
Date: | 1999-02-09 15:20:02 |
Message-ID: | 36C05222.C23DD756@alumni.caltech.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas G. Lockhart | 1999-02-09 15:52:47 | Re: RES: [HACKERS] Timestamp fileds into index |
Previous Message | Chris Williams | 1999-02-09 15:14:30 | Error dropping indexes |