Re: Re: BUG #9578: Undocumented behaviour for temp tables created inside query language (SQL) functions

The following bug has been logged on the website:

Bug reference: 9578
Logged by: Jack Douglas
Email address: jack(at)douglastechnology(dot)co(dot)uk
PostgreSQL version: 9.3.2
Operating system: Debian Wheezy 64-bit
Description:

A temp table created inside an SQL function does not override existing
permanent tables with the same name as the documentation here indicates it
should:

http://www.postgresql.org/docs/9.3/static/sql-createtable.html#AEN72676

I've reproduced this on the major versions back to 8.4.

More details, test case and investigation here:

http://dba.stackexchange.com/q/60997/1396

On Sun, Mar 16, 2014 at 3:50 AM, <jack(at)douglastechnology(dot)co(dot)uk> wrote:
> A temp table created inside an SQL function does not override existing
> permanent tables with the same name as the documentation here indicates it
> should:
>
> http://www.postgresql.org/docs/9.3/static/sql-createtable.html#AEN72676
>
> I've reproduced this on the major versions back to 8.4.
>
> More details, test case and investigation here:
>
> http://dba.stackexchange.com/q/60997/1396

I checked the test case which you given in the above link.
As you are seeing the difference in behavior of accessing a temp table
inside an SQL function
and PLPGSQL function.

Table:

create table foo(id) as values (1);
select * from foo;

SQL-function:

create or replace function f() returns setof integer language sql as $$
create temporary table foo(id) as values (2);
select id from foo;
$$;
select * from f();

PLPGSQL function:

create or replace function f() returns setof integer language plpgsql as $$
begin
create temporary table foo(id) as values (2);
return query select id from foo;
end;
$$;
select * from f();

This is because while executing the SQL function the entire function
body is parsed and executed.
But with the PLPGSQL function statement by statement is parsed and
executed. Because of this
reason the SQL function not able to see the temp table which is
created during the function execution.
That is the reason the result is different.

I don't think it is a bug.

Regards,
Hari Babu
Fujitsu Australia

Haribabu Kommi-2 wrote
> On Sun, Mar 16, 2014 at 3:50 AM, &lt;

> jack(at)(dot)co

> &gt; wrote:
>> A temp table created inside an SQL function does not override existing
>> permanent tables with the same name as the documentation here indicates
>> it
>> should:
>>
>> http://www.postgresql.org/docs/9.3/static/sql-createtable.html#AEN72676
>>
>> I've reproduced this on the major versions back to 8.4.
>>
>> More details, test case and investigation here:
>>
>> http://dba.stackexchange.com/q/60997/1396
>
> I checked the test case which you given in the above link.
> As you are seeing the difference in behavior of accessing a temp table
> inside an SQL function
> and PLPGSQL function.
>
> Table:
>
> create table foo(id) as values (1);
> select * from foo;
>
> SQL-function:
>
> create or replace function f() returns setof integer language sql as $$
> create temporary table foo(id) as values (2);
> select id from foo;
> $$;
> select * from f();
>
> PLPGSQL function:
>
> create or replace function f() returns setof integer language plpgsql as
> $$
> begin
> create temporary table foo(id) as values (2);
> return query select id from foo;
> end;
> $$;
> select * from f();
>
> This is because while executing the SQL function the entire function
> body is parsed and executed.
> But with the PLPGSQL function statement by statement is parsed and
> executed. Because of this
> reason the SQL function not able to see the temp table which is
> created during the function execution.
> That is the reason the result is different.

This seems to be the case; a DML statement inside an SQL function cannot
access any temporary tables created within the same function.

Based on this I have two documentation suggestions:

Add a paragraph/sentence to: CREATE TABLE -> TEMPORARY or TEMP
"""
Note that due to the nature of SQL-language function parsing (see section
35.4) it is not possible to both define and use a temporary table in the
same function.
"""

