Обсуждение: "No Results were returned by the query" exception

I have an issue with "No Results were returned by the query" exception when selecting from a sequence using jdbc:

SELECT nextval('sequence_name')

I had this happen quite a bit before during transfers and then upgraded to the devpgjdbc2.jar.  This seemed to fix
thingsuntil this week. 
The problem has returned again.  I see that somebody had the same issue back in March.  Dave Cramer asked the
individual
to enable DEBUG and send him the output.  I do not see any discussion since then.

I would also like to generate the output, but none of the versions of org.postgresql.Driver that I have include the
setLogLevel()
method specified in the Dave's email.

Is there another JAR file I can use?  Is there a workaround for this?

Sorry I'm not across the actual issue, but have you got a small snippet of Java code to demonstrate the problem (enough
tosee if its inside a transaction etc and how you're executing the statement and looping through the RS)? Also the
versionnumber of the PostgreSQL backend. 

I do the below sort of thing regularly and haven't had a problem. The drivers on http://jdbc.postgresql.org/download
arethe best ones to go for if you're not already using those. 

Cheers,
Joe

> I have an issue with "No Results were returned by the query"
> exception when selecting from a sequence using jdbc:
>
> SELECT nextval('sequence_name')

Thanks for the reply Joe.

I am using PostGreSQL 7.2.1 with JDBC driver of http://jdbc.postgresql.org/download/devpgjdbc2.jar.

Code is:

  public int getNextValue(
      String                           sequenceName )
      throws SQLException {
    checkAll(sequenceName);
    Statement s = null ;
    ResultSet rs = null ;
    try {
      s = connection.createStatement() ;
      rs = s.executeQuery("SELECT nextval('"+sequenceName+"')");
      if (!rs.next()) {
        throw new SQLException("No Rows Returned from Sequence " + sequenceName);
      } //end if
      int returnValue = rs.getInt(1) ;
      rs.close();
      s.close();
      return returnValue ;
    } catch (SQLException e) {
      if (rs != null) {
        rs.close();
      } //end if
      if (s != null) {
        s.close();
      } //end if
      throw e ;
    } //end try
  } //end getNextValue()

And yes I am using it from within a transaction.

What is odd is it is not consistant.  This same code runs fine for a lot of things.

My guess is that there is a variable that is not being reset properly in the driver.  That would explain why
it does not happen all the time.  In this case, I am receiving an error when running the above code after an
INSERT statement on the same connection.

Jon

On Thursday 06 June 2002 04:02 pm, Joe Shevland wrote:
> Sorry I'm not across the actual issue, but have you got a small snippet of
> Java code to demonstrate the problem (enough to see if its inside a
> transaction etc and how you're executing the statement and looping through
> the RS)? Also the version number of the PostgreSQL backend.
>
> I do the below sort of thing regularly and haven't had a problem. The
> drivers on http://jdbc.postgresql.org/download are the best ones to go for
> if you're not already using those.
>
> Cheers,
> Joe

Jon,

then chances are the insert is failing and then the transaction needs to
be rolledback. Once a transaction has an error it cannot be used until
ended, by either rollback, or commit

Dave
On Thu, 2002-06-06 at 20:01, Jon Swinth wrote:
> Thanks for the reply Joe.
>
> I am using PostGreSQL 7.2.1 with JDBC driver of http://jdbc.postgresql.org/download/devpgjdbc2.jar.
>
> Code is:
>
>   public int getNextValue(
>       String                           sequenceName )
>       throws SQLException {
>     checkAll(sequenceName);
>     Statement s = null ;
>     ResultSet rs = null ;
>     try {
>       s = connection.createStatement() ;
>       rs = s.executeQuery("SELECT nextval('"+sequenceName+"')");
>       if (!rs.next()) {
>         throw new SQLException("No Rows Returned from Sequence " + sequenceName);
>       } //end if
>       int returnValue = rs.getInt(1) ;
>       rs.close();
>       s.close();
>       return returnValue ;
>     } catch (SQLException e) {
>       if (rs != null) {
>         rs.close();
>       } //end if
>       if (s != null) {
>         s.close();
>       } //end if
>       throw e ;
>     } //end try
>   } //end getNextValue()
>
> And yes I am using it from within a transaction.
>
> What is odd is it is not consistant.  This same code runs fine for a lot of things.
>
> My guess is that there is a variable that is not being reset properly in the driver.  That would explain why
> it does not happen all the time.  In this case, I am receiving an error when running the above code after an
> INSERT statement on the same connection.
>
> Jon
>
> On Thursday 06 June 2002 04:02 pm, Joe Shevland wrote:
> > Sorry I'm not across the actual issue, but have you got a small snippet of
> > Java code to demonstrate the problem (enough to see if its inside a
> > transaction etc and how you're executing the statement and looping through
> > the RS)? Also the version number of the PostgreSQL backend.
> >
> > I do the below sort of thing regularly and haven't had a problem. The
> > drivers on http://jdbc.postgresql.org/download are the best ones to go for
> > if you're not already using those.
> >
> > Cheers,
> > Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

Dave,

Thanks for the guess, but no the insert is not failing.  If I comment out the call to the sequence then everything
worksfine. 

I only think the insert has something to do with it because I don't get the exception every time a poll the sequence.
I get the exception only in this one place.  And it is the only place I can think of where an insert happens before
a sequence select.  The insert may be irrelevant to the issue, but it is the only thing I can see different from where
things work and don't work.

What I am actually doing is trying to advance the sequence beyond a certain value.  The code actually calls a select
from currval() first and then on exception calls nextval() to see what the sequence is currently at.  The currval()
callis 
failing too.  I had to implement things this way because PostGre will throw an error if the sequence has been newly
created and you try to call currval() on it.  I need to currval() (or nextval()) to make sure that the user is not
tryingto 
set the sequence to a previous value.

I used to have this issue with the non-dev driver all the time.  Especially when I would be making a lot of successive
calls.  Upgrading to the dev driver did away with all the errors until now.

Jon

On Thursday 06 June 2002 05:19 pm, Dave Cramer wrote:
> Jon,
>
> then chances are the insert is failing and then the transaction needs to
> be rolledback. Once a transaction has an error it cannot be used until
> ended, by either rollback, or commit
>
> Dave
>
> On Thu, 2002-06-06 at 20:01, Jon Swinth wrote:
> > Thanks for the reply Joe.
> >
> > I am using PostGreSQL 7.2.1 with JDBC driver of
> > http://jdbc.postgresql.org/download/devpgjdbc2.jar.
> >
> > Code is:
> >
> >   public int getNextValue(
> >       String                           sequenceName )
> >       throws SQLException {
> >     checkAll(sequenceName);
> >     Statement s = null ;
> >     ResultSet rs = null ;
> >     try {
> >       s = connection.createStatement() ;
> >       rs = s.executeQuery("SELECT nextval('"+sequenceName+"')");
> >       if (!rs.next()) {
> >         throw new SQLException("No Rows Returned from Sequence " +
> > sequenceName); } //end if
> >       int returnValue = rs.getInt(1) ;
> >       rs.close();
> >       s.close();
> >       return returnValue ;
> >     } catch (SQLException e) {
> >       if (rs != null) {
> >         rs.close();
> >       } //end if
> >       if (s != null) {
> >         s.close();
> >       } //end if
> >       throw e ;
> >     } //end try
> >   } //end getNextValue()
> >
> > And yes I am using it from within a transaction.
> >
> > What is odd is it is not consistant.  This same code runs fine for a lot
> > of things.
> >
> > My guess is that there is a variable that is not being reset properly in
> > the driver.  That would explain why it does not happen all the time.  In
> > this case, I am receiving an error when running the above code after an
> > INSERT statement on the same connection.
> >
> > Jon
> >
> > On Thursday 06 June 2002 04:02 pm, Joe Shevland wrote:
> > > Sorry I'm not across the actual issue, but have you got a small snippet
> > > of Java code to demonstrate the problem (enough to see if its inside a
> > > transaction etc and how you're executing the statement and looping
> > > through the RS)? Also the version number of the PostgreSQL backend.
> > >
> > > I do the below sort of thing regularly and haven't had a problem. The
> > > drivers on http://jdbc.postgresql.org/download are the best ones to go
> > > for if you're not already using those.
> > >
> > > Cheers,
> > > Joe
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Jon,

