PG 7.1 pre-beta bug ... - Mailing list pgsql-hackers

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_checkcheck (         (group_module_administration is not null)          and
(group_module_administrationin ('full', 'enabling', 'none')))
 
);
create sequence user_group_sequence;
create table user_groups (       group_id        integer primary key,       group_type      varchar(20) not null
referencesuser_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
notnull 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_idinteger 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
Serviceand other goodies at http://donb.photo.net.