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@xxxxxxxxxxx >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@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
