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] INSERT ... ON CONFLICT feature is not working for TEMP tables. #2061

Open
nocaway opened this issue Aug 14, 2019 · 0 comments

Comments

@nocaway
Copy link
Contributor

commented Aug 14, 2019

Test cases

Two rows instead of one was inserted in the following test.

postgres=# create temporary table selfconflict (f1 int primary key, f2 int);
CREATE TABLE
postgres=# begin transaction isolation level read committed;
BEGIN
postgres=# insert into selfconflict values (1,1), (1,2) on conflict do nothing;
INSERT 0 2
postgres=# commit;
COMMIT

The CORRECT behavior for TEMP table should have been the following.

test=# create temp table selfconflict (f1 int primary key, f2 int);
CREATE TABLE
test=# begin transaction isolation level read committed;
BEGIN
test=# insert into selfconflict values (1,1), (1,2) on conflict do nothing;
INSERT 0 1
test=# commit;
COMMIT
test=# 
test=# begin transaction isolation level repeatable read;
BEGIN
test=# insert into selfconflict values (2,1), (2,2) on conflict do nothing;
INSERT 0 1
test=# commit;
COMMIT
test=# 
test=# begin transaction isolation level serializable;
BEGIN
test=# insert into selfconflict values (3,1), (3,2) on conflict do nothing;
INSERT 0 1
test=# commit;
COMMIT
test=# 
test=# begin transaction isolation level read committed;
BEGIN
test=# insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0;
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
test=# commit;
ROLLBACK
test=# 
test=# begin transaction isolation level repeatable read;
BEGIN
test=# insert into selfconflict values (5,1), (5,2) on conflict(f1) do update set f2 = 0;
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
test=# commit;
ROLLBACK
test=# 
test=# begin transaction isolation level serializable;
BEGIN
test=# insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 = 0;
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
test=# commit;
ROLLBACK
test=# 
test=# select * from selfconflict order by f1;
 f1 | f2 
----+----
  1 |  1
  2 |  1
  3 |  1
(3 rows)

@nocaway nocaway self-assigned this Aug 14, 2019

@nocaway nocaway added this to To do in SQL Support via automation Aug 14, 2019

@nocaway nocaway moved this from To do to In progress in SQL Support Aug 19, 2019

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