Thread: stored procedure namespace bug (critical) + COALECSE notice
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Roman (Averk) Grits
Your email address : averk@nscan.org
System Configuration
---------------------
Architecture (example: Intel Pentium) : AMD Athlon XP
Operating System (example: Linux 2.0.26 ELF) : Red Hat Linux 7.3 @
2.4.18-3
PostgreSQL version (example: PostgreSQL-7.2.1): PostgreSQL-7.2.1
Compiler used (example: gcc 2.95.2) : set up from rpm binary
distribution.
Please enter a FULL description of your problem:
------------------------------------------------
When I create temporary table inside a function overriding persistent one,
usage if it's identifer is bound to persistent table instead of temporary at
the first function in chain. In any descendant calls or code outside the
function temporary table is used. See the code.
Also, COALESCE implementation via CASE suffers much if complex queries
inside it use some table updates (e.g. additions) - it makes them to insert
the data TWICE, leading to very unpleasant results. I had to make another
CASE workaround, but consider making COALESCE more accurate. I guess it's a
bug, so please check how does this case comply with SQL language itself.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
see this (forged-simplified) piece of code:
================================
create table "test_table" ("somedata" text);
create function "test_insert"
(text)
returns bool as'
insert into test_table values ($1);
/* this piece of code inserts data to temporary table*/
select true;
' language sql;
create function "test_select" ()
returns text as'
create temporary table test_table (
"somedata" text);
select test_insert(\'pattern1\');
/* so we have inserted data to newly created table here */
select somedata from test_table;
/* but when we use it inside _this_ function, we refer to persistent table
*/
' language sql;
select test_select();
/* we get NULL here, as there's no data in persistent table */
select somedata from test_table;
/* but we get "pattern1" here as we refer to temporary table that overrides
peristent one, according to documentation*/
================================
While adding few inserts to core functions and dropping test_table after
function call, but before last select I've figured out that test_select()
uses the very first persistent definition while test_insert() and any
clauses outside test_select() use temp definition from test_select(). Seems
like pretty nasty bug - I've spent some time wondering what's up with my
stored procedure code until I guessed it might not be my fault. Please reply
with comments (do you approve or reject this report: COALESCE notice also).
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
haven't tried to fix it yet, got only few SQL workarounds
You have to use EXECUTE when accessing temporary tables in functions
because it is compiled on first access and any table references don't
change if the table changes.
---------------------------------------------------------------------------
Averk wrote:
> ============================================================================
> POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
>
>
> Your name : Roman (Averk) Grits
> Your email address : averk@nscan.org
>
>
> System Configuration
> ---------------------
> Architecture (example: Intel Pentium) : AMD Athlon XP
>
> Operating System (example: Linux 2.0.26 ELF) : Red Hat Linux 7.3 @
> 2.4.18-3
>
> PostgreSQL version (example: PostgreSQL-7.2.1): PostgreSQL-7.2.1
>
> Compiler used (example: gcc 2.95.2) : set up from rpm binary
> distribution.
>
>
> Please enter a FULL description of your problem:
> ------------------------------------------------
>
> When I create temporary table inside a function overriding persistent one,
> usage if it's identifer is bound to persistent table instead of temporary at
> the first function in chain. In any descendant calls or code outside the
> function temporary table is used. See the code.
>
> Also, COALESCE implementation via CASE suffers much if complex queries
> inside it use some table updates (e.g. additions) - it makes them to insert
> the data TWICE, leading to very unpleasant results. I had to make another
> CASE workaround, but consider making COALESCE more accurate. I guess it's a
> bug, so please check how does this case comply with SQL language itself.
>
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>
> see this (forged-simplified) piece of code:
> ================================
> create table "test_table" ("somedata" text);
>
> create function "test_insert"
> (text)
> returns bool as'
> insert into test_table values ($1);
>
> /* this piece of code inserts data to temporary table*/
> select true;
> ' language sql;
>
> create function "test_select" ()
> returns text as'
> create temporary table test_table (
> "somedata" text);
> select test_insert(\'pattern1\');
> /* so we have inserted data to newly created table here */
> select somedata from test_table;
> /* but when we use it inside _this_ function, we refer to persistent table
> */
> ' language sql;
> select test_select();
> /* we get NULL here, as there's no data in persistent table */
> select somedata from test_table;
> /* but we get "pattern1" here as we refer to temporary table that overrides
> peristent one, according to documentation*/
>
> ================================
> While adding few inserts to core functions and dropping test_table after
> function call, but before last select I've figured out that test_select()
> uses the very first persistent definition while test_insert() and any
> clauses outside test_select() use temp definition from test_select(). Seems
> like pretty nasty bug - I've spent some time wondering what's up with my
> stored procedure code until I guessed it might not be my fault. Please reply
> with comments (do you approve or reject this report: COALESCE notice also).
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
> haven't tried to fix it yet, got only few SQL workarounds
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
[ rather delayed response, sorry about that ]
"Averk" <averk@nscan.org> writes:
> When I create temporary table inside a function overriding persistent one,
> usage if it's identifer is bound to persistent table instead of temporary at
> the first function in chain. In any descendant calls or code outside the
> function temporary table is used. See the code.
I don't think this is a namespace issue, but rather a side-effect of the
fact that plpgsql caches query plans upon first use. I'd recommend
using EXECUTE for queries that need to refer to different tables during
different executions.
> Also, COALESCE implementation via CASE suffers much if complex queries
> inside it use some table updates (e.g. additions) - it makes them to insert
> the data TWICE, leading to very unpleasant results. I had to make another
> CASE workaround, but consider making COALESCE more accurate. I guess it's a
> bug, so please check how does this case comply with SQL language itself.
Well, arguably it's not a bug, because the letter of the spec is
2) COALESCE (V1, V2) is equivalent to the following <case specifi-
cation>:
CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END
3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the
following <case specification>:
CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n )
END
However, I don't believe that SQL92 contains any functions with
side-effects, so an implementation that evaluates each argument only
once probably can't be distinguished from the spec by any
spec-conforming test.
We did just commit changes to cause COALESCE to evaluate its arguments
at most once (and NULLIF likewise), because this is clearly the more
desirable behavior in the presence of side-effects, regardless of
whether you consider it 100% compliant to the letter of the spec.
regards, tom lane