Re: Could not open relation with OID (table partitioning issue?)

From: "mark" <dvlhntr(at)gmail(dot)com>
To: <peter(at)vfemail(dot)net>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Could not open relation with OID (table partitioning issue?)
Date: 2010-11-16 02:55:35
Message-ID: 01f301cb8539$beabc7b0$3c035710$@com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Our backups are fine and don't have a problem creating or restoring to/from
them. It's just that it appears the create/drop function we have is dropping
child partitions out from under running queries, and I thought that MVCC
would handle this...

Right now I am working up to trying to create a self contained case that
people could use to reproduce the issue for the bugs list.

-Mark

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of peter(at)vfemail(dot)net
Sent: Monday, November 15, 2010 7:47 AM
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Could not open relation with OID (table partitioning
issue?)

At 09:22 AM 11/15/2010, mark wrote:
>Hi all
>
>Running Postgres 8.3.7
>
>Are there any known issues with table partitioning and transactions having
a child partition getting removed out from under running queries?
>
>
>I got an error in my log about not being able to open a relation with OID
XXXXX from a SELECT statement that ran about the same time that a cron job
may have removed some of the older table partitions. (that may or may not
have been visible to select query)
>
>Right now I have been checking but I can't find anything wrong with the
database so it doesn't look like I have any db corruption issues or the like
currently. there is some hate in the logs about it for a while and then the
database was restarted.
>
>My best guess is that a the clean up of old partitions yanked a table out
from view.. Kind of like when you run a \d at the same time a table is
dropped.
>
>Thoughts? Comments? Ideas ?

I started seeing these frightening messages a couple of months ago:

pg_dump: ERROR: could not open relation with OID 2196359751
pg_dump: SQL command to dump the contents of table "xyz" failed:
PQendcopy() failed.
pg_dump: Error message from server: ERROR: could not open relation with
OID 2196359751
pg_dump: The command was: COPY public.xyz ({various_field_names}) TO
stdout;

and the error was causing a daily database backup routine to fail. Because
the problem was too far beyond my ability to solve, I hired Frank Heikens at
http://nl.linkedin.com/pub/frank-heikens/0/190/517 and he got everything
back to normal in a day or two. Mr. Heikens isolated the one corrupted data
record, created a new table, and replaced the flawed table with the new
table. I have nothing but compliments for Mr. Heikens' knowledge,
professionalism, speed, accuracy, caution, communication, and wizardry.

-------------------------------------------------
This message sent via VFEmail.net
http://www.vfemail.net
$14.95 Lifetime accounts - 1GB disk, No bandwidth quotas!

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Machiel Richards 2010-11-16 07:06:00 Postgresql certifications
Previous Message Leif Biberg Kristensen 2010-11-15 22:36:21 Re: in linux, what user do i install postgres under?