Home
The Toolkit for Online Communities
17474 Community Members, 1 member online, 2462 visitors today
Log In Register
OpenACS Home : Forums : OpenACS Development : sequence view not working...

Forum OpenACS Development: sequence view not working...

Icon of envelope Request notifications

Collapse
Posted by Dave Bauer on
Here is an example from static-pages-create.sql

<pre>
create sequence sp_session_id_seq;
create view sp_session_id_sequence as select nextval('sp_session_id_seq') as nextval;
</pre>

What am I doing wrong?

Collapse
Posted by Dan Wickstrom on
I'm not sure what you mean. It seems to work fine:

openacs4=# create sequence sp_session_id_seq;
CREATE
openacs4=# create view sp_session_id_sequence as select nextval('sp_session_id_seq') as nextval;
CREATE
openacs4=# select sp_session_id_sequence.nextval;
 nextval
---------
       1
(1 row)
 
openacs4=# select sp_session_id_sequence.nextval;
 nextval
---------
       2
(1 row)
 
openacs4=#   

Collapse
Posted by Roberto Mello on
I was talking to Dave about this on IRC (BTW, excellent place for porters to hang around and ask/answer questions). I fixed this sequence-without-a-view in CVS.

It looks like your problem is your version of PostgreSQL. It works fine for me too.

Collapse
Posted by Dave Bauer on
I upgraded from 7.1.0 to 7.1.2 and it still. I will have to double check that I am using the correct version.
Collapse
Posted by Dave Bauer on
I think I figured it out.

I was doing this

select sp_session_id_seq.nextval;

instead of

select sp_session_id_sequence.nextval;

It is not clear that you need to change the db_nextval code to refer to the next sequence name.

Since this is called from a TCL page and is not pulled into a query file, should be change the Oracle data-model to use the same sequence name? That is sp_session_id_sequence instead of sp_session_id_seq.

Collapse
Posted by Vinod Kurup on
Since this is called from a TCL page and is not pulled into a query file, should be change the Oracle data-model to use the same sequence name? That is sp_session_id_sequence instead of sp_session_id_seq.

Yup. The name that you choose for the oracle sequence should be the same as the postgres view

Collapse
Posted by Roberto Mello on
"Yup. The name that you choose for the oracle sequence should be the same as the postgres view "

That makes sense to me, but is this being done? And is this what we should do (rename Oracle sequences to match the names of the views in PG)? Don? Dan?

Collapse
Posted by Jeff Davis on
The one problem I can see with renaming them to _sequence
is that some will go over the 30 character limit in oracle.
ACS_ATTRIBUTE_VALUE_ID_SEQ and AD_TEMPLATE_SAMPLE_USERS_SEQ
both would be too long.
Collapse
Posted by Vinod Kurup on
Well, what i've been doing so far is to leave the oracle sequence name
untouched and to make the postgres view match the oracle sequence. Of
course, this isn't always consistent because in some packages the
oracle sequence is _seq and in some it's _sequence.

So, if we want consistency across packages, we should probably use
_seq (for length considerations)

Collapse
Posted by Don Baccus on
Vinod's right - the notion is to leave the Oracle sequence name untouched (in general, we want to leave the Oracle datamodel untouched  except for bug fixes and enhancements).  Give the Postgres view the same name.

As everyone knows, I'm a bear for consistency and due to length considerations, _seq is preferable to _sequence in Oracle names.  But,  I'm not so anal about consistency that I'll ask all porters to weed out names in existing Oracle datamodel code inherited from aD.  If you want to, sure, do so, though!