Forum OpenACS Development: acs_object_id_seq creation speed

In the Postgres version of OpenACS, the acs_object_id_seq is created like so:

create sequence t_acs_object_id_seq;
create view acs_object_id_seq as
select nextval('t_acs_object_id_seq') as nextval;

which seemed to me to be a strange way to implement it. Is this a result of the automated porting from Oracle?

Dirk did some tests on this, and it's about twice as slow as just a straightforward create sequence acs_object_id_seq.

Is there a reason this was done, and why we shouldn't speed up object creation by moving over to a straightforward sequence?

(incidentally, Dirk initially thought caching sequence values would have a speed increase as well, but it turns out this is not the case. Right, Dirk?)

I believe that the view exists so that you can call acs_object_id_seq.nextval.

This needs to be documented better, i.e. somewhere, probably as part of the ad_form documentation. It will bite anyone trying to use ad_form to insert records into a table where the primary key is a sequence but not acs_object_id_seq.

ad_form tries to call yourtable_id.nextval when it inserts a new record and it will it will crash until you dig through the code, discover this trick and define yourtable_id_seq accordingly.

Hey Jade,

Yup, the view/sequence hack was initially used to allow Postgres and Oracle code to have the same syntax: 'select acs_object_id_seq.nextval from dual'. But PG changed over time, so that multiple calls to the view in 1 query only incremented the sequence once, instead of multiple times. So the hack is no longer useful. All TCL code should call db_nextval and SQL code should use the actual sequence (t_acs_object_id_seq), not the view.

getting 10,000 id's one at a time from the view took 2.51s (.25ms per id) and getting 10,000 from the sequence directly one at a time took 2.10s (.21ms per id); I would say it's pretty fast either way and definitely not something thats worth worrying about much.

The view hack is just that, a hack, and using the sequence directly is preferable but from a performance standpoint fixing it is a non-issue.

I did it in psql with output going to /dev/null.

time {db_nextval acs_object_id_seq} 1000

versus

time {db_nextval t_acs_object_id_seq} 1000

shows db_nextval is 6.6ms per id for the sequence versus 13.4ms for the view but that's because it tries the sequence first and then if it does not exist it tries the view (i.e. 2 trips to the db). It should probably just cache which ones it needs to use the view for (then it would be about the same speed).

I really don't like the idea of using t_acs_object_id_seq

Instead, shouldn't we use the same sequence names in Oracle and Postgres? If I use db_nextval acs_object_id_seq on Oracle, I wouldn't even think of using a different name.

Anyway, I guess this isn't really work quibbling over.