PostgreSQL Discussion - problems with currval and rollback

Author

problems with currval and rollback

Assad Jarrahian

2005-12-22, 8:24 pm

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

2005-12-22, 8:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Assad Jarrahian

2005-12-22, 8:24 pm

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:
>
> --
> 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
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Bruce Momjian

2005-12-22, 8:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Pandurangan R S

2005-12-23, 3:24 am

Hi,

Refer http://www.postgresql.org/docs/8.1/...s-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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Edward Macnaghten

2005-12-23, 3:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links