Bug in PG 7.2b4 (and b2, for good measure) - Mailing list pgsql-hackers

From Don Baccus
Subject Bug in PG 7.2b4 (and b2, for good measure)
Date
Msg-id 3C1E375E.7060104@pacifier.com
Whole thread Raw
List pgsql-hackers
Apparently there's been a change in the way views are handled within 
PL/pgSQL.  The following program works fine in earlier versions of 
PostgreSQL.  It also works if the select on the view is replaced with a 
direct call to nextval().

We use this construct repeatedly in OpenACS so we can share queries that 
use sequences between Oracle and PostgreSQL unless they differ in other 
ways that can't be reconciled between the two RDBMS's.  Obviously, the 
greater the number of queries we can share in this way, the lower our 
porting, new code development, and code maintenance costs.

As it stands neither the older OpenACS 3.x toolkit nor the upcoming 
OpenACS 4.x toolkit will work with PG 7.2.

Ouch.

Sorry for the long delay in reporting this.  I only recently decided to 
get off my rear end and test against PG 7.2 after Marc Fournier tried to 
do an install and ran into a few problems.

Here's some code.  The problem's caused by the fact that two rows are 
being inserted due to the reference to "multiple rows".  Sequential 
calls to the function which insert single rows works fine:

create sequence test_seq_x;

create view test_seq as  select nextval('test_seq_x') as nextval;

create table data (i integer primary key);

create table multiple_rows (i integer);

insert into multiple_rows values (1);
insert into multiple_rows values (2);

create function f() returns boolean as '
begin
  -- The insert works if you use nextval() instead of the view.
  insert into data    select test_seq.nextval    from multiple_rows;
  return ''true'';

end;' language 'plpgsql';

select f();

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org



pgsql-hackers by date:

Previous
From: Thomas Lockhart
Date:
Subject: Re: [GENERAL] default modifiers for 7.2b4
Next
From: Don Baccus
Date:
Subject: Recursive SQL functions ...