You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by Sean Laurent <se...@neuronfarm.com> on 2004/02/16 18:21:06 UTC
Problem w/PostgreSQL Insert
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.org&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
------------------------------
Programming is an art form that fights back.
------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Documentation contributions [was: Re: Problem w/PostgreSQL Insert]
Posted by Scott Eade <se...@backstagetech.com.au>.
Sean Laurent wrote:
>Meanwhile, I've been keeping an eye on the Torque-User mailing list as well.
>I have noticed that everyone has a little difficulty getting started with
>Torque, but we all think it's great once it's running. There is a fair
>amount of documentation available, but it seems to be a little daunting for
>most newbies (myself included). Could I offer any assistance with the
>documentation effort?
>
Contributions from everyone are most welcome. You can easily contribute
to the Torque wiki at:
http://wiki.apache.org/db-torque/
Corrections or additions to the online documentation are also welcomed,
preferably as unidiffs to the existing documents (see
http://apache.org/dev/contributors.html#patches), but straight text
updates are a better contribution than nothing at all (you could post
these on the wiki or to the turbine-dev mailing list).
The only way the documentation for Torque is going to get better is if
people make an effort to contribute towards improving it.
BTW: Cross-posting is generally discouraged - in this instance the
message is relevant to both lists.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Documentation contributions [was: Re: Problem w/PostgreSQL Insert]
Posted by Scott Eade <se...@backstagetech.com.au>.
Sean Laurent wrote:
>Meanwhile, I've been keeping an eye on the Torque-User mailing list as well.
>I have noticed that everyone has a little difficulty getting started with
>Torque, but we all think it's great once it's running. There is a fair
>amount of documentation available, but it seems to be a little daunting for
>most newbies (myself included). Could I offer any assistance with the
>documentation effort?
>
Contributions from everyone are most welcome. You can easily contribute
to the Torque wiki at:
http://wiki.apache.org/db-torque/
Corrections or additions to the online documentation are also welcomed,
preferably as unidiffs to the existing documents (see
http://apache.org/dev/contributors.html#patches), but straight text
updates are a better contribution than nothing at all (you could post
these on the wiki or to the turbine-dev mailing list).
The only way the documentation for Torque is going to get better is if
people make an effort to contribute towards improving it.
BTW: Cross-posting is generally discouraged - in this instance the
message is relevant to both lists.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Problem w/PostgreSQL Insert
Posted by Sean Laurent <se...@neuronfarm.com>.
On Wednesday 18 February 2004 03:00 pm, Scott Eade wrote:
> Sean Laurent wrote:
> >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
>
> Can you please post the schema you used for this demonstration. I am
> curious to know if you are telling torque the name of the sequence to
> use (I suspect not).
>
> Have you read the following?
>
>
> http://nagoya.apache.org/wiki/apachewiki.cgi?TorqueProjectPages/PostgreSQLF
>AQ
Sorry it has taken me so long to respond... I've been absolutely swamped and
haven't had an opportunity (until today) to rebuild my test case and play
with this.
I had not read the Wiki page ... in fact, I didn't even know Torque had a
Wiki! I took my basic schema and added the id-method-parameter tag to
specify the sequence name. After a little finagling to make certain I had my
test project setup correctly, I was able to verify that the problem was
solved.
Bottom line: you were right, Scott! I'm a little unhappy with PostgreSQL for
throwing me off for a while there... but I'll survive. *grin*
Meanwhile, I've been keeping an eye on the Torque-User mailing list as well.
I have noticed that everyone has a little difficulty getting started with
Torque, but we all think it's great once it's running. There is a fair
amount of documentation available, but it seems to be a little daunting for
most newbies (myself included). Could I offer any assistance with the
documentation effort?
Thanks again for your patience, Scott.
-Sean
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Problem w/PostgreSQL Insert
Posted by Scott Eade <se...@backstagetech.com.au>.
Sean Laurent wrote:
>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
>
>
Can you please post the schema you used for this demonstration. I am
curious to know if you are telling torque the name of the sequence to
use (I suspect not).
Have you read the following?
http://nagoya.apache.org/wiki/apachewiki.cgi?TorqueProjectPages/PostgreSQLFAQ
>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.
>
>
You are probably right - it most likely just dishes out sequence values
ignoring whether or not they are actually used, thus no concurrency issue.
>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.
>
>
I use PostgreSQL as my primary database server and would thus be very
surprised if this turns out to be an issue.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Problem w/PostgreSQL Insert
Posted by "James A. Hillyerd" <ja...@activerain.com>.
Maybe something has changed between PostgreSQL 7.3 and 7.4 (or Torque
3.0 and 3.1), because I have been running Torque 3.0 with 7.3 in
production for somewhere around a year and can assure you that the
postgres sequences are working fine. :)
-james
--
James A. Hillyerd <ja...@activerain.com>
http://www.activerain.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Problem w/PostgreSQL Insert
Posted by Sean Laurent <se...@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
Re: Problem w/PostgreSQL Insert
Posted by Scott Eade <se...@backstagetech.com.au>.
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.org&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.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org