Mailing-List: contact torque-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Developers List" <torque-dev@db.apache.org>
From: Sean Laurent <sean@neuronfarm.com>
Organization: Neuron Farm
To: "Apache Torque Developers List" <torque-dev@db.apache.org>
Subject: Re: Problem w/PostgreSQL Insert
Date: Tue, 17 Feb 2004 12:15:35 -0600
User-Agent: KMail/1.5.3
References: <200402161121.06446.sean@neuronfarm.com>
 <40321185.6000900@backstagetech.com.au>
In-Reply-To: <40321185.6000900@backstagetech.com.au>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200402171215.35819.sean@neuronfarm.com>

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/ReadMsg?listName=torque-dev@db.apache.o
> >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: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org