Add to 35.4 - Query Language (SQL) Functions
35.4.0 - Parsing Mechanics
(this seems important enough for a sub-section and not just a paragraph in
the introduction)
"""
The body of an SQL function is parsed as if it were a single - multi-part -
statement and thus uses a constant snapshot of the system catalog for every
sub-statement therein. Commands that alter the catalog will likely not work
as expected.

For example: Issuing "CREATE TEMP TABLE" within an SQL function will add the
table to the catalog but subsequent statements in the same function will not
see those additions and thus the temporary table will be invisible to them.

Thus it is generally advised that pl/pgsql be used, instead of SQL, when
non-SELECT/INSERT/UPDATE/DELETE statements are required.
"""

I know there is a intentional lack of documenting function-specific behavior
in the main SQL section; and pl/pgsql is not "preferred" - even though in
reality SQL and pl/pgsql are indeed the primary languages people use for
extending the system - but something should be put in place explaining this
non-obvious behavior.

> I don't think it is a bug.

I use a slightly more broad definition of "bug" and I would say that for the
typical user the lack of documentation - given the non-obviousness of the
behavior (why would someone expect SQL to behave differently than pl/pgsql
in this regard) - constitutes a bug. That the solution is to document the
behavior instead of changing it does not make it any less a bug (i.e.,
something requiring fixing).

The fact the actual report specifically mentions "Undocumented" is worth
props since many "bug" reports are considerably less clean and detailed both
with the subject/comment and their proofs.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9578-Undocumented-behaviour-for-temp-tables-created-inside-query-language-SQL-functions-tp5796176p5796262.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

On Mon, Mar 17, 2014 at 2:42 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> Haribabu Kommi-2 wrote
>> On Sun, Mar 16, 2014 at 3:50 AM, <
>
>> jack(at)(dot)co
>
>> > wrote:
>>> A temp table created inside an SQL function does not override existing
>>> permanent tables with the same name as the documentation here indicates
>>> it
>>> should:
>>>
>>> http://www.postgresql.org/docs/9.3/static/sql-createtable.html#AEN72676
>>>
>>> I've reproduced this on the major versions back to 8.4.
>>>
>>> More details, test case and investigation here:
>>>
>>> http://dba.stackexchange.com/q/60997/1396
>>
>> I checked the test case which you given in the above link.
>> As you are seeing the difference in behavior of accessing a temp table
>> inside an SQL function
>> and PLPGSQL function.
>>
>> Table:
>>
>> create table foo(id) as values (1);
>> select * from foo;
>>
>> SQL-function:
>>
>> create or replace function f() returns setof integer language sql as $$
>> create temporary table foo(id) as values (2);
>> select id from foo;
>> $$;
>> select * from f();
>>
>> PLPGSQL function:
>>
>> create or replace function f() returns setof integer language plpgsql as
>> $$
>> begin
>> create temporary table foo(id) as values (2);
>> return query select id from foo;
>> end;
>> $$;
>> select * from f();
>>
>> This is because while executing the SQL function the entire function
>> body is parsed and executed.
>> But with the PLPGSQL function statement by statement is parsed and
>> executed. Because of this
>> reason the SQL function not able to see the temp table which is
>> created during the function execution.
>> That is the reason the result is different.
>
>
>
> This seems to be the case; a DML statement inside an SQL function cannot
> access any temporary tables created within the same function.
>
> Based on this I have two documentation suggestions:
>
> Add a paragraph/sentence to: CREATE TABLE -> TEMPORARY or TEMP
> """
> Note that due to the nature of SQL-language function parsing (see section
> 35.4) it is not possible to both define and use a temporary table in the
> same function.
> """
>
> Add to 35.4 - Query Language (SQL) Functions
> 35.4.0 - Parsing Mechanics
> (this seems important enough for a sub-section and not just a paragraph in
> the introduction)
> """
> The body of an SQL function is parsed as if it were a single - multi-part -
> statement and thus uses a constant snapshot of the system catalog for every
> sub-statement therein. Commands that alter the catalog will likely not work
> as expected.
>
> For example: Issuing "CREATE TEMP TABLE" within an SQL function will add the
> table to the catalog but subsequent statements in the same function will not
> see those additions and thus the temporary table will be invisible to them.
>
> Thus it is generally advised that pl/pgsql be used, instead of SQL, when
> non-SELECT/INSERT/UPDATE/DELETE statements are required.
> """

