Thread: BUG #1277: plpgsql EXECUTE bug in beta3

The following bug has been logged online:

Bug reference:      1277
Logged by:          Tom Hebbron

Email address:      news_user@hebbron.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.

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> Under beta3, the following behaviour is observed:

> 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

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

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:366.1096909353@sss.pgh.pa.us...
> "PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
>> Under beta3, the following behaviour is observed:
>
>> 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
>
> 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
>

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