Re: Fw: ERROR: could not open relation with OID 59132 - Mailing list pgsql-general

---

From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-general@postgresql.org
Cc: Postgre Novice <postgrenovice@yahoo.com>
Sent: Thu, December 10, 2009 10:23:21 PM
Subject: Re: Fw: [GENERAL] ERROR: could not open relation with OID 59132

On Wednesday 09 December 2009 11:34:39 pm Postgre Novice wrote:
> Can someone please share some light on  this....
>
>
>
> ----- Forwarded Message ----
> From: Postgre Novice <postgrenovice@yahoo.com>
> To: pgsql-general@postgresql.org
> Sent: Wed, December 9, 2009 5:23:18 PM
> Subject: [GENERAL] ERROR:  could not open relation with OID 59132
>
>
> Hello ,
>
> after google search i havent found any solution or clue for this specific
> case:
>
> Background:
> Postgresql: 8.3.0
>
>
> select version();
>                                          version
> ---------------------------------------------------------------------------
>----------------- PostgreSQL 8.3.0 on x86_64-unknown-linux-gnu, compiled by
> GCC gcc (GCC) 4.2.1 (SUSE Linux)
>
>
> I have a master table(sms_new) and few child tables(sms_new_$date)
> inherited from it.
>
> Problem is I am dropping 8 days old partition and at the same time a select
> runs on master but for different partition range that is greater than last
> 2 days.
>
> constraint exclusion is ON.
>
> But this select query fails giving the above error.
>
> As per my understanding with constraint exclusion on select should not even
> care for 8 days old table i.e. it should not include it in plan.
>
> Here the specific case is master is quired  with id > 20091208 criteria for
> SELECT  and I am dropping table 20091130
>
>  Table is partitioned on deliveryid.
>
> Here are the logs:
>
>
> 2009-12-09 03:55:08 IST [16481]: [1-1] LOG:  duration: 3142605.157 ms
> statement: drop table sms_new_20091130;
> 2009-12-09 03:55:08 IST [2984]: [1-1] ERROR:  could not open relation
> with OID 59132
> 2009-12-09 03:55:08 IST [2984]: [2-1] STATEMENT:  select min(smsnewseq)
> from sms_new where deliveryid >=20091208000000000 limit 1
> 2009-12-09 03:55:08 IST [14689]: [1-1] ERROR:  could
>  not open relation
> with OID 59132
> 2009-12-09 03:55:08 IST [14689]: [2-1] STATEMENT:  select min(smsnewseq)
> from sms_new where deliveryid >=20091208000000000 limit 1
> 2009-12-09 03:55:08 IST [28102]: [8711-1] ERROR:  could not open
> relation with OID 59132
> 2009-12-09 03:55:08 IST [28102]: [8712-1] STATEMENT:  select
> deliveryid,mobile,gateway,otid,account,smsnewseq,feedtype,recvtime,otid_seq
>,partid,seq from sms_new  where smsnewseq in
> (7669097743,7669097744,7669097745,7669097754,7669097755,7669097756,76690977
>57,7669097836,7669097837,7669097838,7669097845,7669097846,7669097871,7669097
>872,7669097873,7669097906,7669097907,7669097908,7669097929,7669097930,766909
>7931,7669097944,7669097960,7669097961,7669097962,7669097963,7669097964,76690
>97976,7669097977,7669097987,7669097988) and feedtype in (4,6,7,8,9,12) and
> deliveryid >20091206000000000
> 2009-12-09 03:55:08 IST [18969]: [7-1] ERROR:  could not open relation
> with OID
>  59132
> 2009-12-09 03:55:08 IST [18969]: [8-1] STATEMENT:  select
> reqid,message,createddate,applicationid,gatewayid,deliverytype,fromtext,pri
>ority,url,keyword,ad,taskid,itemtags,otid,account,n.smsnewseq,d.deliveryid,n
>.otid,n.seq,n.mobile,d.feedid,d.itemid,d.feedtype ,lengthinbytes,recvtime
> from delivery d, sms_new n where d.deliveryid = n.deliveryid and
> n.smsnewseq > $1 and not exists (select '1' from
> ent_status where n.smsnewseq = ent_status.smsnewseq) and gateway in
> (2,6,8,9) and  d.deliveryid >= 20091208000000000 and n.deliveryid >=
> 20091208000000000 and d.feedtype in (4,6,7,8,9) and n.otid like
> '%-%-%-%-%' and dlrreport=true limit 20000
> Can anybody please share some light on it..
>
> Thanks in advance
>
> --Anil


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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks Adrian for replay

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