Ok, then the next thing to do is to look at the logs from the backend.
See why it is failing on the server.

Dave
On Thu, 2002-06-06 at 20:56, Jon Swinth wrote:
> Dave,
>
> Thanks for the guess, but no the insert is not failing.  If I comment out the call to the sequence then everything
worksfine. 
>
> I only think the insert has something to do with it because I don't get the exception every time a poll the sequence.
> I get the exception only in this one place.  And it is the only place I can think of where an insert happens before
> a sequence select.  The insert may be irrelevant to the issue, but it is the only thing I can see different from
where
> things work and don't work.
>
> What I am actually doing is trying to advance the sequence beyond a certain value.  The code actually calls a select
> from currval() first and then on exception calls nextval() to see what the sequence is currently at.  The currval()
callis 
> failing too.  I had to implement things this way because PostGre will throw an error if the sequence has been newly
> created and you try to call currval() on it.  I need to currval() (or nextval()) to make sure that the user is not
tryingto 
> set the sequence to a previous value.
>
> I used to have this issue with the non-dev driver all the time.  Especially when I would be making a lot of
successive
> calls.  Upgrading to the dev driver did away with all the errors until now.
>
> Jon
>
> On Thursday 06 June 2002 05:19 pm, Dave Cramer wrote:
> > Jon,
> >
> > then chances are the insert is failing and then the transaction needs to
> > be rolledback. Once a transaction has an error it cannot be used until
> > ended, by either rollback, or commit
> >
> > Dave
> >
> > On Thu, 2002-06-06 at 20:01, Jon Swinth wrote:
> > > Thanks for the reply Joe.
> > >
> > > I am using PostGreSQL 7.2.1 with JDBC driver of
> > > http://jdbc.postgresql.org/download/devpgjdbc2.jar.
> > >
> > > Code is:
> > >
> > >   public int getNextValue(
> > >       String                           sequenceName )
> > >       throws SQLException {
> > >     checkAll(sequenceName);
> > >     Statement s = null ;
> > >     ResultSet rs = null ;
> > >     try {
> > >       s = connection.createStatement() ;
> > >       rs = s.executeQuery("SELECT nextval('"+sequenceName+"')");
> > >       if (!rs.next()) {
> > >         throw new SQLException("No Rows Returned from Sequence " +
> > > sequenceName); } //end if
> > >       int returnValue = rs.getInt(1) ;
> > >       rs.close();
> > >       s.close();
> > >       return returnValue ;
> > >     } catch (SQLException e) {
> > >       if (rs != null) {
> > >         rs.close();
> > >       } //end if
> > >       if (s != null) {
> > >         s.close();
> > >       } //end if
> > >       throw e ;
> > >     } //end try
> > >   } //end getNextValue()
> > >
> > > And yes I am using it from within a transaction.
> > >
> > > What is odd is it is not consistant.  This same code runs fine for a lot
> > > of things.
> > >
> > > My guess is that there is a variable that is not being reset properly in
> > > the driver.  That would explain why it does not happen all the time.  In
> > > this case, I am receiving an error when running the above code after an
> > > INSERT statement on the same connection.
> > >
> > > Jon
> > >
> > > On Thursday 06 June 2002 04:02 pm, Joe Shevland wrote:
> > > > Sorry I'm not across the actual issue, but have you got a small snippet
> > > > of Java code to demonstrate the problem (enough to see if its inside a
> > > > transaction etc and how you're executing the statement and looping
> > > > through the RS)? Also the version number of the PostgreSQL backend.
> > > >
> > > > I do the below sort of thing regularly and haven't had a problem. The
> > > > drivers on http://jdbc.postgresql.org/download are the best ones to go
> > > > for if you're not already using those.
> > > >
> > > > Cheers,
> > > > Joe
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

