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

"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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-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

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-03-22 05:15:39
Subject: Re: Proposal: Adding JIS X 0213 support
Previous:From: Tatsuo IshiiDate: 2007-03-22 04:58:58
Subject: Re: Proposal: Adding JIS X 0213 support

pgsql-bugs by date

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

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