============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Roman (Averk) Grits
Your email address : [EMAIL PROTECTED]
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
