create index right after create table not working?

Поиск
Список
Период
Сортировка
От Joost Witteveen
Тема create index right after create table not working?
Дата
Msg-id 20030315235332.GA32358@co.uea.org
обсуждение исходный текст
Ответы Re: create index right after create table not working?  (Manfred Koizar <mkoi-pg@aon.at>)
Список 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: "Jordan S. Jones"
Дата:
Сообщение: Retrieving Definition for Composite Type
Следующее
От: "Objectz"
Дата:
Сообщение: String aggregate function