The following bug has been logged online:

Bug reference: 1277
Logged by: Tom Hebbron

Email address: news_user (AT) hebbron (DOT) com

PostgreSQL version: 8.0 Beta

Operating system: n/a

Description: plpgsql EXECUTE bug in beta3

Details:

In beta2, the following script runs as expected:

--begin SQL script
drop table a cascade;

create or replace function execute_sql(text) returns void AS $$begin execute
$1; return; end; $$ language plpgsql;

select execute_sql('create table a (i integer); insert into a(i)
values(1);');


select * from a;
--eof SQL script

The create table and insert commands are executed OK, and table 'a' contains
a single row with value 1 in the column i.


Under beta3, the following behaviour is observed:

test=# drop table a cascade;
ERROR: table "a" does not exist
test=#
test=# create or replace function execute_sql(text) returns void AS $$begin
execute $1; return; end; $$ language plpgsql;
CREATE FUNCTION
test=#
test=# select execute_sql('create table a (i integer); insert into a(i)
values(1);');
ERROR: relation "a" does not exist
CONTEXT: SQL query "create table a (i integer); insert into a(i)
values(1);"
PL/pgSQL function "execute_sql" line 1 at execute statement
test=#
test=# select * from a;
ERROR: relation "a" does not exist
test=#

As demonstrated, some change in the plpgsql EXECUTE handler between beta2
and beta3 has caused multiple statement EXECUTE calls to be unable to see
the effects of earlier statements in the same EXECUTE call.

calling select execute_sql('begin; create table a (i integer); insert into
a(i) values(1); commit;');

has the same results.



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
This is happening because EXECUTE now parses and plans the whole string
in one go, so that it tries to plan the INSERT before the CREATE has
been carried out. You would see the same behavior if you tried for
instance to execute those two commands as the body of an SQL function.
I am inclined to regard this as "not a bug", and tell you to execute the
two queries in separate EXECUTE commands. I'm not sure it's worth the
substantial additional complexity in spi.c that would be needed to
preserve the old behavior --- especially when the documentation does not
suggest anywhere that you can use EXECUTE to execute more than one
command in the first place.

Anyone else have an opinion?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Tom Lane" <tgl (AT) sss (DOT) pgh.pa.us> wrote

I guessed it would be something like that - thanks for the explanation -
will update our code to perform each step individually.


--
Tom Hebbron
www.hebbron.com




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)