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@comcast.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@comcast.net
