PG 7.1 pre-beta bug ...

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(at)gyrfalcon pgtest]$
[pgtest(at)gyrfalcon pgtest]$ createdb test
CREATE DATABASE
[pgtest(at)gyrfalcon pgtest]$ createlang plpgsql test
[pgtest(at)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(at)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(at)gyrfalcon pgtest]$

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