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

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