update_partition_test.out
application/octet-stream
Filename: update_partition_test.out
Type: application/octet-stream
Part: 0
Message:
Re: UPDATE of partition key
--===========================================================================================================
--creating test dataset
CREATE TABLE pt (a INT, b INT, c INT) PARTITION BY RANGE(a);
CREATE TABLE
CREATE TABLE pt_p1 PARTITION OF pt FOR VALUES FROM (1) to (6) PARTITION BY RANGE (b);
CREATE TABLE
CREATE TABLE pt_p1_p1 PARTITION OF pt_p1 FOR VALUES FROM (11) to (44);
CREATE TABLE
CREATE TABLE pt_p1_p2 PARTITION OF pt_p1 FOR VALUES FROM (44) to (66);
CREATE TABLE
CREATE TABLE pt_p2 PARTITION OF pt FOR VALUES FROM (6) to (11) PARTITION BY LIST (c);
CREATE TABLE
CREATE TABLE pt_p2_p1 PARTITION OF pt_p2 FOR VALUES IN (666,777,888);
CREATE TABLE
CREATE TABLE pt_p2_p2 PARTITION OF pt_p2 FOR VALUES IN (999,NULL);
CREATE TABLE
INSERT INTO pt (a,b,c) VALUES (1,11,111),(2,22,222),(3,33,333),(4,44,444),(5,55,555);
INSERT 0 5
INSERT INTO pt (a,b,c) VALUES (6,66,666),(7,77,777),(8,88,888),(9,99,999),(10,100,NULL);
INSERT 0 5
--test with updating root partition
--move data within same partition,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET b = 23 WHERE b = 11;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 1 | 23 | 111
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET b = 45,c = 422 WHERE b = 22;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p1_p2 | 2 | 45 | 422
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data different subtree,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET a = 8,c=888 WHERE b = 33;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p1 | 8 | 33 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same partition,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET b = 23, a=13 WHERE b = 11;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:34: ERROR: no partition of relation "pt" found for row
DETAIL: Partition key of the failing row contains (a) = (13).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:35: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET b = 45, a=14 WHERE b = 22;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:40: ERROR: no partition of relation "pt" found for row
DETAIL: Partition key of the failing row contains (a) = (14).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:41: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data different subtree,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET b = 88, c=198 WHERE b = 33;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:46: ERROR: no partition of relation "pt_p1" found for row
DETAIL: Partition key of the failing row contains (b) = (88).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:47: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
----------------------------------------------------------------------------------------------------------
--test with updating child partition
--move data within same partition,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt_p1_p1 SET b = 23 WHERE b = 11;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 1 | 23 | 111
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt_p1 SET b = 45,c = 422 WHERE b = 22;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p1_p2 | 2 | 45 | 422
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,setisfying partition contraint,updating leaf child --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt_p1_p1 SET b = 45 WHERE b = 22;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:66: ERROR: new row for relation "pt_p1_p1" violates partition constraint
DETAIL: Failing row contains (2, 45, 222).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:67: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data different subtree,setisfying partition contraint,updating child partition --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt_p1 SET a = 8,c=888 WHERE b = 33;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:72: ERROR: new row for relation "pt_p1_p1" violates partition constraint
DETAIL: Failing row contains (8, 33, 888).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:73: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data within same partition,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt_p1_p1 SET b = 23, a=13 WHERE b = 11;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:78: ERROR: new row for relation "pt_p1_p1" violates partition constraint
DETAIL: Failing row contains (13, 23, 111).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:79: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt_p1 SET b = 45, a=14 WHERE b = 22;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:84: ERROR: new row for relation "pt_p1" violates partition constraint
DETAIL: Failing row contains (14, 45, 222).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:85: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--===========================================================================================================
--creating test dataset
ALTER TABLE pt_p1 ADD constraint pt_p1_check check(c < 560);
ALTER TABLE
ALTER TABLE pt_p1_p1 add CONSTRAINT pt_p1_p1_uk UNIQUE (c);
ALTER TABLE
ALTER TABLE pt_p1_p2 ADD constraint pt_p1_p2_check check(c > 400);
ALTER TABLE
ALTER TABLE pt_p2_p1 ALTER COLUMN c SET NOT NULL;
ALTER TABLE
--test with updating partition with table contraints.
--move data within same partition,setisfying partition constraint,setisfying table constraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET b = 22 WHERE b = 11;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 1 | 22 | 111
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same partition,setisfying partition constraint,not setisfying table constraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET b = 22, c =222 WHERE b = 11;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:104: ERROR: duplicate key value violates unique constraint "pt_p1_p1_uk"
DETAIL: Key (c)=(222) already exists.
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:105: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--updated data voilating same partition table constraint but good as per new partition constraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET c = NULL WHERE a = 7;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
pt_p2_p2 | 7 | 77 |
(10 rows)
ROLLBACK;
ROLLBACK
--===========================================================================================================
--creating test dataset
CREATE VIEW v_pt AS SELECT * FROM pt;
CREATE VIEW
CREATE VIEW v_pt_p1 AS SELECT * FROM pt_p1;
CREATE VIEW
CREATE VIEW v_pt_p1_p1 AS SELECT * FROM pt_p1_p1;
CREATE VIEW
CREATE VIEW v_pt_p1_p2 AS SELECT * FROM pt_p1_p2;
CREATE VIEW
CREATE VIEW v_pt_p2 AS SELECT * FROM pt_p2;
CREATE VIEW
CREATE VIEW v_pt_p2_p1 AS SELECT * FROM pt_p2_p1;
CREATE VIEW
CREATE VIEW v_pt_p2_p2 AS SELECT * FROM pt_p2_p2;
CREATE VIEW
CREATE VIEW v_pt_ck AS SELECT * FROM pt WHERE 0 = a%2 WITH CHECK OPTION;
CREATE VIEW
--test with updating view refering to partition table and childs.
--move data within same partition,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt SET b = 23 WHERE b = 11;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 1 | 23 | 111
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt SET b = 45,c = 422 WHERE b = 22;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p1_p2 | 2 | 45 | 422
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data different subtree,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt SET a = 8,c=888 WHERE b = 33;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p1 | 8 | 33 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same partition,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt SET b = 23, a=13 WHERE b = 11;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:146: ERROR: no partition of relation "pt" found for row
DETAIL: Partition key of the failing row contains (a) = (13).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:147: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt SET b = 45, a=14 WHERE b = 22;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:152: ERROR: no partition of relation "pt" found for row
DETAIL: Partition key of the failing row contains (a) = (14).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:153: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data different subtree,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt SET b = 88, c=198 WHERE b = 33;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:158: ERROR: no partition of relation "pt_p1" found for row
DETAIL: Partition key of the failing row contains (b) = (88).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:159: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data within same partition,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt_p1_p1 SET b = 23 WHERE b = 11;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 1 | 23 | 111
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,setisfying partition contraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt_p1 SET b = 45,c = 422 WHERE b = 22;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p1_p2 | 2 | 45 | 422
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,setisfying partition contraint,updating leaf child --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt_p1_p1 SET b = 45 WHERE b = 22;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:176: ERROR: new row for relation "pt_p1_p1" violates partition constraint
DETAIL: Failing row contains (2, 45, 222).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:177: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data different subtree,setisfying partition contraint,updating child partition --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt_p1 SET a = 8,c=888 WHERE b = 33;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:182: ERROR: new row for relation "pt_p1_p1" violates partition constraint
DETAIL: Failing row contains (8, 33, 888).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:183: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data within same partition,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt_p1_p1 SET b = 23, a=13 WHERE b = 11;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:188: ERROR: new row for relation "pt_p1_p1" violates partition constraint
DETAIL: Failing row contains (13, 23, 111).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:189: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data within same subtree,different partition,not setisfying partition contraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt_p1 SET b = 45, a=14 WHERE b = 22;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:194: ERROR: new row for relation "pt_p1" violates partition constraint
DETAIL: Failing row contains (14, 45, 222).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:195: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data within same partition,setisfying partition constraint,setisfying table constraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt SET b = 22 WHERE b = 11;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 1 | 22 | 111
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same partition,setisfying partition constraint,not setisfying table constraint --should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt SET b = 22, c =222 WHERE b = 11;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:206: ERROR: duplicate key value violates unique constraint "pt_p1_p1_uk"
DETAIL: Key (c)=(222) already exists.
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:207: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--updated data voilating same partition table constraint but good as per new partition constraint --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt SET c = NULL WHERE a = 7;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
pt_p2_p2 | 7 | 77 |
(10 rows)
ROLLBACK;
ROLLBACK
----------------------------------------------------------------------------------------------------------
--test with updating view refering to partition table with check option.
--move data within same partition, setisfy with check option, should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt_ck SET a = 4 WHERE a = 2;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 4 | 22 | 222
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same partition,not setisfy with check option, should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt_ck SET a = 3 WHERE a = 2;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:226: ERROR: new row violates check option for view "v_pt_ck"
DETAIL: Failing row contains (3, 22, 222).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:227: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--move data to next subtree partition based on next partition key setisfying partition and check option, should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE v_pt_ck SET a = 8, c = 777 WHERE a = 4;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p1 | 8 | 44 | 777
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--===========================================================================================================
--test with updating partition table with returning.
--move data within same partition, should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET b = b + 10 WHERE b = 22 RETURNING a,b,c;
a | b | c
---+----+-----
2 | 32 | 222
(1 row)
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 2 | 32 | 222
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data within same subtree partition directly updating child, should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt_p1_p1 SET b = b + 10 WHERE b = 33 RETURNING a,c-b;
a | ?column?
---+----------
3 | 290
(1 row)
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 43 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
--move data to next subtree partition based on partition key, should fail
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET b = 77 WHERE b = 55 RETURNING b,c,a;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:252: ERROR: no partition of relation "pt_p1" found for row
DETAIL: Partition key of the failing row contains (b) = (77).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:253: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
--returning whole row reference --should pass
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt t1 SET b = b + 10 RETURNING t1;
t1
-------------
(1,21,111)
(2,32,222)
(3,43,333)
(4,54,444)
(5,65,555)
(6,76,666)
(7,87,777)
(8,98,888)
(9,109,999)
(10,110,)
(10 rows)
UPDATE 10
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 21 | 111
pt_p1_p1 | 2 | 32 | 222
pt_p1_p1 | 3 | 43 | 333
pt_p1_p2 | 4 | 54 | 444
pt_p1_p2 | 5 | 65 | 555
pt_p2_p1 | 6 | 76 | 666
pt_p2_p1 | 7 | 87 | 777
pt_p2_p1 | 8 | 98 | 888
pt_p2_p2 | 9 | 109 | 999
pt_p2_p2 | 10 | 110 |
(10 rows)
ROLLBACK;
ROLLBACK
--===========================================================================================================
--creating test dataset
create table part_upd (a int, b int) partition by range(a);
CREATE TABLE
create table part_upd1 partition of part_upd for values from (-50) to (-10);
CREATE TABLE
create table part_upd2 partition of part_upd for values from (-10) to (0);
CREATE TABLE
create table part_upd3 partition of part_upd for values from (0) to (10);
CREATE TABLE
create table part_upd4 partition of part_upd for values from (10) to (50);
CREATE TABLE
insert into part_upd select i,i from generate_series(-30,30,3)i;
INSERT 0 21
select count(*) from part_upd;
count
-------
21
(1 row)
create table non_part_upd (a int);
CREATE TABLE
insert into non_part_upd select i%2 from generate_series(-30,30,5)i;
INSERT 0 13
--testing update with from class, joins, --should pass
update part_upd t1 set a = (t2.a+10) from non_part_upd t2 where t2.a = t1.b;
UPDATE 1
select count(*) from part_upd;
count
-------
21
(1 row)
select tableoid::regclass,* from part_upd;
tableoid | a | b
-----------+-----+-----
part_upd1 | -30 | -30
part_upd1 | -27 | -27
part_upd1 | -24 | -24
part_upd1 | -21 | -21
part_upd1 | -18 | -18
part_upd1 | -15 | -15
part_upd1 | -12 | -12
part_upd2 | -9 | -9
part_upd2 | -6 | -6
part_upd2 | -3 | -3
part_upd3 | 3 | 3
part_upd3 | 6 | 6
part_upd3 | 9 | 9
part_upd4 | 12 | 12
part_upd4 | 15 | 15
part_upd4 | 18 | 18
part_upd4 | 21 | 21
part_upd4 | 24 | 24
part_upd4 | 27 | 27
part_upd4 | 30 | 30
part_upd4 | 10 | 0
(21 rows)
--===========================================================================================================
--creating test dataset
CREATE TABLE pt_trigger(operation varchar NOT NULL, a_old int,b_old int,c_old int,a_new int,b_new int,c_new int);
CREATE TABLE
CREATE OR REPLACE FUNCTION process_pt_trigger1() RETURNS TRIGGER AS $pt_trigger1$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : ROW AFTER DELETE',OLD.a,OLD.b,OLD.c,NULL,NULL,NULL;
RETURN OLD;
END IF;
RETURN NULL;
END;
$pt_trigger1$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger11 AFTER DELETE ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger1();
CREATE TRIGGER
CREATE TRIGGER pt_trigger12 AFTER DELETE ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger1();
CREATE TRIGGER
CREATE TRIGGER pt_trigger13 AFTER DELETE ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger1();
CREATE TRIGGER
CREATE TRIGGER pt_trigger14 AFTER DELETE ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger1();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger2() RETURNS TRIGGER AS $pt_trigger2$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : ROW AFTER UPDATE',OLD.a,OLD.b,OLD.c,NEW.a,NEW.b,NEW.c;
RETURN NEW;
END IF;
RETURN NULL;
END;
$pt_trigger2$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger21 AFTER UPDATE ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger2();
CREATE TRIGGER
CREATE TRIGGER pt_trigger22 AFTER UPDATE ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger2();
CREATE TRIGGER
CREATE TRIGGER pt_trigger23 AFTER UPDATE ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger2();
CREATE TRIGGER
CREATE TRIGGER pt_trigger24 AFTER UPDATE ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger2();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger3() RETURNS TRIGGER AS $pt_trigger3$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : ROW AFTER INSERT',NULL,NULL,NULL,NEW.a,NEW.b,NEW.c;
RETURN NEW;
END IF;
RETURN NULL;
END;
$pt_trigger3$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger31 AFTER INSERT ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger3();
CREATE TRIGGER
CREATE TRIGGER pt_trigger32 AFTER INSERT ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger3();
CREATE TRIGGER
CREATE TRIGGER pt_trigger33 AFTER INSERT ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger3();
CREATE TRIGGER
CREATE TRIGGER pt_trigger34 AFTER INSERT ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger3();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger4() RETURNS TRIGGER AS $pt_trigger4$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : ROW BEFORE DELETE',OLD.a,OLD.b,OLD.c, NULL,NULL,NULL;
RETURN OLD;
END IF;
RETURN NULL;
END;
$pt_trigger4$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger41 BEFORE DELETE ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger4();
CREATE TRIGGER
CREATE TRIGGER pt_trigger42 BEFORE DELETE ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger4();
CREATE TRIGGER
CREATE TRIGGER pt_trigger43 BEFORE DELETE ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger4();
CREATE TRIGGER
CREATE TRIGGER pt_trigger44 BEFORE DELETE ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger4();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger5() RETURNS TRIGGER AS $pt_trigger5$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : ROW BEFORE UPDATE',OLD.a,OLD.b,OLD.c,NEW.a,NEW.b,NEW.c;
RETURN NEW;
END IF;
RETURN NULL;
END;
$pt_trigger5$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger51 BEFORE UPDATE ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger5();
CREATE TRIGGER
CREATE TRIGGER pt_trigger52 BEFORE UPDATE ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger5();
CREATE TRIGGER
CREATE TRIGGER pt_trigger53 BEFORE UPDATE ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger5();
CREATE TRIGGER
CREATE TRIGGER pt_trigger54 BEFORE UPDATE ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger5();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger6() RETURNS TRIGGER AS $pt_trigger6$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : ROW BEFORE INSERT', NULL, NULL, NULL,NEW.a,NEW.b,NEW.c;
RETURN NEW;
END IF;
RETURN NULL;
END;
$pt_trigger6$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger61 BEFORE INSERT ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger6();
CREATE TRIGGER
CREATE TRIGGER pt_trigger62 BEFORE INSERT ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger6();
CREATE TRIGGER
CREATE TRIGGER pt_trigger63 BEFORE INSERT ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger6();
CREATE TRIGGER
CREATE TRIGGER pt_trigger64 BEFORE INSERT ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger6();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger7() RETURNS TRIGGER AS $pt_trigger7$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : STMT AFTER DELETE',NULL,NULL,NULL,NULL,NULL,NULL;
RETURN OLD;
END IF;
RETURN NULL;
END;
$pt_trigger7$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger71 AFTER DELETE ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger7();
CREATE TRIGGER
CREATE TRIGGER pt_trigger72 AFTER DELETE ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger7();
CREATE TRIGGER
CREATE TRIGGER pt_trigger73 AFTER DELETE ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger7();
CREATE TRIGGER
CREATE TRIGGER pt_trigger74 AFTER DELETE ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger7();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger8() RETURNS TRIGGER AS $pt_trigger8$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : STMT AFTER UPDATE',NULL,NULL,NULL,NULL,NULL,NULL;
RETURN NEW;
END IF;
RETURN NULL;
END;
$pt_trigger8$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger81 AFTER UPDATE ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger8();
CREATE TRIGGER
CREATE TRIGGER pt_trigger82 AFTER UPDATE ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger8();
CREATE TRIGGER
CREATE TRIGGER pt_trigger83 AFTER UPDATE ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger8();
CREATE TRIGGER
CREATE TRIGGER pt_trigger84 AFTER UPDATE ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger8();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger9() RETURNS TRIGGER AS $pt_trigger9$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : STMT AFTER INSERT',NULL,NULL,NULL,NULL,NULL,NULL;
RETURN NEW;
END IF;
RETURN NULL;
END;
$pt_trigger9$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger91 AFTER INSERT ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger9();
CREATE TRIGGER
CREATE TRIGGER pt_trigger92 AFTER INSERT ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger9();
CREATE TRIGGER
CREATE TRIGGER pt_trigger93 AFTER INSERT ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger9();
CREATE TRIGGER
CREATE TRIGGER pt_trigger94 AFTER INSERT ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger9();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger10() RETURNS TRIGGER AS $pt_trigger10$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : STMT BEFORE DELETE',NULL,NULL,NULL,NULL,NULL,NULL;
RETURN OLD;
END IF;
RETURN NULL;
END;
$pt_trigger10$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger101 BEFORE DELETE ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger10();
CREATE TRIGGER
CREATE TRIGGER pt_trigger102 BEFORE DELETE ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger10();
CREATE TRIGGER
CREATE TRIGGER pt_trigger103 BEFORE DELETE ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger10();
CREATE TRIGGER
CREATE TRIGGER pt_trigger104 BEFORE DELETE ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger10();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger11() RETURNS TRIGGER AS $pt_trigger11$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : STMT BEFORE UPDATE',NULL,NULL,NULL,NULL,NULL,NULL;
RETURN NEW;
END IF;
RETURN NULL;
END;
$pt_trigger11$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger111 BEFORE UPDATE ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger11();
CREATE TRIGGER
CREATE TRIGGER pt_trigger112 BEFORE UPDATE ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger11();
CREATE TRIGGER
CREATE TRIGGER pt_trigger113 BEFORE UPDATE ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger11();
CREATE TRIGGER
CREATE TRIGGER pt_trigger114 BEFORE UPDATE ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger11();
CREATE TRIGGER
CREATE OR REPLACE FUNCTION process_pt_trigger12() RETURNS TRIGGER AS $pt_trigger12$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO pt_trigger SELECT TG_TABLE_NAME ||' : STMT BEFORE INSERT',NULL,NULL,NULL,NULL,NULL,NULL;
RETURN NEW;
END IF;
RETURN NULL;
END;
$pt_trigger12$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER pt_trigger121 BEFORE INSERT ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger12();
CREATE TRIGGER
CREATE TRIGGER pt_trigger122 BEFORE INSERT ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger12();
CREATE TRIGGER
CREATE TRIGGER pt_trigger123 BEFORE INSERT ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger12();
CREATE TRIGGER
CREATE TRIGGER pt_trigger124 BEFORE INSERT ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger12();
CREATE TRIGGER
--test with updating partition table having triggers.
--move data within same partition,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
DELETE 0
UPDATE pt SET b = 23 WHERE b = 11;
UPDATE 1
SELECT * FROM pt_trigger ORDER BY 1;
operation | a_old | b_old | c_old | a_new | b_new | c_new
-------------------------------+-------+-------+-------+-------+-------+-------
pt_p1_p1 : ROW AFTER UPDATE | 1 | 11 | 111 | 1 | 23 | 111
pt_p1_p1 : ROW BEFORE UPDATE | 1 | 11 | 111 | 1 | 23 | 111
(2 rows)
--move data within same subtree,different partition,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
DELETE 2
UPDATE pt SET b = 45,c = 422 WHERE b = 22;
UPDATE 1
SELECT * FROM pt_trigger ORDER BY 1;
operation | a_old | b_old | c_old | a_new | b_new | c_new
-------------------------------+-------+-------+-------+-------+-------+-------
pt_p1_p1 : ROW AFTER DELETE | 2 | 22 | 222 | | |
pt_p1_p1 : ROW BEFORE DELETE | 2 | 22 | 222 | | |
pt_p1_p1 : ROW BEFORE UPDATE | 2 | 22 | 222 | 2 | 45 | 422
pt_p1_p2 : ROW AFTER INSERT | | | | 2 | 45 | 422
pt_p1_p2 : ROW BEFORE INSERT | | | | 2 | 45 | 422
(5 rows)
--move data different subtree,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
DELETE 5
UPDATE pt SET a = 8,c=888 WHERE b = 33;
UPDATE 1
SELECT * FROM pt_trigger ORDER BY 1;
operation | a_old | b_old | c_old | a_new | b_new | c_new
-------------------------------+-------+-------+-------+-------+-------+-------
pt_p1_p1 : ROW AFTER DELETE | 3 | 33 | 333 | | |
pt_p1_p1 : ROW BEFORE DELETE | 3 | 33 | 333 | | |
pt_p1_p1 : ROW BEFORE UPDATE | 3 | 33 | 333 | 8 | 33 | 888
pt_p2_p1 : ROW AFTER INSERT | | | | 8 | 33 | 888
pt_p2_p1 : ROW BEFORE INSERT | | | | 8 | 33 | 888
(5 rows)
--move data within same partition,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
DELETE 5
UPDATE pt_p1_p1 SET b = 11 WHERE b = 23;
UPDATE 1
SELECT * FROM pt_trigger ORDER BY 1;
operation | a_old | b_old | c_old | a_new | b_new | c_new
-------------------------------+-------+-------+-------+-------+-------+-------
pt_p1_p1 : ROW AFTER UPDATE | 1 | 23 | 111 | 1 | 11 | 111
pt_p1_p1 : ROW BEFORE UPDATE | 1 | 23 | 111 | 1 | 11 | 111
pt_p1_p1 : STMT AFTER UPDATE | | | | | |
pt_p1_p1 : STMT BEFORE UPDATE | | | | | |
(4 rows)
--move data within same subtree,different partition,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
DELETE 4
UPDATE pt_p1 SET b = 22,c = 222 WHERE b = 45;
UPDATE 1
SELECT * FROM pt_trigger ORDER BY 1;
operation | a_old | b_old | c_old | a_new | b_new | c_new
-------------------------------+-------+-------+-------+-------+-------+-------
pt_p1_p1 : ROW AFTER INSERT | | | | 2 | 22 | 222
pt_p1_p1 : ROW BEFORE INSERT | | | | 2 | 22 | 222
pt_p1_p2 : ROW AFTER DELETE | 2 | 45 | 422 | | |
pt_p1_p2 : ROW BEFORE DELETE | 2 | 45 | 422 | | |
pt_p1_p2 : ROW BEFORE UPDATE | 2 | 45 | 422 | 2 | 22 | 222
(5 rows)
--move data different subtree,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
DELETE 5
UPDATE pt SET a = 3,c=333 WHERE b = 33;
UPDATE 1
SELECT * FROM pt_trigger ORDER BY 1;
operation | a_old | b_old | c_old | a_new | b_new | c_new
-------------------------------+-------+-------+-------+-------+-------+-------
pt_p1_p1 : ROW AFTER INSERT | | | | 3 | 33 | 333
pt_p1_p1 : ROW BEFORE INSERT | | | | 3 | 33 | 333
pt_p2_p1 : ROW AFTER DELETE | 8 | 33 | 888 | | |
pt_p2_p1 : ROW BEFORE DELETE | 8 | 33 | 888 | | |
pt_p2_p1 : ROW BEFORE UPDATE | 8 | 33 | 888 | 3 | 33 | 333
(5 rows)
--===========================================================================================================
--test with updating partition table having rule.
--rule at root partition to first level child, all contraint pass --should pass
CREATE RULE pt_rule_ptp1 AS ON UPDATE TO pt DO INSTEAD UPDATE pt_p1 SET a = new.a WHERE a = old.a;
CREATE RULE
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET a = 3 WHERE a = 2;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 3 | 22 | 222
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
DROP RULE pt_rule_ptp1 ON pt;
DROP RULE
--rule at root partition to second level child, all contraint pass --should pass
CREATE RULE pt_rule_ptp1p1 AS ON UPDATE TO pt DO INSTEAD UPDATE pt_p1_p1 SET a = new.a WHERE a = old.a;
CREATE RULE
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET a = 3 WHERE a = 2;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 3 | 22 | 222
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
DROP RULE pt_rule_ptp1p1 ON pt;
DROP RULE
--rule to first level child to second level child, all contraint pass --should pass
CREATE RULE ptp1_rule_ptp1p1 AS ON UPDATE TO pt_p1 DO INSTEAD UPDATE pt_p1_p1 SET a = new.a WHERE a = old.a;
CREATE RULE
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt_p1 SET a = 3 WHERE a = 2;
UPDATE 1
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 3 | 33 | 333
pt_p1_p1 | 3 | 22 | 222
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
ROLLBACK;
ROLLBACK
DROP RULE ptp1_rule_ptp1p1 ON pt_p1;
DROP RULE
--rule at root partition to first level child, some contraint failed --should fail
CREATE RULE pt_rule_ptp1_f AS ON UPDATE TO pt DO INSTEAD UPDATE pt_p1 SET a = new.a, c = new.c WHERE a = old.a;
CREATE RULE
BEGIN;
BEGIN
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
partition_name | a | b | c
----------------+----+-----+-----
pt_p1_p1 | 1 | 11 | 111
pt_p1_p1 | 2 | 22 | 222
pt_p1_p1 | 3 | 33 | 333
pt_p1_p2 | 4 | 44 | 444
pt_p1_p2 | 5 | 55 | 555
pt_p2_p1 | 6 | 66 | 666
pt_p2_p1 | 7 | 77 | 777
pt_p2_p1 | 8 | 88 | 888
pt_p2_p2 | 9 | 99 | 999
pt_p2_p2 | 10 | 100 |
(10 rows)
UPDATE pt SET a = 3, c = 570 WHERE a = 2;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:495: ERROR: new row for relation "pt_p1_p1" violates check constraint "pt_p1_check"
DETAIL: Failing row contains (3, 22, 570).
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:496: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
DROP RULE pt_rule_ptp1_f ON pt;
DROP RULE
--===========================================================================================================
--cleanup test dataset
DROP TABLE pt cascade;
psql:/home/edb/edb_work/v11/update_partition_41903/update_partition_test_pg.sql:501: NOTICE: drop cascades to 8 other objects
DETAIL: drop cascades to view v_pt
drop cascades to view v_pt_ck
drop cascades to view v_pt_p1
drop cascades to view v_pt_p1_p1
drop cascades to view v_pt_p1_p2
drop cascades to view v_pt_p2
drop cascades to view v_pt_p2_p1
drop cascades to view v_pt_p2_p2
DROP TABLE
DROP TABLE pt_trigger;
DROP TABLE
DROP TABLE part_upd;
DROP TABLE
DROP TABLE non_part_upd;
DROP TABLE
DROP FUNCTION process_pt_trigger1 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger2 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger3 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger4 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger5 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger6 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger7 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger8 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger9 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger10 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger11 ();
DROP FUNCTION
DROP FUNCTION process_pt_trigger12 ();
DROP FUNCTION
--===========================================================================================================