Skip site navigation (1) Skip section navigation (2)

Re: "Relation not found" error but table exits.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: TANIDA Yutaka <tanida(at)sraoss(dot)co(dot)jp>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: "Relation not found" error but table exits.
Date: 2007-03-22 18:13:48
Message-ID: 20156.1174587228@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
TANIDA Yutaka <tanida(at)sraoss(dot)co(dot)jp> writes:
> My customer found a problem about PL/pgsql functions and TRUNCATE command.
> If you execute PL/pgsql function includeing TRUNCATE command concurrently, 
> causes "relation ... does not exist." or "relation with OID XXXXX does not exist" against
> exists table.
> Here's a testcase to reproduce this.

After some thought I have a theory about what's happening here.  The
test case involves lots of TRUNCATEs, which each will do an update on
the relation's pg_class row.  Now an incoming operation on the table
has to look up the relation's OID before it can obtain lock, so that
means that it is scanning pg_class using the relname index concurrently
with these updates.  That scan is done using SnapshotNow rules, which
means that it's possible for this sequence of events to occur:

	1. TX A updates pg_class row.
	2. TX B visits the updated row while scanning; it's not
	   committed good, so it's ignored.
	3. TX A commits.
	4. TX B visits the old row in its scan.  By now it's committed
	   dead, so it's also ignored.
	5. Hence TX B fails to find any live row matching the requested
	   table name, and comes back with "relation does not exist".

I'm not sure about a good way to fix this.  It sorta looks like we need
a different visibility rule for scanning pg_class when we don't yet have
any lock on the relation, but I'm unclear what that rule ought to be.

This also ties into the discussions we've had off-and-on about making
catalog lookups behave in an MVCC fashion instead of using SnapshotNow.
I'm still pretty hesitant to go there, but maybe we could do something
involving MVCC for unlocked lookups and then SnapshotNow for (re)reading
a table's schema info once we've got lock on it.

Ideas anyone?

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2007-03-22 18:22:00
Subject: Re: CREATE INDEX and HOT (was Question: pg_classattributes and race conditions ?)
Previous:From: Pavan DeolaseeDate: 2007-03-22 18:00:20
Subject: Re: CREATE INDEX and HOT - revised design

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-03-22 20:23:16
Subject: Re: Very slow bytea extraction
Previous:From: msmbarabino@virgilio.itDate: 2007-03-22 18:07:11
Subject: Re: Very slow bytea extraction

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group