A question on postgresql sequences

Dev Shed Forums Sponsor:

June 4th, 2003, 09:13 AM

zapa

Mentat of IX

Join Date: Mar 2003

Location: Bucuresti / Toronto

Posts: 112

Time spent in forums: 29 sec
Reputation Power: 11

A question on postgresql sequences

Hi there ,
I have a question about using postgreSQL sequences .
Whenever I create a sequence and set it's value , after starting another session with postgreSQL that value is lost and the sequence has to be set again .

Is there any way of setting the sequence to retain their values even after the session is reset ?

__________________
FreeBSD , dooing more with less since 10 years ago

June 4th, 2003, 09:59 AM

bcyde

Me likey breadsticks...

Join Date: Jan 2003

Location: Los Angeles

Posts: 1,189

Time spent in forums: 3 Days 1 h 12 m 58 sec
Reputation Power: 17

Can you go into more depth about what you're doing? Are you saying that you create a sequence and then after successfully updating it with setval() it resets after you disconnect and reconnect to the DB?

-b

__________________
PostgreSQL, it's what's for dinner...

June 4th, 2003, 10:20 AM

zapa

Mentat of IX

Join Date: Mar 2003

Location: Bucuresti / Toronto

Posts: 112

Time spent in forums: 29 sec
Reputation Power: 11

here is some terminal output to demonstrate my problem

Code:

mydb=# create sequence cont_seq_nr;
CREATE SEQUENCE
mydb=# select setval('cont_seq_nr' , 13 );
 setval
--------
     13
(1 row)

mydb=# select currval('cont_seq_nr');
 currval
---------
      13
(1 row)

mydb=# \q

$ psql mydb

Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

mydb=# select currval('cont_seq_nr');
ERROR:  cont_seq_nr.currval is not yet defined in this session
mydb=#

June 4th, 2003, 10:43 AM

bcyde

Me likey breadsticks...

Join Date: Jan 2003

Location: Los Angeles

Posts: 1,189

Time spent in forums: 3 Days 1 h 12 m 58 sec
Reputation Power: 17

Thx for the additional info,
Basically, the value of the actual sequence isn't lost it's just that the you can't use the currval until you've selected from the sequence during the current session.

This newsgroup posting probably explains it better than I have.

HTH,
-b

June 4th, 2003, 10:57 AM

zapa

Mentat of IX

Join Date: Mar 2003

Location: Bucuresti / Toronto

Posts: 112

Time spent in forums: 29 sec
Reputation Power: 11

ah ok , i understand

thanks for clearing it all up . It's a bit odd thought , that you can't get currval from a sequence like this .

I was trying to use this as a cheap trick to improve performance on an application that has to show a listing in the format :
"current_record_nr of last_record_nr" . Considering currval takes 0.09 miliseconds as oposed to the full second of count(*) or max(*) it was a good trick ...

oh well i'll work it out somehow

June 4th, 2003, 11:15 AM

bcyde

Me likey breadsticks...

Join Date: Jan 2003

Location: Los Angeles

Posts: 1,189

Time spent in forums: 3 Days 1 h 12 m 58 sec
Reputation Power: 17

If you're using max(*) then that means that there's no deletions allowed, correct? If so, then I'm not sure if it will be more efficient but how about trying to see if a
SELECT your_id_field FROM your_table ORDER BY your_id_field DESC LIMIT 1

How does that compare?
HTH
-b

June 4th, 2003, 11:40 AM

zapa

Mentat of IX

Join Date: Mar 2003

Location: Bucuresti / Toronto

Posts: 112

Time spent in forums: 29 sec
Reputation Power: 11

I used this :

Code:

int currval;
ResultSet rs = stmt.executeQuery("Select nextval('cont_seq_nr') as c");
rs.next();
currval = Integer.parseInt(rs.getString("c"));
currval--;
rs = stmt.executeQuery("Select setval('cont_seq_nr' , " + currval + ")");
return currval;

just as I was done testing it i refreshed this page , saw your sugestion which seems to be 0.05 miliseconds faster in SQL code , my guess is that i'll gain some more speed by simplifying the java code too . Thanks , that was exactly what I needed

A question on postgresql sequences

Developer Shed Advertisers and Affiliates