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

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.
"""

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.