PG 7.1 pre-beta bug ... 
Author Message
 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

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

Oh, BTW - outer joins ROCK!


psql:t.sql:1: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'users_pkey' for table 'u
psql:t.sql:19: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
for table 'user_group_types'
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
for table 'user_groups'
psql:t.sql:46: NOTICE:  CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)
INSERT 40467 1
INSERT 40468 1
psql:t.sql:83: ERROR:  SS_finalize_plan: plan shouldn't reference subplan's

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
        email_alert_p           char(1) default 'f' check (email_alert_p in
        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
        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 '
  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;
     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, '''', ''t'', ''f'', ''closed'',
     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)
 ('group', 'Group', 'Groups', 'open');

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

  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://www.***.com/ .

Fri, 09 May 2003 10:48:06 GMT
 PG 7.1 pre-beta bug ...


> 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

Fri, 09 May 2003 11:07:20 GMT
 [ 2 post ] 

 Relevant Pages 

1. PG 7.1 pre-beta bug ...

2. HOWTO for pg 7.1 installation from cvs

3. big pg 6.5 and 7.1 problem in simple application

4. Authentication type 5 fails in PG 7.1 to 7.3 connect

5. Too Many Open Files PG 7.1

6. HOWTO for pg 7.1 installation from cvs

7. Docs for beta 7.1

8. Postgresql 7.1 Beta 5, Postmaster dead

9. Beta 7.1 and Solaris

10. pg_dumpall -c (7.1 beta 4 buglet)

11. Problems with Multibyte in 7.1 beta?

12. DB2 v. 7.1 beta - SQL sp problem

Powered by phpBB® Forum Software