Re: problems with currval and rollback - Mailing list pgsql-general

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
>