Forum OpenACS Development: sequence view not working...

Posted by Dave Bauer on 07/03/01 06:09 AM

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?

2: Response to sequence view not working... (response to 1)

Posted by Dan Wickstrom on 07/03/01 06:42 AM

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=#

3: Response to sequence view not working... (response to 1)

Posted by Roberto Mello on 07/03/01 08:12 AM

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.

4: Response to sequence view not working... (response to 1)

Posted by Dave Bauer on 07/03/01 03:35 PM

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.

5: Response to sequence view not working... (response to 1)

Posted by Dave Bauer on 07/03/01 03:52 PM

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.

6: Response to sequence view not working... (response to 1)

Posted by Vinod Kurup on 07/04/01 06:50 PM

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

7: Response to sequence view not working... (response to 1)

Posted by Roberto Mello on 07/04/01 07:24 PM

"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?

8: Response to sequence view not working... (response to 1)

Posted by Jeff Davis on 07/04/01 07:40 PM

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.

9: Response to sequence view not working... (response to 1)

Posted by Vinod Kurup on 07/04/01 07:57 PM

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)

10: Response to sequence view not working... (response to 1)

Posted by Don Baccus on 07/05/01 02:30 AM

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!