On Fri, Aug 4, 2017 at 10:28 PM, Amit Khandekar <amitdkhan...@gmail.com> wrote:
> > > > Below are the TODOS at this point : > > > > Fix for bug reported by Rajkumar about update with join. > > I had explained the root issue of this bug here : [1] > > Attached patch includes the fix, which is explained below. > Hi Amit, I have applied v14 patch and tested from my side, everything looks good to me. attaching some of test case and out file for reference. Thanks & Regards, Rajkumar Raghuwanshi QMG, EnterpriseDB Corporation
update_partition_test.out
Description: Binary data
--===========================================================================================================
--creating test dataset
CREATE TABLE pt (a INT, b INT, c INT) PARTITION BY RANGE(a);
CREATE TABLE pt_p1 PARTITION OF pt FOR VALUES FROM (1) to (6) PARTITION BY RANGE (b);
CREATE TABLE pt_p1_p1 PARTITION OF pt_p1 FOR VALUES FROM (11) to (44);
CREATE TABLE pt_p1_p2 PARTITION OF pt_p1 FOR VALUES FROM (44) to (66);
CREATE TABLE pt_p2 PARTITION OF pt FOR VALUES FROM (6) to (11) PARTITION BY LIST (c);
CREATE TABLE pt_p2_p1 PARTITION OF pt_p2 FOR VALUES IN (666,777,888);
CREATE TABLE pt_p2_p2 PARTITION OF pt_p2 FOR VALUES IN (999,NULL);
INSERT INTO pt (a,b,c) VALUES (1,11,111),(2,22,222),(3,33,333),(4,44,444),(5,55,555);
INSERT INTO pt (a,b,c) VALUES (6,66,666),(7,77,777),(8,88,888),(9,99,999),(10,100,NULL);
--test with updating root partition
--move data within same partition,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET b = 23 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET b = 45,c = 422 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data different subtree,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET a = 8,c=888 WHERE b = 33;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same partition,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET b = 23, a=13 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET b = 45, a=14 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data different subtree,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET b = 88, c=198 WHERE b = 33;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
----------------------------------------------------------------------------------------------------------
--test with updating child partition
--move data within same partition,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt_p1_p1 SET b = 23 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt_p1 SET b = 45,c = 422 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,setisfying partition contraint,updating leaf child --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt_p1_p1 SET b = 45 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data different subtree,setisfying partition contraint,updating child partition --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt_p1 SET a = 8,c=888 WHERE b = 33;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same partition,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt_p1_p1 SET b = 23, a=13 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt_p1 SET b = 45, a=14 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--===========================================================================================================
--creating test dataset
ALTER TABLE pt_p1 ADD constraint pt_p1_check check(c < 560);
ALTER TABLE pt_p1_p1 add CONSTRAINT pt_p1_p1_uk UNIQUE (c);
ALTER TABLE pt_p1_p2 ADD constraint pt_p1_p2_check check(c > 400);
ALTER TABLE pt_p2_p1 ALTER COLUMN c SET NOT NULL;
--test with updating partition with table contraints.
--move data within same partition,setisfying partition constraint,setisfying table constraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET b = 22 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same partition,setisfying partition constraint,not setisfying table constraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET b = 22, c =222 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--updated data voilating same partition table constraint but good as per new partition constraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET c = NULL WHERE a = 7;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--===========================================================================================================
--creating test dataset
CREATE VIEW v_pt AS SELECT * FROM pt;
CREATE VIEW v_pt_p1 AS SELECT * FROM pt_p1;
CREATE VIEW v_pt_p1_p1 AS SELECT * FROM pt_p1_p1;
CREATE VIEW v_pt_p1_p2 AS SELECT * FROM pt_p1_p2;
CREATE VIEW v_pt_p2 AS SELECT * FROM pt_p2;
CREATE VIEW v_pt_p2_p1 AS SELECT * FROM pt_p2_p1;
CREATE VIEW v_pt_p2_p2 AS SELECT * FROM pt_p2_p2;
CREATE VIEW v_pt_ck AS SELECT * FROM pt WHERE 0 = a%2 WITH CHECK OPTION;
--test with updating view refering to partition table and childs.
--move data within same partition,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt SET b = 23 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt SET b = 45,c = 422 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data different subtree,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt SET a = 8,c=888 WHERE b = 33;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same partition,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt SET b = 23, a=13 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt SET b = 45, a=14 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data different subtree,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt SET b = 88, c=198 WHERE b = 33;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same partition,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt_p1_p1 SET b = 23 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,setisfying partition contraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt_p1 SET b = 45,c = 422 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,setisfying partition contraint,updating leaf child --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt_p1_p1 SET b = 45 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data different subtree,setisfying partition contraint,updating child partition --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt_p1 SET a = 8,c=888 WHERE b = 33;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same partition,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt_p1_p1 SET b = 23, a=13 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree,different partition,not setisfying partition contraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt_p1 SET b = 45, a=14 WHERE b = 22;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same partition,setisfying partition constraint,setisfying table constraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt SET b = 22 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same partition,setisfying partition constraint,not setisfying table constraint --should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt SET b = 22, c =222 WHERE b = 11;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--updated data voilating same partition table constraint but good as per new partition constraint --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt SET c = NULL WHERE a = 7;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
----------------------------------------------------------------------------------------------------------
--test with updating view refering to partition table with check option.
--move data within same partition, setisfy with check option, should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt_ck SET a = 4 WHERE a = 2;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same partition,not setisfy with check option, should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt_ck SET a = 3 WHERE a = 2;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data to next subtree partition based on next partition key setisfying partition and check option, should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE v_pt_ck SET a = 8, c = 777 WHERE a = 4;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--===========================================================================================================
--test with updating partition table with returning.
--move data within same partition, should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET b = b + 10 WHERE b = 22 RETURNING a,b,c;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data within same subtree partition directly updating child, should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt_p1_p1 SET b = b + 10 WHERE b = 33 RETURNING a,c-b;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--move data to next subtree partition based on partition key, should fail
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET b = 77 WHERE b = 55 RETURNING b,c,a;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--returning whole row reference --should pass
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt t1 SET b = b + 10 RETURNING t1;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
--===========================================================================================================
--creating test dataset
create table part_upd (a int, b int) partition by range(a);
create table part_upd1 partition of part_upd for values from (-50) to (-10);
create table part_upd2 partition of part_upd for values from (-10) to (0);
create table part_upd3 partition of part_upd for values from (0) to (10);
create table part_upd4 partition of part_upd for values from (10) to (50);
insert into part_upd select i,i from generate_series(-30,30,3)i;
select count(*) from part_upd;
create table non_part_upd (a int);
insert into non_part_upd select i%2 from generate_series(-30,30,5)i;
--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;
select count(*) from part_upd;
select tableoid::regclass,* from part_upd;
--===========================================================================================================
--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 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 TRIGGER pt_trigger11 AFTER DELETE ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger1();
CREATE TRIGGER pt_trigger12 AFTER DELETE ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger1();
CREATE TRIGGER pt_trigger13 AFTER DELETE ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger1();
CREATE TRIGGER pt_trigger14 AFTER DELETE ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger1();
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 TRIGGER pt_trigger21 AFTER UPDATE ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger2();
CREATE TRIGGER pt_trigger22 AFTER UPDATE ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger2();
CREATE TRIGGER pt_trigger23 AFTER UPDATE ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger2();
CREATE TRIGGER pt_trigger24 AFTER UPDATE ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger2();
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 TRIGGER pt_trigger31 AFTER INSERT ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger3();
CREATE TRIGGER pt_trigger32 AFTER INSERT ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger3();
CREATE TRIGGER pt_trigger33 AFTER INSERT ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger3();
CREATE TRIGGER pt_trigger34 AFTER INSERT ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger3();
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 TRIGGER pt_trigger41 BEFORE DELETE ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger4();
CREATE TRIGGER pt_trigger42 BEFORE DELETE ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger4();
CREATE TRIGGER pt_trigger43 BEFORE DELETE ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger4();
CREATE TRIGGER pt_trigger44 BEFORE DELETE ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger4();
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 TRIGGER pt_trigger51 BEFORE UPDATE ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger5();
CREATE TRIGGER pt_trigger52 BEFORE UPDATE ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger5();
CREATE TRIGGER pt_trigger53 BEFORE UPDATE ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger5();
CREATE TRIGGER pt_trigger54 BEFORE UPDATE ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger5();
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 TRIGGER pt_trigger61 BEFORE INSERT ON pt_p1_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger6();
CREATE TRIGGER pt_trigger62 BEFORE INSERT ON pt_p1_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger6();
CREATE TRIGGER pt_trigger63 BEFORE INSERT ON pt_p2_p1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger6();
CREATE TRIGGER pt_trigger64 BEFORE INSERT ON pt_p2_p2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger6();
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 TRIGGER pt_trigger71 AFTER DELETE ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger7();
CREATE TRIGGER pt_trigger72 AFTER DELETE ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger7();
CREATE TRIGGER pt_trigger73 AFTER DELETE ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger7();
CREATE TRIGGER pt_trigger74 AFTER DELETE ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger7();
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 TRIGGER pt_trigger81 AFTER UPDATE ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger8();
CREATE TRIGGER pt_trigger82 AFTER UPDATE ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger8();
CREATE TRIGGER pt_trigger83 AFTER UPDATE ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger8();
CREATE TRIGGER pt_trigger84 AFTER UPDATE ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger8();
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 TRIGGER pt_trigger91 AFTER INSERT ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger9();
CREATE TRIGGER pt_trigger92 AFTER INSERT ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger9();
CREATE TRIGGER pt_trigger93 AFTER INSERT ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger9();
CREATE TRIGGER pt_trigger94 AFTER INSERT ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger9();
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 TRIGGER pt_trigger101 BEFORE DELETE ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger10();
CREATE TRIGGER pt_trigger102 BEFORE DELETE ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger10();
CREATE TRIGGER pt_trigger103 BEFORE DELETE ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger10();
CREATE TRIGGER pt_trigger104 BEFORE DELETE ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger10();
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 TRIGGER pt_trigger111 BEFORE UPDATE ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger11();
CREATE TRIGGER pt_trigger112 BEFORE UPDATE ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger11();
CREATE TRIGGER pt_trigger113 BEFORE UPDATE ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger11();
CREATE TRIGGER pt_trigger114 BEFORE UPDATE ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger11();
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 TRIGGER pt_trigger121 BEFORE INSERT ON pt_p1_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger12();
CREATE TRIGGER pt_trigger122 BEFORE INSERT ON pt_p1_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger12();
CREATE TRIGGER pt_trigger123 BEFORE INSERT ON pt_p2_p1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger12();
CREATE TRIGGER pt_trigger124 BEFORE INSERT ON pt_p2_p2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger12();
--test with updating partition table having triggers.
--move data within same partition,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
UPDATE pt SET b = 23 WHERE b = 11;
SELECT * FROM pt_trigger ORDER BY 1;
--move data within same subtree,different partition,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
UPDATE pt SET b = 45,c = 422 WHERE b = 22;
SELECT * FROM pt_trigger ORDER BY 1;
--move data different subtree,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
UPDATE pt SET a = 8,c=888 WHERE b = 33;
SELECT * FROM pt_trigger ORDER BY 1;
--move data within same partition,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
UPDATE pt_p1_p1 SET b = 11 WHERE b = 23;
SELECT * FROM pt_trigger ORDER BY 1;
--move data within same subtree,different partition,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
UPDATE pt_p1 SET b = 22,c = 222 WHERE b = 45;
SELECT * FROM pt_trigger ORDER BY 1;
--move data different subtree,setisfying partition contraint --should pass
DELETE FROM pt_trigger;
UPDATE pt SET a = 3,c=333 WHERE b = 33;
SELECT * FROM pt_trigger ORDER BY 1;
--===========================================================================================================
--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;
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET a = 3 WHERE a = 2;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
DROP RULE pt_rule_ptp1 ON pt;
--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;
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET a = 3 WHERE a = 2;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
DROP RULE pt_rule_ptp1p1 ON pt;
--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;
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt_p1 SET a = 3 WHERE a = 2;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
DROP RULE ptp1_rule_ptp1p1 ON pt_p1;
--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;
BEGIN;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
UPDATE pt SET a = 3, c = 570 WHERE a = 2;
SELECT TABLEOID::REGCLASS AS PARTITION_NAME,* FROM pt;
ROLLBACK;
DROP RULE pt_rule_ptp1_f ON pt;
--===========================================================================================================
--cleanup test dataset
DROP TABLE pt cascade;
DROP TABLE pt_trigger;
DROP TABLE part_upd;
DROP TABLE non_part_upd;
DROP FUNCTION process_pt_trigger1 ();
DROP FUNCTION process_pt_trigger2 ();
DROP FUNCTION process_pt_trigger3 ();
DROP FUNCTION process_pt_trigger4 ();
DROP FUNCTION process_pt_trigger5 ();
DROP FUNCTION process_pt_trigger6 ();
DROP FUNCTION process_pt_trigger7 ();
DROP FUNCTION process_pt_trigger8 ();
DROP FUNCTION process_pt_trigger9 ();
DROP FUNCTION process_pt_trigger10 ();
DROP FUNCTION process_pt_trigger11 ();
DROP FUNCTION process_pt_trigger12 ();
--===========================================================================================================
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
