"Relation not found" error but table exits.

From: TANIDA Yutaka <tanida(at)sraoss(dot)co(dot)jp>
To: pgsql-bugs(at)postgresql(dot)org
Subject: "Relation not found" error but table exits.
Date: 2007-03-22 05:03:17
Message-ID: 20070322140317.031e3dff.tanida@sraoss.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

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(at)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(at)srapc2209 tmp]$ cat bench.sql
select test_function();
--

3. run pgbench prepared function with -f option and see errors.

---
[tanida(at)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(at)sraoss(dot)co(dot)jp>

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Steve Gieseking 2007-03-22 05:34:05 BUG #3181: ecpg generating wrong code
Previous Message Tom Lane 2007-03-21 23:01:02 Re: Very slow bytea extraction

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-03-22 05:15:39 Re: Proposal: Adding JIS X 0213 support
Previous Message Tatsuo Ishii 2007-03-22 04:58:58 Re: Proposal: Adding JIS X 0213 support