problems with currval and rollback

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

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"/>

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

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

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