Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    35

    Question Unanswered: returning sequence number upon addition of a new record

    Howdy, all.

    I'm connection to a PostgreSQL db through ADO, and stumbled across a question. I have a table with an ID column that is an autonumbering sequence. When I perform an INSERT command to insert a new record in the table, I add data to all fields but the sequencing field (since it advances itself when the new record is commited).

    Is there an easy way of knowing the sequence number that was created for that new row? Currently, I'm calling the nextval() function before adding the new row, and then adding the row, but it seems to me that this can lead to concurrency problems with a multi-connection database. eg, someone adds a record in the table after I obtain my nextval() but before I add my record.

    Any help would be greatly appreciated.

    Cheers!
    -PJ

  2. #2
    Join Date
    Oct 2003
    Posts
    33
    Hum, it seems to me that using the nextval function automatickly increment the serial. so, when you do so, inserting a row after lauching this function will result in the fact that the row will not have the id you catch, but the next one.
    You should better use the currentval after your insert command.

    And, By the way, if you do all this things in a transaction, you'll never get concurrency problems.

  3. #3
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up a returning Function is bettre

    I've got such prob. the best way to do is to set a function return the inserted value, the autonumbering must be set to default in insert transcation.
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  4. #4
    Join Date
    Sep 2003
    Posts
    35

    thanks

    OK, thanks! I appreciate the advice!

    -PJ

  5. #5
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up Ok , and ...

    Don't forget please to post us you solution !!
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  6. #6
    Join Date
    Sep 2003
    Posts
    35

    solution...and another problem!

    Sorry! I've tried it out by using a transaction and it works great. The transaction allows me to add a new record, and then get its CURRENT (!) value, irregardless whether or not others are adding records too.

    I also like the idea of writing a server side function to return the new serial right away, but at the moment I'm trying to isolate all of my database code to a ActiveX dll.

    But, I noticed something that I can't explain:

    Take two seperate connections to the database and do the following:
    Conn #1: Obtain the Currval() for a table's serial field
    Conn #1: Add a record to a table
    Conn #1: Now obtain the Currval() again. It will be old val + 1
    Conn #2: Obtain the Currval(). It will still be the old val.

    Why is this? Should the sequence not show as oldval + 1 for all other connections?

  7. #7
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Cool How ?

    You can't controle Auto num column 'acuse it cause be altered by an other client !! imagine you have some tens of users getting to that table at the same time !!! you'll have trouble doing it client side , the only way is to get it from a server side function !!
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  8. #8
    Join Date
    Sep 2003
    Posts
    35

    Question Re: How ?

    Sorry, but I'm not sure I understand. I'm not trying to *modify* the sequence column in this case. I only want to know what the current sequence value is, and I thought that Currval() *is* a server-side function (built in).

    In other words, shouldn't any client be able to simply read the current sequence value and obtain the same number? After all, it's just stored in another data file on the server.

  9. #9
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    ah ok

    I've misunderstood your problem man , ok that's the way it works that's ok !
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  10. #10
    Join Date
    Sep 2003
    Posts
    35

    Re: ah ok

    Does anyone know why other clients cannot obtain the most current currval()? It seems that if a client adds a new record (say, #50), and then calls currval() it will respond with the ID for the record just added (50). But if other clients ask for currval() the function returns the old value (49). I can't figure out why.

    Has anyone run into this problem?

  11. #11
    Join Date
    Oct 2003
    Posts
    33

    Re: How ?

    Originally posted by pjme7154
    Sorry, but I'm not sure I understand. I'm not trying to *modify* the sequence column in this case. I only want to know what the current sequence value is, and I thought that Currval() *is* a server-side function (built in).

    In other words, shouldn't any client be able to simply read the current sequence value and obtain the same number? After all, it's just stored in another data file on the server.
    No.

    The currval function is session dependent. To be sure about this, simply try this:
    - Open a new session
    - Create a new sequence
    - Select the currval of this sequence. You'll get an error like this: sequence_name.currval is not yet defined in this session
    - Select the nextval of the same sequence. Now it will work for this session and the currval will give you the same result as the previous nextval.

    - Open a new session and try the currval function. You'll get the same error as before.
    - try the nextval and it will give you the REAL nexval of the sequence.

    In other word, the currval will always give you le last # YOU ask for. This allow you to use it in some foreign keys without any risk.
    If this wasn't so, you could have to kind of problems:

    - Someone could ask another value of the sequence, making you unable to easily reuse yours for your own treatment.
    - Suppose you block the sequence in a transaction, to be sure you won't lost the real currval, anyone else won't be able to take another one till you transaction ends, and this could take a LONG time (suppose you let your transaction open and go to lunch...)

  12. #12
    Join Date
    Sep 2003
    Posts
    35

    I see!

    Great explaination. Thank you. In fact, thanks to all on this thread. I'm still a bit of a newbie with this database, but I'm loving it so far. I hope to return the favors one day.

    -PJ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •