Re: PostgreSQL 8.3 temporary tables & stored functions

Поиск
Список
Период
Сортировка
Искать

Re: PostgreSQL 8.3 temporary tables & stored functions

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Gabriele Messineo  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

PostgreSQL 8.3 temporary tables & stored functions

От:
Gabriele Messineo <gabriele.messineo@winext.eu>
Дата:
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

Re: PostgreSQL 8.3 temporary tables & stored functions

От:
Gabriele Messineo <gabriele.messineo@winext.eu>
Дата:
On Tuesday 17 June 2008 15:11:06 Tom Lane wrote:
> Gabriele Messineo  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
FAQ