Saturday, November 14, 2009

longtime plpgsql misfeature removed

Tom Lane did refactoring of plpgsql source code. These changes are very very important. plpgsql is good language - simple, robust with good error diagnostic. But it had one bizarre behave. plpgsql connects two worlds - procedural ADA like code and SQL statements. Usually there are not problems. But there are one exception - collision of identifiers. Older behave was too simply. Plpgsql identifiers win every-time. It was a source of some bizarre bugs. Look on code: 
postgres=# select * from omega;
 a  
────
 10
 20
 30
(3 rows)

create or replace function foo() 
returns void as $$
#variable_conflict use_variable -- compatible with 8.4 and older
declare a integer; 
begin 
  for a in select a from omega 
  loop 
    raise notice '%', a;
  end loop; 
end; 
$$ language plpgsql;
This code is very simple. Just show content of table omega.
postgres=# select foo();
NOTICE:  <null>
NOTICE:  <null>
NOTICE:  <null>
 foo 
─────
 
(1 row)
or not? Why we don't see values 10,20,30? Because interpret prefer plpgsql identifier against to sql identifier omega.a. This bug is very strange and some time is very difficult to find it. But it is a history. plpgsql 8.5 is much more cleaner. Wrong code raises en exception:
postgres=# 
create or replace function foo() 
returns void as $$
declare a integer;                                              
begin              
  for a in select a from omega 
  loop                         
    raise notice '%', a;
  end loop; 
end; 
$$ language plpgsql;
CREATE FUNCTION
Time: 3,501 ms
postgres=# select foo();
ERROR:  column reference "a" is ambiguous
LINE 1: select a from omega
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  select a from omega
CONTEXT:  PL/pgSQL function "foo" line 3 at FOR over SELECT rows
We could to fix this problem and we get a good answer:
postgres=# 
create or replace function foo() 
returns void as $$
declare a integer; 
begin 
  for a in select omega.a from omega 
  loop 
    raise notice '%', a;
  end loop; 
end; 
$$ language plpgsql;
CREATE FUNCTION
Time: 2,289 ms
postgres=# select foo();
NOTICE:  10
NOTICE:  20
NOTICE:  30
 foo 
─────
 
(1 row)
I am very happy from this changes. Thanks Tom.

2 Comments:

At November 15, 2009 at 6:24 PM , Blogger Johan Chang said...

it's cool. never thought this pattern.

 
At November 20, 2009 at 11:34 AM , Anonymous Log Buffer said...

"Pavel Stehule has some news of a longtime plpgsql misfeature removed. [...]"


Log Buffer #170

 

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home