Re: stored procedure namespace bug (critical) + COALECSE notice - Mailing list pgsql-bugs

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