Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problems during doing reindex on 7.0.3

Problems during doing reindex on 7.0.3

From
Denis Perchine
Date:
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.

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

--
Sincerely Yours,
Denis Perchine

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

Re: Problems during doing reindex on 7.0.3

From
Hiroshi Inoue
Date:
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

Re: Problems during doing reindex on 7.0.3

From
Tom Lane
Date:
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

Re: Problems during doing reindex on 7.0.3

From
Denis Perchine
Date:
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
----------------------------------