Обсуждение: 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