Postgresql transactions aint'working

Answered

Hi,

 

I cannot turn off autocommit for postgresql driver. I have added postgresql datasource, set Options - Transaction control: manual.
I opened an sql file with insert then, and executed this insert. Then I made a select and observed inserted data. After thatб I made a rollback and select again, and still observe inserted data, although I didn't perform commit nor press any button or something. What have I missed? How to make rollback work?
Ty

8 comments
Comment actions Permalink

how did you execute command? Via 'run script'? What if you open new query console and try there?

0
Comment actions Permalink

Thank you for your help! I found it, and I couldn't find it myself ever!
I don't really know these idea terms for their windows. I just clicked on the test.sql file in the "Project" window, and it has been opened with some default handler in the new tab, which I dragged to the separate window.

I selected the Tx option "manual", as you advised. I observe, it's trying to perform transactions management after that, but now it fails with another error. I created a test table, then I try to perform the following steps:

1) Insert some value into the table. It's done ok

2) Select * from the table to check whether the value has been inserted. It was inserted ok.

3) Perform rollback to rollback the insert. Ok as well

4) Select * from the table again, to check it returned to its previous state. At this step I am getting an error:

base.base> insert into transactios_test(f1) values('123')
[2020-09-03 20:34:33] 1 row affected in 2 ms
base.base> select * from transactios_test
[2020-09-03 20:34:35] 1 row retrieved starting from 1 in 13 ms (execution: 4 ms, fetching: 9 ms)
base.base> rollback
[2020-09-03 20:34:38] completed in 3 ms
base.base> select * from transactios_test
[2020-09-03 20:34:39] [25P02] ERROR: current transaction is aborted, commands ignored until end of transaction block
[2020-09-03 20:34:39] [3B001] ERROR: savepoint "jdbc_savepoint_4" does not exist

 

0
Comment actions Permalink

You have to END transaction by 'commit' or 'end transaction' statement before executing statements after rollback in 'manual' mode.

0
Comment actions Permalink

Wow! Thank you so much! it works this way.

is it kinda "idea extension"? According to the sql-92 standard, section 4.28: "An SQL-transaction is terminated by a <commit statement> or a <rollback statement>", not a "please end transaction" nor "please stop it" nor anything else...

0
Comment actions Permalink

Looks like it's PostgreSQL requirement, check 'Notes'. psql utility ends transactions automatically by default.

0
Comment actions Permalink

Notes: "Use COMMIT to successfully terminate a transaction. Issuing ROLLBACK when not inside a transaction does no harm, but it will provoke a warning message."
It is exactly the same what is stated in the sql-92 standard. There are 2 ways to terminate a transaction - either with commit or rollback. No need to do anything else, except commit or rollback. Looks like it's an Idea bug. I suppose, they explicitly start a transaction inside, like some legacy code there. Not sure, but according to the error messages, it is so.

0
Comment actions Permalink

I've created an issue on our tracker, please follow and vote.

0
Comment actions Permalink

Thank you!

According to my experience, it will be fixed never.

Btw, it looks like there's an error in your code there. First and second examples looks the same. You probably meant "commit" in the second one.

0

Please sign in to leave a comment.