X-Original-To: pgsql-sql@postgresql.org
Received: from co.uea.org (ip3e838ce6.speed.planet.nl [62.131.140.230])
	by postgresql.org (Postfix) with ESMTP id 815014758C9
	for <pgsql-sql@postgresql.org>; Sat, 15 Mar 2003 18:53:34 -0500 (EST)
Received: from co.uea.org (localhost [127.0.0.1])
	by co.uea.org (8.12.6/8.12.6/Debian-7) with ESMTP id h2FNrWvD032385;
	Sun, 16 Mar 2003 00:53:32 +0100
Received: (from joostje@localhost)
	by co.uea.org (8.12.6/8.12.6/Debian-7) id h2FNrWmx032384;
	Sun, 16 Mar 2003 00:53:32 +0100
Date: Sun, 16 Mar 2003 00:53:32 +0100
From: Joost Witteveen <joostje@komputilo.org>
To: pgsql-sql@postgresql.org
Subject: create index right after create table not working?
Message-ID: <20030315235332.GA32358@co.uea.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.4i
X-Archive-Number: 200303/377
X-Sequence-Number: 12416

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