Thread: Re: [HACKERS] Declarative partitioning - another take

Hi Amit,

Thanks for looking into it. I have applied fixes and checked for triggers. I could see difference in behaviour of statement triggers for INSERT and UPDATE, for insert only root partition triggers are getting fired but for update root as well as child partition table triggers both getting fired. is this expected??

Below are steps to reproduce.

CREATE TABLE pt (a INT, b INT) PARTITION BY RANGE(a);
CREATE TABLE pt1 PARTITION OF pt FOR VALUES FROM (1) to (6);
CREATE TABLE pt2 PARTITION OF pt FOR VALUES FROM (6) to (11);
INSERT INTO pt (a,b) SELECT i,i FROM generate_series(1,7)i;

CREATE TABLE pt_trigger(TG_NAME varchar,TG_TABLE_NAME varchar,TG_LEVEL varchar,TG_WHEN varchar,a_old int,a_new int,b_old int,b_new int);
CREATE FUNCTION process_pt_trigger() RETURNS TRIGGER AS $ttp$
     BEGIN
         IF (TG_OP = 'INSERT') THEN
              IF (TG_LEVEL = 'STATEMENT') THEN INSERT INTO pt_trigger SELECT TG_NAME,TG_TABLE_NAME,TG_LEVEL,TG_WHEN,NULL,NULL,NULL,NULL; END IF;
              IF (TG_LEVEL = 'ROW') THEN INSERT INTO pt_trigger SELECT TG_NAME,TG_TABLE_NAME,TG_LEVEL,TG_WHEN,NULL,NEW.a,NULL,NEW.b; END IF;
      RETURN NEW;
      END IF;
         IF (TG_OP = 'UPDATE') THEN
                IF (TG_LEVEL = 'STATEMENT') THEN INSERT INTO pt_trigger SELECT TG_NAME,TG_TABLE_NAME,TG_LEVEL,TG_WHEN,NULL,NULL,NULL,NULL; END IF;
                IF (TG_LEVEL = 'ROW') THEN INSERT INTO pt_trigger SELECT TG_NAME,TG_TABLE_NAME,TG_LEVEL,TG_WHEN,OLD.a,NEW.a,OLD.b,NEW.b; END IF;
         RETURN NEW;
         END IF;
     END;
$ttp$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_test11 AFTER INSERT OR UPDATE ON pt FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger();
CREATE TRIGGER trigger_test12 AFTER INSERT OR UPDATE ON pt1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger();
CREATE TRIGGER trigger_test13 AFTER INSERT OR UPDATE ON pt2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger();

CREATE TRIGGER trigger_test21 BEFORE INSERT OR UPDATE ON pt FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger();
CREATE TRIGGER trigger_test22 BEFORE INSERT OR UPDATE ON pt1 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger();
CREATE TRIGGER trigger_test23 BEFORE INSERT OR UPDATE ON pt2 FOR EACH STATEMENT EXECUTE PROCEDURE process_pt_trigger();

CREATE TRIGGER trigger_test32 AFTER INSERT OR UPDATE ON pt1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger();
CREATE TRIGGER trigger_test33 AFTER INSERT OR UPDATE ON pt2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger();

CREATE TRIGGER trigger_test42 BEFORE INSERT OR UPDATE ON pt1 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger();
CREATE TRIGGER trigger_test43 BEFORE INSERT OR UPDATE ON pt2 FOR EACH ROW EXECUTE PROCEDURE process_pt_trigger();

postgres=# INSERT INTO pt (a,b) VALUES (8,8);
INSERT 0 1
postgres=# SELECT * FROM pt_trigger;
    tg_name     | tg_table_name | tg_level  | tg_when | a_old | a_new | b_old | b_new
----------------+---------------+-----------+---------+-------+-------+-------+-------
 trigger_test21 | pt            | STATEMENT | BEFORE  |       |       |       |     
 trigger_test43 | pt2           | ROW       | BEFORE  |       |     8 |       |     8
 trigger_test33 | pt2           | ROW       | AFTER   |       |     8 |       |     8
 trigger_test11 | pt            | STATEMENT | AFTER   |       |       |       |     
(4 rows)

postgres=# TRUNCATE TABLE pt_trigger;
TRUNCATE TABLE
postgres=# UPDATE pt SET a = 2 WHERE a = 1;
UPDATE 1
postgres=# SELECT * FROM pt_trigger;
    tg_name     | tg_table_name | tg_level  | tg_when | a_old | a_new | b_old | b_new
----------------+---------------+-----------+---------+-------+-------+-------+-------
 trigger_test21 | pt            | STATEMENT | BEFORE  |       |       |       |     
 trigger_test22 | pt1           | STATEMENT | BEFORE  |       |       |       |     
 trigger_test42 | pt1           | ROW       | BEFORE  |     1 |     2 |     1 |     1
 trigger_test32 | pt1           | ROW       | AFTER   |     1 |     2 |     1 |     1
 trigger_test11 | pt            | STATEMENT | AFTER   |       |       |       |     
 trigger_test12 | pt1           | STATEMENT | AFTER   |       |       |       |     
(6 rows)

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB