*** /home/vagrant/src/postgres/src/test/regress/expected/constraints.out 2018-12-24 23:19:00.678004003 +0000 --- /home/vagrant/src/postgres/src/test/regress/results/constraints.out 2018-12-24 23:19:07.150004003 +0000 *************** *** 475,482 **** BEGIN; -- default is immediate so this should fail right away UPDATE unique_tbl SET i = 1 WHERE i = 0; - ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" - DETAIL: Key (i)=(1) already exists. ROLLBACK; -- check is done at end of statement, so this should succeed UPDATE unique_tbl SET i = i+1; --- 475,480 ---- *************** *** 532,553 **** BEGIN; INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now COMMIT; -- should fail - ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" - DETAIL: Key (i)=(3) already exists. -- make constraint check immediate BEGIN; SET CONSTRAINTS ALL IMMEDIATE; INSERT INTO unique_tbl VALUES (3, 'Three'); -- should fail - ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" - DETAIL: Key (i)=(3) already exists. COMMIT; -- forced check when SET CONSTRAINTS is called BEGIN; SET CONSTRAINTS ALL DEFERRED; INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now SET CONSTRAINTS ALL IMMEDIATE; -- should fail - ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" - DETAIL: Key (i)=(3) already exists. COMMIT; -- test deferrable UNIQUE with a partitioned table CREATE TABLE parted_uniq_tbl (i int UNIQUE DEFERRABLE) partition by range (i); --- 530,545 ---- *************** *** 566,579 **** INSERT INTO parted_uniq_tbl VALUES (1); SAVEPOINT f; INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation - ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key" - DETAIL: Key (i)=(1) already exists. ROLLBACK TO f; SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED; INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit COMMIT; - ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key" - DETAIL: Key (i)=(1) already exists. DROP TABLE parted_uniq_tbl; -- test a HOT update that invalidates the conflicting tuple. -- the trigger should still fire and catch the violation --- 558,567 ---- *************** *** 581,588 **** INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now UPDATE unique_tbl SET t = 'THREE' WHERE i = 3 AND t = 'Three'; COMMIT; -- should fail - ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" - DETAIL: Key (i)=(3) already exists. SELECT * FROM unique_tbl; i | t ---+------- --- 569,574 ---- *************** *** 591,597 **** 5 | one 4 | two 2 | four ! (5 rows) -- test a HOT update that modifies the newly inserted tuple, -- but should succeed because we then remove the other conflicting tuple. --- 577,587 ---- 5 | one 4 | two 2 | four ! 3 | THREE ! 3 | THREE ! 3 | THREE ! 3 | THREE ! (9 rows) -- test a HOT update that modifies the newly inserted tuple, -- but should succeed because we then remove the other conflicting tuple. *************** *** 606,613 **** 5 | one 4 | two 2 | four 3 | threex ! (5 rows) COMMIT; SELECT * FROM unique_tbl; --- 596,607 ---- 5 | one 4 | two 2 | four + 3 | THREE + 3 | THREE + 3 | THREE + 3 | THREE 3 | threex ! (9 rows) COMMIT; SELECT * FROM unique_tbl; *************** *** 617,624 **** 5 | one 4 | two 2 | four 3 | threex ! (5 rows) DROP TABLE unique_tbl; -- --- 611,622 ---- 5 | one 4 | two 2 | four + 3 | THREE + 3 | THREE + 3 | THREE + 3 | THREE 3 | threex ! (9 rows) DROP TABLE unique_tbl; -- *************** *** 668,688 **** INSERT INTO deferred_excl VALUES(1); INSERT INTO deferred_excl VALUES(2); INSERT INTO deferred_excl VALUES(1); -- fail - ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" - DETAIL: Key (f1)=(1) conflicts with existing key (f1)=(1). INSERT INTO deferred_excl VALUES(1) ON CONFLICT ON CONSTRAINT deferred_excl_con DO NOTHING; -- fail ERROR: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters BEGIN; INSERT INTO deferred_excl VALUES(2); -- no fail here COMMIT; -- should fail here - ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" - DETAIL: Key (f1)=(2) conflicts with existing key (f1)=(2). BEGIN; INSERT INTO deferred_excl VALUES(3); INSERT INTO deferred_excl VALUES(3); -- no fail here COMMIT; -- should fail here - ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" - DETAIL: Key (f1)=(3) conflicts with existing key (f1)=(3). -- bug #13148: deferred constraint versus HOT update BEGIN; INSERT INTO deferred_excl VALUES(2, 1); -- no fail here --- 666,680 ---- *************** *** 693,700 **** f1 | f2 ----+---- 1 | 2 | 2 ! (2 rows) ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con; -- This should fail, but worth testing because of HOT updates --- 685,695 ---- f1 | f2 ----+---- 1 | + 1 | + 3 | + 3 | 2 | 2 ! (5 rows) ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con; -- This should fail, but worth testing because of HOT updates ====================================================================== *** /home/vagrant/src/postgres/src/test/regress/expected/triggers.out 2018-12-23 20:41:32.506001000 +0000 --- /home/vagrant/src/postgres/src/test/regress/results/triggers.out 2018-12-24 23:19:07.834004003 +0000 *************** *** 2237,2247 **** group by tgrelid::regclass order by tgrelid::regclass; tgrelid | count ---------------+------- ! trg_clone | 1 ! trg_clone1 | 1 ! trg_clone2 | 1 ! trg_clone3 | 1 ! trg_clone_3_3 | 1 (5 rows) drop table trg_clone; --- 2237,2247 ---- group by tgrelid::regclass order by tgrelid::regclass; tgrelid | count ---------------+------- ! trg_clone | 2 ! trg_clone1 | 2 ! trg_clone2 | 2 ! trg_clone3 | 2 ! trg_clone_3_3 | 2 (5 rows) drop table trg_clone; ======================================================================