Обсуждение: Recreating indices safely
Hello, I have quite strange problem with 7.1.3. The problem is that when I try to recreate indices in working system (it is needed as updates are quite intensive, and you need to make indices smaller) I realise that some queries using these indices are failed. They are failed with error something like can not find relation <oid>. This is understandable. Query was prepared, all oids was fixed, and between query prepare, and execution I drop the index (I create a new one, and drop an old one afterwards). As far as I can understand drop index should obtain exclusive lock on table it is created on. Is it correct? The only explanation I can find is that this lock is not obtained. Any suggestions/comments/ideas? BTW, using begin; lock table; create index;drop index;commit; is not working, as create index can not detect that table is already locked by current transaction, and tries to lock it again... This is also bug IMHO. -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
Denis Perchine <dyp@perchine.com> writes:
> BTW, using begin; lock table; create index;drop index;commit; is not working,
??
regression=# create table foo (f1 text);
CREATE
regression=# create index fooi1 on foo(f1);
CREATE
regression=# begin;
BEGIN
regression=# lock table foo;
LOCK TABLE
regression=# create index fooi2 on foo(f1);
CREATE
regression=# drop index fooi1;
DROP
regression=# end;
COMMIT
Please define "not working".
regards, tom lane
On Wednesday 19 September 2001 11:25, Tom Lane wrote: > Denis Perchine <dyp@perchine.com> writes: > > BTW, using begin; lock table; create index;drop index;commit; is not > > working, > > ?? > > regression=# create table foo (f1 text); > CREATE > regression=# create index fooi1 on foo(f1); > CREATE > regression=# begin; > BEGIN > regression=# lock table foo; > LOCK TABLE > regression=# create index fooi2 on foo(f1); > CREATE > regression=# drop index fooi1; > DROP > regression=# end; > COMMIT > > Please define "not working". Hmmm... I got deadlock detected... Something was interfered as well... -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
On Wednesday 19 September 2001 11:25, Tom Lane wrote: > Denis Perchine <dyp@perchine.com> writes: > > BTW, using begin; lock table; create index;drop index;commit; is not > > working, > > ?? > > regression=# create table foo (f1 text); > CREATE > regression=# create index fooi1 on foo(f1); > CREATE > regression=# begin; > BEGIN > regression=# lock table foo; > LOCK TABLE > regression=# create index fooi2 on foo(f1); > CREATE > regression=# drop index fooi1; > DROP > regression=# end; > COMMIT > > Please define "not working". Hmmm... I got deadlock detected... Something was interfered as well... BTW, also sometimes I get the following message which really intrigues me. ERROR: Index 8734149 does not exist When I restart my application it just disappears... Is it possible, that backends loose information about updated indices? And how can I debug/detect this? -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------