Re: stored procedure namespace bug (critical) + COALECSE notice

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(at)nscan(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073