Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[YSQL] In a transaction combining YSQL and temporary table, the temporary table txn is committed instead of being rolled back #1546

Open
d-uspenskiy opened this issue Jun 14, 2019 · 1 comment

Comments

@d-uspenskiy
Copy link
Contributor

commented Jun 14, 2019

regular + temp tables in one transaction works incorrectly

Scenario:

  • session_1 creates temp table + regular table with unique index. When starts transaction and inserts some values
postgres=# create table reg_tbl(k int, v int);
CREATE TABLE
postgres=# create unique index on reg_tbl(k);
CREATE INDEX
postgres=# create temp table tmp_tbl(v int);
CREATE TABLE
postgres=# create temp table tmp_tbl(v int);
CREATE TABLE

postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=# insert into tmp_tbl values(1);
INSERT 0 1
  • at this point session_2 inserts value into regular table
postgres=# insert into reg_tbl values(2, 2);
INSERT 0 1
  • session_1 inserts same value and commits transaction
postgres=# insert into reg_tbl values(2, 2);
INSERT 0 1
postgres=# commit;
ERROR:  Error during commit: Operation expired: Transaction expired
  • at this point tmp_tbl has value in spite of the fact transaction was rolled back
postgres=# select * from tmp_tbl;
 v 
---
 1
(1 row)

Note: problem is not 100% reproducible, often session_1 detects that reg_tbl already have value 2 on attemp to insert it. But I was able to reproduce it several times.

@d-uspenskiy

This comment has been minimized.

Copy link
Contributor Author

commented Jun 14, 2019

Comment from @mbautin

I think we should commit the YB transaction first, and only if that succeeds, commit the temporary table transaction.
My understanding is that temporary table transaction commit should never fail.

@d-uspenskiy d-uspenskiy added this to To do in SQL Support via automation Jun 14, 2019

@mbautin mbautin changed the title [YSQL] Postgres commits native transaction instead of rollback [YSQL] In a transaction combining YSQL and temporary table, the temporary table txn is committed instead of being rolled back Jun 16, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
2 participants
You can’t perform that action at this time.