Jon,

Jon Swinth wrote:
 > The code actually calls a select
> from currval() first and then on exception calls nextval() to see what the sequence is currently at.

The description here sounds like you are doing something you cannot do
in Postgres.  If you get an exception/error in postgres you cannot just
catch the exception and continue processing.  Any error in postgres will
abort the transaction, you must first do an explicit rollback() before
you can continue issuing additional sql statements.  So when you say
'and then on excpetion calls ...' it sounds to me like you are trapping
an exception and trying to do additional work.

thanks,
--Barry

First off, thank you Dave and Barry for getting to the bottom of this.  I now understand why it is happening.

Now, I realize that I am probably in the wrong e-mail list, but why in the world would it work that way?
Why is the DB telling me when to commit or rollback my transaction?

This is going to make high volume operations not work very well.  No matter how well I select first before
trying to update or insert, I am going to run into issues of uncommited data from other clients.  This means
that when I try to insert a record because I don't see one already there (i.e. unique key) but there is already
an uncommited one there, I will receive an exception and be forced to rollback all my work.  The DB doesn't
allow me to do the simple step of handling the insert exception, finding the existing record, and simply updating
it and going on.  After all, the uncommited record that was in the way should be commited already because
otherwise the DB would not have known wither to throw an exception (other client commited) or allow the insert
(other client rolled back).

Thanks again for the help.

On Thursday 06 June 2002 09:22 pm, Barry Lind wrote:
> Jon,
>
> Jon Swinth wrote:
>  > The code actually calls a select
> >
> > from currval() first and then on exception calls nextval() to see what
> > the sequence is currently at.
>
> The description here sounds like you are doing something you cannot do
> in Postgres.  If you get an exception/error in postgres you cannot just
> catch the exception and continue processing.  Any error in postgres will
> abort the transaction, you must first do an explicit rollback() before
> you can continue issuing additional sql statements.  So when you say
> 'and then on excpetion calls ...' it sounds to me like you are trapping
> an exception and trying to do additional work.
>
> thanks,
> --Barry

Jon,

One thing to keep in mind is that sequences will commit right away. In
other words if one transaction does a select nextval('sequence') and
get's back the value 1, and another transaction does a select
nextval('sequence') it will get the value 2 back regardless of what the
other transaction does, yes even if it rolls back.

Dave

On Fri, 2002-06-07 at 12:43, Jon Swinth wrote:
> First off, thank you Dave and Barry for getting to the bottom of this.  I now understand why it is happening.
>
> Now, I realize that I am probably in the wrong e-mail list, but why in the world would it work that way?
> Why is the DB telling me when to commit or rollback my transaction?
>
> This is going to make high volume operations not work very well.  No matter how well I select first before
> trying to update or insert, I am going to run into issues of uncommited data from other clients.  This means
> that when I try to insert a record because I don't see one already there (i.e. unique key) but there is already
> an uncommited one there, I will receive an exception and be forced to rollback all my work.  The DB doesn't
> allow me to do the simple step of handling the insert exception, finding the existing record, and simply updating
> it and going on.  After all, the uncommited record that was in the way should be commited already because
> otherwise the DB would not have known wither to throw an exception (other client commited) or allow the insert
> (other client rolled back).
>
> Thanks again for the help.
>
> On Thursday 06 June 2002 09:22 pm, Barry Lind wrote:
> > Jon,
> >
> > Jon Swinth wrote:
> >  > The code actually calls a select
> > >
> > > from currval() first and then on exception calls nextval() to see what
> > > the sequence is currently at.
> >
> > The description here sounds like you are doing something you cannot do
> > in Postgres.  If you get an exception/error in postgres you cannot just
> > catch the exception and continue processing.  Any error in postgres will
> > abort the transaction, you must first do an explicit rollback() before
> > you can continue issuing additional sql statements.  So when you say
> > 'and then on excpetion calls ...' it sounds to me like you are trapping
> > an exception and trying to do additional work.
> >
> > thanks,
> > --Barry
>
>