Problems during doing reindex on 7.0.3

Denis Perchine wrote:

Hello,

I have very interesting problem. I have quite highly loaded database.
Once a day I create new indices, and after that drop old ones. Ido this one
by one.

Your "reindex" is different from REINDEX command,isn't it ?

All is fine except this error message I sometimes get for some of the queries
just after reindex.

ERROR: Index 2050642 does not exist

Looks like index oid was already resolved, but index was not locked yet...
And it was dropped just under the feet...

Parser-rewriter-planner acquires a short term lock.
This may be improved in 7.1 though I'm not sure.

Regards.
Hiroshi Inoue

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Denis Perchine wrote:

ERROR: Index 2050642 does not exist

Looks like index oid was already resolved, but index was not locked yet...
And it was dropped just under the feet...

Parser-rewriter-planner acquires a short term lock.
This may be improved in 7.1 though I'm not sure.

I believe it is fixed for 7.1 --- a lock is acquired at first
reference to the table and not released.

regards, tom lane

On 14 November 2000 07:33, Hiroshi Inoue wrote:

Denis Perchine wrote:

Hello,

I have very interesting problem. I have quite highly loaded database.
Once a day I create new indices, and after that drop old ones. Ido this
one by one.

Your "reindex" is different from REINDEX command,isn't it ?

Sure. It is a sequnce like:
create index ix_name_1 ...;
drop index ix_name;
alter table ix_name_1 rename to ix_name;

For each index I like to recreate.

All is fine except this error message I sometimes get for some of the
queries just after reindex.

ERROR: Index 2050642 does not exist

Looks like index oid was already resolved, but index was not locked
yet... And it was dropped just under the feet...

Parser-rewriter-planner acquires a short term lock.
This may be improved in 7.1 though I'm not sure.

That's really bad, as it forces some of the queries to fail... And I can not
detect the situation, as PostgreSQL does not have any classes for errors.

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------