Forum OpenACS Development: Possible issue with pgsql hack of acs_object_id_seq.nextval

In new-portal/tcl/portal-procs.xql, 2 queries fail because of the
postgres acs_object_id_seq view hack that allows postgres to use
oracle-like syntax for accessing sequences.

The queries are portal::add_element_to_region.template_params_insert,
and portal::add_element_to_region.params_insert.

Both queries are similar.  The first one is:

    insert into portal_element_parameters
    (parameter_id, element_id, config_required_p, configured_p, key,
value)
    select acs_object_id_seq.nextval, :new_element_id,
config_required_p, configured_p, key, value
    from portal_datasource_def_params where datasource_id= :ds_id

The reason the queries fail is that the select may return more than
one row from portal_datasource_def_params.  For each row being
inserted, you would want a different parameter_id (the primary key,
which is being generated from acs_object_id_seq).  However, because
acs_object_id_seq is not a real sequence, but rather is a view on the
sequence t_acs_object_id_seq, postgres only accesses it once in the
above select statement.  When there is more than one row, a duplicate
key violation occurs immediately.

The only way to fix this is to instead say nextval('t_object_id_seq').
Postgres then gives a different value for each row.

Has anyone else notice this type of behavior elsewhere?

Thanks for tracking this down.  If by some chance you're up for a grep session it would be nice to track down any inserts that use this hack.

And if someone has time for 4.6 I think we should retire the hack entirely though writing the proper upgrade scripts to set a new sequence with the right name to the right value would be awfully tedious (we'd probably want to retire the "t_sequence_name" sequences, too).

This is one of those hacks that seemed a beautiful crutch at the time Dan Wickstrom proposed it, but turned out to be a not so great idea. We've been using it from the first OpenACS 3 and PG 6.5 days.  That's how much we all liked it.

The bottom line in PostgreSQL is that we can't depend on the view being evaluated more than once in a query.  Nor can we depend on it being evaluated only once, unfortunately.

The insert queries you're describing worked fine in PG 7.1 but failed in PG 7.2.  But they only *appeared* to work.  It was an optimizer choice and in PG 7.2 the view only gets evaluated once due to some tweaking of optimizer cost estimates.

There's a PG bug here but I've not been able to get the PG team to acknowledge it.  However I *have* determined that the current PG 7.2 behavior is correct - a view should only be evaluated once, pure and simple.  The remaining PG bug is that it still will evaluate such views more than once in certain queries that return multiple rows (just not simple inserts like this).

Lesson of the story - views that generate dynamic values such as the sequence hack views are a bad idea in PG because the behavior's unpredictable.  In standard SQL 92 such views aren't going to do what one hopes anyway.  Two good reasons to avoid them in the future.

But we didn't know that 'way back when!  I, at least, learned a bit about SQL 92 and PG and PG team politics in the process of figuring all this out.