Thanks. The proposed documentation changes are good for the user to
understand the behavior.

Regards,
Hari Babu
Fujitsu Australia

Haribabu Kommi-2 wrote
> On Mon, Mar 17, 2014 at 2:42 PM, David Johnston &lt;

> polobo@

> &gt; wrote:
>> Haribabu Kommi-2 wrote
>>> On Sun, Mar 16, 2014 at 3:50 AM, <
>>
>>> jack(at)(dot)co
>>
>>> > wrote:
>>>> A temp table created inside an SQL function does not override existing
>>>> permanent tables with the same name as the documentation here indicates
>>>> it
>>>> should:
>>>>
>>>> http://www.postgresql.org/docs/9.3/static/sql-createtable.html#AEN72676
>>>>
>>>> I've reproduced this on the major versions back to 8.4.
>>>>
>>>> More details, test case and investigation here:
>>>>
>>>> http://dba.stackexchange.com/q/60997/1396
>>>
>>> I checked the test case which you given in the above link.
>>> As you are seeing the difference in behavior of accessing a temp table
>>> inside an SQL function
>>> and PLPGSQL function.
>>>
>>> Table:
>>>
>>> create table foo(id) as values (1);
>>> select * from foo;
>>>
>>> SQL-function:
>>>
>>> create or replace function f() returns setof integer language sql as $$
>>> create temporary table foo(id) as values (2);
>>> select id from foo;
>>> $$;
>>> select * from f();
>>>
>>> PLPGSQL function:
>>>
>>> create or replace function f() returns setof integer language plpgsql as
>>> $$
>>> begin
>>> create temporary table foo(id) as values (2);
>>> return query select id from foo;
>>> end;
>>> $$;
>>> select * from f();
>>>
>>> This is because while executing the SQL function the entire function
>>> body is parsed and executed.
>>> But with the PLPGSQL function statement by statement is parsed and
>>> executed. Because of this
>>> reason the SQL function not able to see the temp table which is
>>> created during the function execution.
>>> That is the reason the result is different.
>>
>>
>>
>> This seems to be the case; a DML statement inside an SQL function cannot
>> access any temporary tables created within the same function.
>>
>> Based on this I have two documentation suggestions:
>>
>> Add a paragraph/sentence to: CREATE TABLE -> TEMPORARY or TEMP
>> """
>> Note that due to the nature of SQL-language function parsing (see section
>> 35.4) it is not possible to both define and use a temporary table in the
>> same function.
>> """
>>
>> Add to 35.4 - Query Language (SQL) Functions
>> 35.4.0 - Parsing Mechanics
>> (this seems important enough for a sub-section and not just a paragraph
>> in
>> the introduction)
>> """
>> The body of an SQL function is parsed as if it were a single - multi-part
>> -
>> statement and thus uses a constant snapshot of the system catalog for
>> every
>> sub-statement therein. Commands that alter the catalog will likely not
>> work
>> as expected.
>>
>> For example: Issuing "CREATE TEMP TABLE" within an SQL function will add
>> the
>> table to the catalog but subsequent statements in the same function will
>> not
>> see those additions and thus the temporary table will be invisible to
>> them.
>>
>> Thus it is generally advised that pl/pgsql be used, instead of SQL, when
>> non-SELECT/INSERT/UPDATE/DELETE statements are required.
>> """
>
> Thanks. The proposed documentation changes are good for the user to
> understand the behavior.

Although "CREATE TEMP TABLE" probably shouldn't be special-cased for this
since it is no more or less likely to be mis-used in this way than CREATE
TYPE or CREATE FUNCTION. The comment in the SQL language area should be
sufficient as creating such a function can be expected to have at least read
that section and to have seen the "parsing rules" note that pertains to all
of these.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9578-Undocumented-behaviour-for-temp-tables-created-inside-query-language-SQL-functions-tp5796176p5796272.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

