Forum OpenACS Q&A: acs_object_id_seq View
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
begin 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; end;
...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.
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.