Horton

Thread

  1. Recreating indices safely

    Denis Perchine <dyp@perchine.com> — 2001-09-18T17:00:46Z

    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
----------------------------------

  2. Re: Recreating indices safely

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-19T04:25:54Z

    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

  3. Re: Recreating indices safely

    Denis Perchine <dyp@perchine.com> — 2001-09-19T17:32:02Z

    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
----------------------------------

  4. Re: Recreating indices safely

    Denis Perchine <dyp@perchine.com> — 2001-09-20T21:08:19Z

    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
----------------------------------