create index right after create table not working?

From: joost witteveen <joostje(at)co(dot)uea(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: create index right after create table not working?
Date: 2003-03-15 09:16:15
Message-ID: 20030315091615.GA15408@co.uea.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

It seems as though "CREATE INDEX" are failing if I give them right after
a CREATE TABLE, although they don't return any error messages.

DROP table tmp0;
CREATE TABLE tmp0(v0 varchar);

DROP index tmp0_v0_idx;
CREATE index tmp0_v0_idx ON tmp0(v0); --this one silently fails

INSERT INTO tmp0 VALUES('jwit');
SELECT val AS v0 FROM db, tmp0 WHERE tab='pers' AND tmp0.v0=id AND var='nomfam';

As the table db is rather large (300000 entries), the select takes
several seconds, which is usual if there is no index on table tmp0,
but, if I now do again:

DROP index tmp0_v0_idx; -- drop returns no error message
CREATE index tmp0_v0_idx ON tmp0(v0); -- this one now works!
SELECT val AS v0 FROM db, tmp0 WHERE tab='pers' AND tmp0.v0=id AND var='nomfam';

then the select returns instantly.

The strange thing is that if I do "\d tmp0", then I always see:

Table "tmp0"
Column | Type | Modifiers
--------+-------------------+-----------
v0 | character varying |
Indexes: tmp0_v0_idx

ie both after the first piece of code, and after the second, the index
seems to be there. Just the select takes ages in the first case.

Oh, and I notice that doing:

DROP table tmp0;
CREATE TABLE tmp0(v0 varchar);
DROP index tmp0_v0_idx;

INSERT INTO tmp0 VALUES('jwit'); --first insert, then
CREATE index tmp0_v0_idx ON tmp0(v0); --create (inverse of first example)

SELECT val AS v0 FROM db, tmp0 WHERE tab='pers' AND tmp0.v0=id AND var='nomfam';

ie. swapping the INSERT and CREATE index commands,
now the select returns instantly too.

Unfortunately this order is rather difficult for me to implement, and should
not be needed, or what?

Can CREATE index commands be issued right after the CREATE TABLE?

Postgresql: 7.2.1, debian release 3 and 2 (different computers).

Thanks,
joostje

Browse pgsql-sql by date

  From Date Subject
Next Message cliff 2003-03-15 15:38:20 help with table constraint / check
Previous Message Andrew McMillan 2003-03-15 07:57:12 Re: SERIAL does not ROLLBACK