Forum OpenACS Q&A: acs_object_id_seq View

Posted by Randy Beggs on
Hi all,

I was trying to add a new group type at admin/group-types/new, with a
supertype of the top level "Group". The insert failed at this sql in
/packages/acs-subsite/tcl/group-type-procs- postgresql.xql::new.copy_rel_types

    insert into group_type_rels 
      (group_rel_type_id, rel_type, group_type)
    select acs_object_id.nextval, r.rel_type, :group_type
    from group_type_rels r
    where r.group_type = :supertype;
    return null;

...which copies a composition and a membership relationship from the "Group" supertype to my new group type. The acs_object_id view is returning one id to be used for the insert, therein the failure.

I changed the sql to "nextval('t_acs_object_id_seq')", and all works fine; but I'm a Postgres newbie, and was wondering if there's a more appropriate fix, or something else that I should be doing instead.

Thanks, Randy

Posted by Don Baccus on
That view was made to ease porting, and you've just nailed the one case where it doesn't work.  PG is correctly returning one value in PG 7.2, while in PG 7.2 it incorrectly expanded the view for each row.

Warning: while the PG 7.2 behavior's correct, it's only by chance that it changed, it thinks it's cheaper in this case where in 7.1 it didn't.  So don't depend on either behavior even though the SQL standard makes it clear that views should be built once per statement.

Please submit a patch ASAP so I can squeeze this into 4.5, it's safe and important.  I thought I'd nailed the cases needing changes but apparently didn't.  It's masked in PG 7.1 and that's been my base for testing thus far.