On Mon, Mar 17, 2014 at 4:15 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> Haribabu Kommi-2 wrote
>> On Mon, Mar 17, 2014 at 2:42 PM, David Johnston <
>
>> polobo@
>
>> > wrote:
>>> Haribabu Kommi-2 wrote
>>>> On Sun, Mar 16, 2014 at 3:50 AM, <
>>>
>>>> jack(at)(dot)co
>>>
>>>> > wrote:
>>>>> A temp table created inside an SQL function does not override existing
>>>>> permanent tables with the same name as the documentation here indicates
>>>>> it
>>>>> should:
>>>>>
>>>>> http://www.postgresql.org/docs/9.3/static/sql-createtable.html#AEN72676
>>>>>
>>>>> I've reproduced this on the major versions back to 8.4.
>>>>>
>>>>> More details, test case and investigation here:
>>>>>
>>>>> http://dba.stackexchange.com/q/60997/1396
>>>>
>>>> I checked the test case which you given in the above link.
>>>> As you are seeing the difference in behavior of accessing a temp table
>>>> inside an SQL function
>>>> and PLPGSQL function.
>>>>
>>>> Table:
>>>>
>>>> create table foo(id) as values (1);
>>>> select * from foo;
>>>>
>>>> SQL-function:
>>>>
>>>> create or replace function f() returns setof integer language sql as $$
>>>> create temporary table foo(id) as values (2);
>>>> select id from foo;
>>>> $$;
>>>> select * from f();
>>>>
>>>> PLPGSQL function:
>>>>
>>>> create or replace function f() returns setof integer language plpgsql as
>>>> $$
>>>> begin
>>>> create temporary table foo(id) as values (2);
>>>> return query select id from foo;
>>>> end;
>>>> $$;
>>>> select * from f();
>>>>
>>>> This is because while executing the SQL function the entire function
>>>> body is parsed and executed.
>>>> But with the PLPGSQL function statement by statement is parsed and
>>>> executed. Because of this
>>>> reason the SQL function not able to see the temp table which is
>>>> created during the function execution.
>>>> That is the reason the result is different.
>>>
>>>
>>>
>>> This seems to be the case; a DML statement inside an SQL function cannot
>>> access any temporary tables created within the same function.
>>>
>>> Based on this I have two documentation suggestions:
>>>
>>> Add a paragraph/sentence to: CREATE TABLE -> TEMPORARY or TEMP
>>> """
>>> Note that due to the nature of SQL-language function parsing (see section
>>> 35.4) it is not possible to both define and use a temporary table in the
>>> same function.
>>> """
>>>
>>> Add to 35.4 - Query Language (SQL) Functions
>>> 35.4.0 - Parsing Mechanics
>>> (this seems important enough for a sub-section and not just a paragraph
>>> in
>>> the introduction)
>>> """
>>> The body of an SQL function is parsed as if it were a single - multi-part
>>> -
>>> statement and thus uses a constant snapshot of the system catalog for
>>> every
>>> sub-statement therein. Commands that alter the catalog will likely not
>>> work
>>> as expected.
>>>
>>> For example: Issuing "CREATE TEMP TABLE" within an SQL function will add
>>> the
>>> table to the catalog but subsequent statements in the same function will
>>> not
>>> see those additions and thus the temporary table will be invisible to
>>> them.
>>>
>>> Thus it is generally advised that pl/pgsql be used, instead of SQL, when
>>> non-SELECT/INSERT/UPDATE/DELETE statements are required.
>>> """
>>
>> Thanks. The proposed documentation changes are good for the user to
>> understand the behavior.
>
> Although "CREATE TEMP TABLE" probably shouldn't be special-cased for this
> since it is no more or less likely to be mis-used in this way than CREATE
> TYPE or CREATE FUNCTION. The comment in the SQL language area should be
> sufficient as creating such a function can be expected to have at least read
> that section and to have seen the "parsing rules" note that pertains to all
> of these.

How about attached documentation patch as per the discussion?

Regards,
Hari Babu
Fujitsu Australia

(Cc: to pgsql-bugs dropped.)

