Postgres Pro
Downloads
Home   >   mailing lists

Thread: problems with currval and rollback

problems with currval and rollback

From
Assad Jarrahian
Date:
Hi all,
   So I started to make some changes with my code here and there
(nothing to do with the relevant lines) and suddenly currval and
rollback don't work.

          try{
            db.setAutoCommit(false);
            addLM.setInt(1, lm.getOrigin());
                           .......
                     addLM.executeUpdate();
                     sql = db.createStatement();
                      ResultSet result = sql.executeQuery("SELECT
currval('lm_id_seq');");
            if (result. next()){
                db.commit();
                db.setAutoCommit(true);
                         else{
                db.rollback(); //reverse all changes
                db.setAutoCommit(true);
                       }
        catch(SQLException ex){
            try {
                db.rollback();
                db.setAutoCommit(true);
            } catch (SQLException e) {
                throw e;
                       }
            throw ex;
       }

What happens is that the it inserts a db, but cannot get a value back
using currval  (ERROR: ERROR: currval of sequence "lm_id_seq" is not
yet defined in this session 55000  ) , yet the db gets the addlm
inserted.


Shouldn't it rollback?
Furthermore, why would currval suddenly stop working?

Much thanks for your feedback in advance.

-assad

Re: problems with currval and rollback

From
Bruce Momjian
Date:
Uh, you really didn't give us enough information to diagnose this.  I
recommend you find the queries that are being run by Java and type them
into psql to see if they work there.  If not, please show them to us.

---------------------------------------------------------------------------

Assad Jarrahian wrote:
> Hi all,
>    So I started to make some changes with my code here and there
> (nothing to do with the relevant lines) and suddenly currval and
> rollback don't work.
>
>           try{
>             db.setAutoCommit(false);
>             addLM.setInt(1, lm.getOrigin());
>                            .......
>                      addLM.executeUpdate();
>                      sql = db.createStatement();
>                       ResultSet result = sql.executeQuery("SELECT
> currval('lm_id_seq');");
>             if (result. next()){
>                 db.commit();
>                 db.setAutoCommit(true);
>                          else{
>                 db.rollback(); //reverse all changes
>                 db.setAutoCommit(true);
>                        }
>         catch(SQLException ex){
>             try {
>                 db.rollback();
>                 db.setAutoCommit(true);
>             } catch (SQLException e) {
>                 throw e;
>                        }
>             throw ex;
>        }
>
> What happens is that the it inserts a db, but cannot get a value back
> using currval  (ERROR: ERROR: currval of sequence "lm_id_seq" is not
> yet defined in this session 55000  ) , yet the db gets the addlm
> inserted.
>
>
> Shouldn't it rollback?
> Furthermore, why would currval suddenly stop working?
>
> Much thanks for your feedback in advance.
>
> -assad
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: problems with currval and rollback

From
Assad Jarrahian
Date:
I am not sure what do you mean (In terms of what more do you need).
For the java code:
The insert works. (cause i see it in the db).
the currval does not work anymore. (see error message below).
And for some reason, it still inserts into the db, regardless of the
rollback (and setAutocommit(false))

For the SQL code itself
[here is the query being performed]
INSERT INTO lm values (DEFAULT, DEFAULT, DEFAULT, 'jarraa', DEFAULT,
ROW(point(0,0),0), '', 'jarraa','jarraa', '', 'blah', DEFAULT);
SELECT currval('lm_id_seq');

I perform this back to back in the db, and the currval works fine.

What gives? It used to work in the java code, but now it doesn't!
Also, please comment on your thoughts about the rollback.
Much thanks for your help.

-assad


On 12/22/05, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>
> Uh, you really didn't give us enough information to diagnose this.  I
> recommend you find the queries that are being run by Java and type them
> into psql to see if they work there.  If not, please show them to us.
>
> ---------------------------------------------------------------------------
>
> Assad Jarrahian wrote:
> > Hi all,
> >    So I started to make some changes with my code here and there
> > (nothing to do with the relevant lines) and suddenly currval and
> > rollback don't work.
> >
> >             try{
> >                       db.setAutoCommit(false);
> >                       addLM.setInt(1, lm.getOrigin());
> >                            .......
> >                      addLM.executeUpdate();
> >                      sql = db.createStatement();
> >                       ResultSet result = sql.executeQuery("SELECT
> > currval('lm_id_seq');");
> >                       if (result. next()){
> >                               db.commit();
> >                               db.setAutoCommit(true);
> >                          else{
> >                               db.rollback(); //reverse all changes
> >                               db.setAutoCommit(true);
> >                        }
> >               catch(SQLException ex){
> >                       try {
> >                               db.rollback();
> >                               db.setAutoCommit(true);
> >                       } catch (SQLException e) {
> >                               throw e;
> >                        }
> >                       throw ex;
> >          }
> >
> > What happens is that the it inserts a db, but cannot get a value back
> > using currval  (ERROR: ERROR: currval of sequence "lm_id_seq" is not
> > yet defined in this session 55000  ) , yet the db gets the addlm
> > inserted.
> >
> >
> > Shouldn't it rollback?
> > Furthermore, why would currval suddenly stop working?
> >
> > Much thanks for your feedback in advance.
> >
> > -assad
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >        choose an index scan if your joining column's datatypes do not
> >        match
> >
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
>

Re: problems with currval and rollback

From
Bruce Momjian
Date:
Assad Jarrahian wrote:
> I am not sure what do you mean (In terms of what more do you need).
> For the java code:
> The insert works. (cause i see it in the db).
> the currval does not work anymore. (see error message below).
> And for some reason, it still inserts into the db, regardless of the
> rollback (and setAutocommit(false))
>
> For the SQL code itself
> [here is the query being performed]
> INSERT INTO lm values (DEFAULT, DEFAULT, DEFAULT, 'jarraa', DEFAULT,
> ROW(point(0,0),0), '', 'jarraa','jarraa', '', 'blah', DEFAULT);
> SELECT currval('lm_id_seq');
>
> I perform this back to back in the db, and the currval works fine.
>
> What gives? It used to work in the java code, but now it doesn't!
> Also, please comment on your thoughts about the rollback.
> Much thanks for your help.

I recommend you ask jdbc questions on the jdbc email list.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: problems with currval and rollback

From
Pandurangan R S
Date:
Hi,

Refer http://www.postgresql.org/docs/8.1/static/functions-sequence.html

On 12/23/05, Assad Jarrahian <jarraa@gmail.com> wrote:
> Hi all,
>    So I started to make some changes with my code here and there
> (nothing to do with the relevant lines) and suddenly currval and
> rollback don't work.
>
>               try{
>                         db.setAutoCommit(false);
>                         addLM.setInt(1, lm.getOrigin());
>                            .......
>                      addLM.executeUpdate();
>                      sql = db.createStatement();
>                       ResultSet result = sql.executeQuery("SELECT
> currval('lm_id_seq');");
>                         if (result. next()){
>                                 db.commit();
>                                 db.setAutoCommit(true);
>                          else{
>                                 db.rollback(); //reverse all changes
>                                 db.setAutoCommit(true);
>                        }
>                 catch(SQLException ex){
>                         try {
>                                 db.rollback();
>                                 db.setAutoCommit(true);
>                         } catch (SQLException e) {
>                                 throw e;
>                        }
>                         throw ex;
>            }
>
> What happens is that the it inserts a db, but cannot get a value back
> using currval  (ERROR: ERROR: currval of sequence "lm_id_seq" is not
> yet defined in this session 55000  ) , yet the db gets the addlm
> inserted.
>
>
> Shouldn't it rollback?
> Furthermore, why would currval suddenly stop working?
>
> Much thanks for your feedback in advance.
>
> -assad
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>


--
Regards
Pandu

Re: problems with currval and rollback

From
Edward Macnaghten
Date:
Without seeing the SQL statements you executed in the session it is
difficult to see your problem.

Getting a current sequence after a rollback is no problem (in 8.0
anyway).  Please note though, the sequence itself is NOT rolled back.
This is correct behaviour.  Currval will return the last sequence
retrieved for the session (using nextval),  even if other sessions have
accessed the sequence in the intervening time.

Eddy


Assad Jarrahian wrote:

>Hi all,
>   So I started to make some changes with my code here and there
>(nothing to do with the relevant lines) and suddenly currval and
>rollback don't work.
>
>
>
>
<snip content="code"/>

>What happens is that the it inserts a db, but cannot get a value back
>using currval  (ERROR: ERROR: currval of sequence "lm_id_seq" is not
>yet defined in this session 55000  ) , yet the db gets the addlm
>inserted.
>
>
>Shouldn't it rollback?
>Furthermore, why would currval suddenly stop working?
>
>Much thanks for your feedback in advance.
>
>-assad
>
>