PostgreSQL Bugs List wrote:
The following bug has been logged online:
Bug reference: 1204
Logged by: Golkin Stanislav
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4
Operating system: FREBSD 4.3
Description: user-defined function in transaction
Details:
User-defined function is called inside transaction block (begin end) in php
script. There is loop in php script where this PL/pgsql functon is invoked
several times. On first iteration it cause no mistake, on second it cause
mistake like this:
ERROR: relation with OID 165645734 does not exist
CONTEXT: PL/pgSQL function "session_recount_time_sec" line 35 at select
into variables
ERROR: current transaction is aborted, commands ignored until end of
transaction block
And it doesn't depend on input data. On first loop it's always OK and then
it's always error
Mmm, I bet you are using temporary table in this fashion:
CREATE OR REPLACE FUNCTION sp_test ( )
RETURNS INTEGER AS'
DECLARE
my_value integer;
BEGIN
CREATE TEMP TABLE test ( a integer );
select a INTO my_value from test limit 1;
drop table test;
return 0;
END;
' LANGUAGE 'plpgsql';
regression=# select sp_test();
sp_test
---------
0
(1 row)
regression=# select sp_test();
ERROR: relation with OID 89367289 does not exist
CONTEXT: PL/pgSQL function "sp_test" line 7 at select into variables
As you can see I got the same error.
I don't know if this is the cleaneast way but you can solve in this way:
CREATE OR REPLACE FUNCTION sp_test ( )
RETURNS INTEGER AS'
DECLARE
my_value integer;
BEGIN
PERFORM * FROM pg_tables
WHERE schemaname = ''pg_temp_1'' AND
tablename = ''test'';
IF NOT FOUND THEN
CREATE TEMP TABLE test ( a integer ) ON COMMIT DELETE ROWS;
END IF;
select a INTO my_value from test limit 1;
return 0;
END;
' LANGUAGE 'plpgsql'
VOLATILE;
regression=# select sp_test();
sp_test
---------
0
(1 row)
regression=# select sp_test();
sp_test
---------
0
(1 row)
Regards
Gaetano Mendola
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