At 2014-03-17 18:24:55 +1100, kommi(dot)haribabu(at)gmail(dot)com wrote:
>
> *** a/doc/src/sgml/xfunc.sgml
> --- b/doc/src/sgml/xfunc.sgml
> ***************
> *** 153,159 **** SELECT clean_emp();
> --- 153,186 ----
> (<literal>\</>) (assuming escape string syntax) in the body of
> the function (see <xref linkend="sql-syntax-strings">).
> </para>
> +
> + <sect2 id="xfunc-sql-function-parsing-mechanism">
> + <title>Parsing mechanism of a function</title>
>
> + <indexterm>
> + <primary>function</primary>
> + <secondary>parsing mechanism</secondary>
> + </indexterm>

I suggest "Catalog changes within functions" instead of the above title.

> + <para>
> + The body of an SQL function is parsed as if it were a single - multi-part
> + statement and thus uses a constant snapshot of the system catalogs for
> + every sub-statement therein. Commands that alter the catalog will likely not
> + work as expected.
> + </para>
> +
> + <para>
> + For example: Issuing "CREATE TEMP TABLE" within an SQL function will add
> + the table to the catalog but subsequent statements in the same function will
> + not see those additions and thus the temporary table will be invisible to them.
> + </para>
> +
> + <para>
> + Thus it is generally advised that <application>PL/pgSQL</> be used, instead of
> + <acronym>SQL</acronym>, when any catalog visibilities are required in the same function.
> + </para>
> + </sect2>

I don't think that much text is warranted. I suggest something like the
following condensed wording:

<para>
The body of an SQL function is parsed as if it were a single
multi-part statement, using a constant snapshot of the system
catalogs. The effect of any commands that alter the catalogs
(e.g. "CREATE TEMP TABLE") will therefore not be visible to
subsequent commands in the function body.
</para>

<para>
The recommended workaround is to use <application>PL/PgSQL</>.
</para>

Does that seem sensible to you?

-- Abhijit

On Tue, Jun 17, 2014 at 12:30 AM, Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com> wrote:
> (Cc: to pgsql-bugs dropped.)
>
> At 2014-03-17 18:24:55 +1100, kommi(dot)haribabu(at)gmail(dot)com wrote:
>>
>> *** a/doc/src/sgml/xfunc.sgml
>> --- b/doc/src/sgml/xfunc.sgml
>> ***************
>> *** 153,159 **** SELECT clean_emp();
>> --- 153,186 ----
>> (<literal>\</>) (assuming escape string syntax) in the body of
>> the function (see <xref linkend="sql-syntax-strings">).
>> </para>
>> +
>> + <sect2 id="xfunc-sql-function-parsing-mechanism">
>> + <title>Parsing mechanism of a function</title>
>>
>> + <indexterm>
>> + <primary>function</primary>
>> + <secondary>parsing mechanism</secondary>
>> + </indexterm>
>
> I suggest "Catalog changes within functions" instead of the above title.
>
>> + <para>
>> + The body of an SQL function is parsed as if it were a single - multi-part
>> + statement and thus uses a constant snapshot of the system catalogs for
>> + every sub-statement therein. Commands that alter the catalog will likely not
>> + work as expected.
>> + </para>
>> +
>> + <para>
>> + For example: Issuing "CREATE TEMP TABLE" within an SQL function will add
>> + the table to the catalog but subsequent statements in the same function will
>> + not see those additions and thus the temporary table will be invisible to them.
>> + </para>
>> +
>> + <para>
>> + Thus it is generally advised that <application>PL/pgSQL</> be used, instead of
>> + <acronym>SQL</acronym>, when any catalog visibilities are required in the same function.
>> + </para>
>> + </sect2>
>
> I don't think that much text is warranted. I suggest something like the
> following condensed wording:
>
> <para>
> The body of an SQL function is parsed as if it were a single
> multi-part statement, using a constant snapshot of the system
> catalogs. The effect of any commands that alter the catalogs
> (e.g. "CREATE TEMP TABLE") will therefore not be visible to
> subsequent commands in the function body.
> </para>
>
> <para>
> The recommended workaround is to use <application>PL/PgSQL</>.
> </para>
>
> Does that seem sensible to you?

Looks good, Thanks for the review.
Updated patch attached.

Regards,
Hari Babu
Fujitsu Australia

Thanks, I've marked this ready for committer.

-- Abhijit

Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com> writes:
> Updated patch attached.

I revised this a bit more and committed it.

regards, tom lane