PostgreSQL 8.3 temporary tables & stored functions

Hello,
I started working on PostgreSQL 8.2 and I used some temporary tables to pass
data between stored functions. To avoid any issue, according to this post
(http://archives.postgresql.org/pgsql-bugs/2007-07/msg00067.php) I used lots
of EXECUTE statement to cause the compilation of every statement involving
temporary tables.

Now I'm performing a move of my code to PostgreSQL 8.3 in order to keep
advantage of some new features such as "Automatically re-plan cached queries
when table definitions change or statistics are updated"
(http://www.postgresql.org/docs/8.3/static/release-8-3.html).

So I rewrote my code and changed some functions removing the execute
statement.

That way my expected result would have been:
- having volatile functions the query plan shouldn't be cached
- between different calls (with different temp tables) plpgsql should
understand when I'm working on a different OID and should recompile the query

But actually I'm experiencing some issues because some statements does not
work at all, in particular, I can read from temporary tables without any
issue but my DELETEs fail silently.

I've reproduced the issue with the following code:

-- set up database and functions --------------------------------------------
-- psql -p 5433
DROP DATABASE testtemp;
CREATE DATABASE testtemp;
-- createlang -p 5433 plpgsql testtemp
-- psql -p 5433 -d testtemp

DROP FUNCTION IF EXISTS testexecute(VARCHAR(255));
DROP FUNCTION IF EXISTS testdynamic(VARCHAR(255));

CREATE FUNCTION testexecute(str VARCHAR(255)) RETURNS VOID AS $$
DECLARE myoid BIGINT DEFAULT NULL;
BEGIN
EXECUTE $body$ SELECT tableoid FROM pg_attribute WHERE attrelid
= 'testtemptable'::regclass LIMIT 1;$body$ INTO myoid;
RAISE NOTICE 'testexecute on %',myoid;
EXECUTE 'DELETE FROM testtemptable WHERE name='|| quote_literal(str) || ';';
RETURN;
END;
$$ LANGUAGE PLPGSQL VOLATILE SECURITY INVOKER;

CREATE FUNCTION testdynamic(str VARCHAR(255)) RETURNS VOID AS $$
DECLARE myoid BIGINT DEFAULT NULL;
BEGIN
SELECT tableoid INTO myoid FROM pg_attribute WHERE attrelid
= 'testtemptable'::regclass LIMIT 1;
RAISE NOTICE 'testdynamic on %',myoid;
DELETE FROM testtemptable WHERE name=str;
RETURN;
END;
$$ LANGUAGE PLPGSQL VOLATILE SECURITY INVOKER;

-- execute the following steps twice ----------------------------------------
CREATE TEMPORARY TABLE testtemptable(myid BIGINT PRIMARY KEY,name
VARCHAR(255));
SELECT tableoid FROM pg_attribute WHERE attrelid = 'testtemptable'::regclass;
INSERT INTO testtemptable(myid,name) VALUES(1,'teststring1');
INSERT INTO testtemptable(myid,name) VALUES(2,'teststring2');
INSERT INTO testtemptable(myid,name) VALUES(3,'teststring3');
INSERT INTO testtemptable(myid,name) VALUES(4,'teststring4');
INSERT INTO testtemptable(myid,name) VALUES(5,'teststring5');
INSERT INTO testtemptable(myid,name) VALUES(6,'teststring6');
INSERT INTO testtemptable(myid,name) VALUES(7,'teststring7');
INSERT INTO testtemptable(myid,name) VALUES(8,'teststring8');
INSERT INTO testtemptable(myid,name) VALUES(9,'teststring9');

SELECT testexecute('teststring6');
SELECT testexecute('teststring8');
SELECT testdynamic('teststring2');
SELECT testdynamic('teststring4');
SELECT * FROM testtemptable;

DROP TABLE testtemptable;

-----------------------------------------------------------------------------

As you can see testexecute work fine, testdynamic instead got null table
reference, but it fails silently.

Is there something wrong on what I'm expecting from new PostgreSQL version, or
is there some misconfiguration?

Thanks,

Gabriele Messineo

Gabriele Messineo <gabriele(dot)messineo(at)winext(dot)eu> writes:
> But actually I'm experiencing some issues because some statements does not
> work at all, in particular, I can read from temporary tables without any
> issue but my DELETEs fail silently.

AFAICS the deletes work fine. The only issue I can find with your
example is that a regclass constant doesn't get re-evaluated ... which
is a bug no doubt, but it shouldn't really affect normal use.

regards, tom lane

On Tuesday 17 June 2008 15:11:06 Tom Lane wrote:
> Gabriele Messineo <gabriele(dot)messineo(at)winext(dot)eu> writes:
> > But actually I'm experiencing some issues because some statements does
> > not work at all, in particular, I can read from temporary tables without
> > any issue but my DELETEs fail silently.
>
> AFAICS the deletes work fine. The only issue I can find with your
> example is that a regclass constant doesn't get re-evaluated ... which
> is a bug no doubt, but it shouldn't really affect normal use.
>
> regards, tom lane

I finally realized a bug in my SQL code (I was checking with the equal
operator a variable that could assume NULL values).
I apologize for the previous mail, but I was driven on a false track when,
debugging, I started to find strange oids ... (they were the first thing I
checked when porting).

Thanks for your support.

Regards,

Gabriele Messineo