I decided that perhaps it was time to toss the current OpenACS datamodel
at PG 7.1 to see what would happen (it's a bit shy of 10K lines, including
comments and white space).

All went well except for a handful of occurances of the following error:

ERROR:  SS_finalize_plan: plan shouldn't reference subplan's variable

The code in question does something like:

insert into foo (key, name)
select (nextval('key_sequence', 'some_value')
where not exists (select 1 from foo where name='some_value');

The key field is the primary key.  The name field is constrained unique.
The check is to avoid getting a duplicate insertion error if the name
isn't unique.  Since this is a script which loads initial data into
the system, in essence this check allows the script to avoid flooding the
user with errors if they run it twice.

 From the error message it would appear that perhaps the plan for the insert
is referencing table "foo" from the subselect, and someone doesn't think
that's
kosher.

Here's the actual sequence of events with a self-contained example at the end.

Oh, BTW - outer joins ROCK!

[pgtest@gyrfalcon pgtest]$ 
[pgtest@gyrfalcon pgtest]$ createdb test
CREATE DATABASE
[pgtest@gyrfalcon pgtest]$ createlang plpgsql test
[pgtest@gyrfalcon pgtest]$ psql test -f t.sql
psql:t.sql:1: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'users_pkey' for table 'u
sers'
CREATE
psql:t.sql:19: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'user_group_types_pkey' 
for table 'user_group_types'
CREATE
CREATE
psql:t.sql:46: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'user_groups_pkey' for t
able 'user_groups'
psql:t.sql:46: NOTICE:  CREATE TABLE/UNIQUE will create implicit index
'user_groups_short_name_key' 
for table 'user_groups'
psql:t.sql:46: NOTICE:  CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
CREATE
CREATE
INSERT 40467 1
INSERT 40468 1
psql:t.sql:83: ERROR:  SS_finalize_plan: plan shouldn't reference subplan's
variable
[pgtest@gyrfalcon pgtest]$ more t.sql
create table users (user_id integer primary key);


create table user_group_types (
        group_type      varchar(20) primary key,
        pretty_name     varchar(50) not null,
        pretty_plural   varchar(50) not null,
        approval_policy varchar(30) not null,
        default_new_member_policy       varchar(30) default 'open' not null,
        group_module_administration     varchar(20) default 'none',
        has_virtual_directory_p         char(1) default 'f'
check(has_virtual_directory_p in ('t','f
')),
        group_type_public_directory     varchar(200),
        group_type_admin_directory      varchar(200),
        group_public_directory          varchar(200),
        group_admin_directory           varchar(200)
        constraint group_type_module_admin_check check (
          (group_module_administration is not null)
           and (group_module_administration in ('full', 'enabling', 'none')))
);
 
create sequence user_group_sequence;
create table user_groups (
        group_id        integer primary key,
        group_type      varchar(20) not null references user_group_types,
        group_name      varchar(100),
        short_name      varchar(100) unique not null,
        admin_email     varchar(100),
        registration_date       datetime not null,
        creation_user           integer not null references users(user_id),
        creation_ip_address     varchar(50) not null,
        approved_p      char(1) check (approved_p in ('t','f')),
        active_p        char(1) default 't' check(active_p in ('t','f')),
        existence_public_p      char(1) default 't' check
(existence_public_p in ('t','f')),
        new_member_policy       varchar(30) default 'open' not null,
        spam_policy             varchar(30) default 'open' not null,
        constraint user_groups_spam_policy_check check(spam_policy in
('open','closed','wait')),
        email_alert_p           char(1) default 'f' check (email_alert_p in
('t','f')),
        multi_role_p    char(1) default 'f' check (multi_role_p in ('t','f')),
        group_admin_permissions_p   char(1) default 'f' check
(group_admin_permissions_p in ('t','f'
)),
        index_page_enabled_p    char(1) default 'f' check
(index_page_enabled_p in ('t','f')),
        body                    lztext,
        html_p                  char(1) default 'f' check (html_p in
('t','f')),
        modification_date   datetime,
        modifying_user      integer references users,
        parent_group_id integer references user_groups(group_id)
);
-- index parent_group_id to make parent lookups quick!
create index user_groups_parent_grp_id_idx on user_groups(parent_group_id);

create function user_group_add (varchar, varchar, varchar, varchar)
RETURNS integer AS '
DECLARE
  v_group_type alias for $1;
  v_pretty_name alias for $2;
  v_short_name alias for $3;
  v_multi_role_p alias for $4;
  v_system_user_id  integer; 
BEGIN
     v_system_user_id := 1;
     -- create the actual group
     insert into user_groups 
      (group_id, group_type, short_name, group_name, creation_user,
creation_ip_address, approved_p,
 existence_public_p, new_member_policy, multi_role_p)
     select nextval(''user_group_sequence''), v_group_type, v_short_name,
       v_pretty_name, v_system_user_id, ''0.0.0.0'', ''t'', ''f'', ''closed'',
       v_multi_role_p
     where not exists (select * from user_groups
       where upper(short_name) = upper(v_short_name));

       RETURN 1;
end;' language 'plpgsql';

insert into users (user_id) values(1);

insert into user_group_types
 (group_type, pretty_name, pretty_plural, approval_policy)
values
 ('group', 'Group', 'Groups', 'open');

select user_group_add('group', 'shortname', 'prettyname', 'f');

[pgtest@gyrfalcon pgtest]$ 



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Sun, 19 Nov 2000 18:44:27 -0800
Don Baccus <dhogaza@pacifier.com>
PG 7.1 pre-beta bug ...
Don Baccus <dhogaza@pacifier.com> writes:
> All went well except for a handful of occurances of the following error:
> ERROR:  SS_finalize_plan: plan shouldn't reference subplan's variable

This is probably my fault --- will look at it.

Appreciate the self-contained example...

			regards, tom lane

Sun, 19 Nov 2000 22:05:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PG 7.1 pre-beta bug ... 
Don Baccus <dhogaza@pacifier.com> writes:
> All went well except for a handful of occurances of the following error:
> ERROR:  SS_finalize_plan: plan shouldn't reference subplan's variable

Fixed, I believe.  Your test case now gives

regression=# select user_group_add('group', 'shortname', 'prettyname', 'f');
ERROR:  ExecAppend: Fail to add null value in not null attribute registration_date

but that's correct AFAICT, and 7.0.2 agrees...

			regards, tom lane

Mon, 20 Nov 2000 19:20:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PG 7.1 pre-beta bug ... 
At 07:20 PM 11/20/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>> All went well except for a handful of occurances of the following error:
>> ERROR:  SS_finalize_plan: plan shouldn't reference subplan's variable
>
>Fixed, I believe.  Your test case now gives
>
>regression=# select user_group_add('group', 'shortname', 'prettyname', 'f');
>ERROR:  ExecAppend: Fail to add null value in not null attribute
registration_date
>
>but that's correct AFAICT, and 7.0.2 agrees...

Yeah, I boiled down my example a bit too far for the case where the RDBMS
works, apparently :)   There's probably a trigger to fill the registration
date that I stripped out, something like that.

Thanks ... I'll not be able to get back to testing until later this week (I'm
busy with a client site, Oracle-based, boo-hoo).



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Mon, 20 Nov 2000 16:45:21 -0800
Don Baccus <dhogaza@pacifier.com>
Re: PG 7.1 pre-beta bug ... 