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.
---
[EMAIL PROTECTED] 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.
---
[EMAIL PROTECTED] tmp]$ cat bench.sql
select test_function();
--
3. run pgbench prepared function with -f option and see errors.
---
[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
