#1
  1. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12

    Arrow 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
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    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...
  4. #3
  5. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    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=#
    FreeBSD , dooing more with less since 10 years ago
  6. #4
  7. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    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
    PostgreSQL, it's what's for dinner...
  8. #5
  9. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    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
    FreeBSD , dooing more with less since 10 years ago
  10. #6
  11. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    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
    PostgreSQL, it's what's for dinner...
  12. #7
  13. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    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
    FreeBSD , dooing more with less since 10 years ago

IMN logo majestic logo threadwatch logo seochat tools logo