On Tuesday 17 February 2004 07:05 am, Scott Eade wrote: > Sean Laurent wrote: > >I already posted about this, but no one responded, so I'll try again. > > > >There is a bug in the PostgreSQL adapter code that Tim Regovich pointed > > out in December of 2002: > >http://nagoya.apache.org/eyebrowse/[EMAIL PROTECTED] > >rg&msgId=590783 > > > >The basic idea is that getIDMethodSQL() uses the 'currval' function, which > >causes problems on inserts with an error messages like 'xxx.currval is not > >yet defined in this session.' > > > >According to the PostgreSQL documentation, currval() returns "the value > > most recently obtained by nextval for this sequence in the current > > session. (An error is reported if nextval has never been called for this > > sequence in this session.)" Notice the part in parenthesis. For new > > sessions, nextval() will never have been called and thus currval() won't > > work. > > > >The proper solution is simply to use nextval() instead of currval(). I > > was easily able to change this in my copy of the Torque code and my > > testing indicates that it's now working properly. > > > >What can I do to help make certain this gets fixed in the main Torque > >development line? > > > >-Sean > > You will find if you trace the code that Torque retrieves the id value > after the insert takes place and hence currval() is correct. > > Using currval() has fewer concurrency issues than using nextval(), I > believe this may be why it is used.
I appreciate the response, Scott. However, with all due respect, you're incorrect. To demonstrate this, I created a simple project which contained a single table with two columns: an autoincrement primary key and a value (type double). Initially, the table was completely empty. After initializing Torque, I created a new object, set the value and attempted to save it. I got the following error: org.postgresql.util.PSQLException: ERROR: currval of sequence "mytest_seq" is not yet defined in this session Second, I stepped through the entire code and you are partially correct: the attempt to retrieve the current sequence value occurs after the insert. However, the insert has not technically completed, since it's wrapped in a transaction. Under the hood, the insert should be triggering a call nextval() inside PostgreSQL. Unfortunately, something still doesn't work correctly. I have a couple of guesses, but I'm not certain which, if any, are correct. It could be because the insert and the call to currval are all wrapped in the one giant transaction. It could be because the automatic trigger to nextval() is technically in a different session. I'm not certain. Regardless, the end result is that the call to currval() always fails. I refer you to section 9.11 Sequence-Manipulation Functions in the PostgreSQL 7.4 documentation: http://www.postgresql.org/docs/7.4/interactive/functions-sequence.html ---snip--- nextval() Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value. currval() Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer even if other sessions are executing nextval meanwhile. ---snip--- Please notice that concurrency is not an issue when calling nextval(). The easiest solution is simply to call nextval() instead of currval() in DBPostgres.java:117. So, I respectfully would like to know what I need to do help get this fixed in the main code base. I've already modified my own copy of Torque, but I would prefer to see this fixed everywhere. Your assistance would be greatly appreciated. Thanks. -Sean Laurent --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
