Обсуждение: "Relation not found" error but table exits.
Hi,
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.
1. prepare plpgsql function , includes TRUNCATE command. Create it.
make sure table "history" exists, or modify function to specify a table already exists.
---
[tanida@srapc2209 tmp]$ cat function.sql
create or replace function test_function() returns integer as
'
begin
lock table public.history in access exclusive mode;
truncate history;
return 0;
end;
' language plpgsql
---
2. prepare SQL file to execute function.
---
[tanida@srapc2209 tmp]$ cat bench.sql
select test_function();
--
3. run pgbench prepared function with -f option and see errors.
---
[tanida@srapc2209 tmp]$ pgbench -p 54381 -n -f bench.sql -c 100 -t 100
Client 85 aborted in state 0: ERROR: relation "public.history" does not exist
CONTEXT: SQL statement "lock table public.history in access exclusive mode"
PL/pgSQL function "tcs" line 5 at SQL statement
Client 82 aborted in state 0: ERROR: relation with OID 31503 does not exist
CONTEXT: SQL statement "lock table public.history in access exclusive mode"
PL/pgSQL function "tcs" line 5 at SQL statement
(snip)
---
In my tests , this bug affect at least 7.4 or above. 7.3.x doesn't affect because
TRUNCATE can't execute from a function. But I don't know whether other commands
such as CLUSTER have this bug.
--
TANIDA Yutaka <tanida@sraoss.co.jp>
TANIDA Yutaka wrote: > Hi, > > 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. > ... > In my tests , this bug affect at least 7.4 or above. 7.3.x doesn't affect because > TRUNCATE can't execute from a function. But I don't know whether other commands > such as CLUSTER have this bug. This looks very similar to this report from yesterday: http://archives.postgresql.org/pgsql-hackers/2007-03/msg01278.php Can you reproduce this with 8.2? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Hi, On Thu, 22 Mar 2007 07:48:18 +0000 Heikki Linnakangas <heikki@enterprisedb.com> wrote: > Can you reproduce this with 8.2? Yes, I can. [tanida@srapc2209 tmp]$ psql -p 54382 Welcome to psql 8.1.2 (server 8.2.3), the PostgreSQL interactive terminal. (snip) tanida=# \q [tanida@srapc2209 tmp]$ pgbench -p 54382 -n -f test_function.sql -c 10 -t 100 Client 8 aborted in state 1: ERROR: relation "history" does not exist -- TANIDA Yutaka <tanida@sraoss.co.jp>
TANIDA Yutaka <tanida@sraoss.co.jp> writes:
> 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.
Do you still see this in 8.2?
regards, tom lane
TANIDA Yutaka <tanida@sraoss.co.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.
HenceTX 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
On Thu, 22 Mar 2007 13:18:12 -0300 Jorge Godoy <jgodoy@gmail.com> wrote: > > > > [tanida@srapc2209 tmp]$ psql -p 54382 > > Welcome to psql 8.1.2 (server 8.2.3), the PostgreSQL interactive terminal. > > (snip) > > ... is 8.*1*.2, not 8.*2*. No. This is "Server 8.2.3" , so I connect to 8.2.3 server by 8.1.2 psql. -- -- TANIDA Yutaka <tanida@sraoss.co.jp>
TANIDA Yutaka <tanida@sraoss.co.jp> writes: > Hi, > > On Thu, 22 Mar 2007 07:48:18 +0000 > Heikki Linnakangas <heikki@enterprisedb.com> wrote: > >> Can you reproduce this with 8.2? > > Yes, I can. This ... > > [tanida@srapc2209 tmp]$ psql -p 54382 > Welcome to psql 8.1.2 (server 8.2.3), the PostgreSQL interactive terminal. > (snip) ... is 8.*1*.2, not 8.*2*. -- Jorge Godoy <jgodoy@gmail.com>
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > 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. No ideas, but a strong +1 for making catalog lookups MVCC. Can this perhaps become a TODO so we don't forget about it and possibly entice people to give it a go? :) - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200703262326 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFGCI8ZvJuQZxSWSsgRA71vAKCNHCRtQUhxVoYKiSmxUAohFSE6TgCeN5qt sdb4PWjhBn+6sepNPTWkArQ= =18qw -----END PGP SIGNATURE-----