Re: Fw: ERROR: could not open relation with OID 59132

On Thursday 10 December 2009 9:17:47 am Postgre Novice wrote:

>
> At a guess I am thinking it has to do with this:
>
> "All constraints on all partitions of the master table are examined during
> constraint exclusion, so large numbers of partitions are likely to increase
> query planning time considerably. Partitioning using these techniques will
> work well with up to perhaps a hundred partitions; don't try to use many
> thousands of partitions."
>
>
> From here:
> http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html
>
>
> You are not showing how you are calling the DROP and SELECT statements but
> I thinking it is being done in a way that the SELECT statement still thinks
> the dropped table exists. For more information see:
>
> http://www.postgresql.org/docs/8.4/interactive/mvcc.html
>
> --
> Adrian Klaver
> aklaver(at)comcast(dot)net

>I think :
>
>           A) Drop statement is called from a cronjob script at 3:00A.M for a 8
> days old partition. It takes about 55 min to drop it.
>           B) Select statement is called from app (some where after 3:00 A.M.)
> > querying on the partition key for last two days only  . There are only 8 >
> partitions.

> So I am thinking on the line that while drop table is in progress and because
> of MVCC the select query tries to evaluate constraint on all partitions which
> include the partition is question and waits for its lock to open and once the
> drop table succeeded  it tries to evaluate its constraint and does not find >
> the table.(This assumption is due to the same time when  drop table succeeded
> and the error is raised by select query and when query is again hit it works
> fine)
>
>
> any more thoughts will be more helpful and a work around for such condition
>because the master table is under continues query for large data (each
>partition contains about 10 million records for a particular day)

>Thanks for help ...

> --Anil  

It is hard to say exactly what to do without knowing in detail your application.
Some helpful information on locks can be found here:

http://www.postgresql.org/docs/8.4/interactive/explicit-locking.html

It details what operations hold what locks. It may turn out doing a DELETE then
a DROP is better, but I would test that first to see if plays well with your
app. Be sure and read up on DELETE and the ONLY clause.

EMAIL NOTE: When replying please try to keep your reply above the signature
line "---", it makes responding to a reply easier:)

Thanks,
--
Adrian Klaver
aklaver(at)comcast(dot)net