I am working with partitioned tables. I have partitioned based on date and I have the INSERT trigger in place, I don't have an Update or Delete Trigger but both updates and deletes against the master table work correctly. I am not sure how these are working without triggers.
Any insight?
So, this deletes the right number of rows : delete from torque.test_master where tstamp ='2012-08-03 03:00:00';
And this updates the right rows : update torque.test_master set system='zzz' where tstamp ='2012-08-03 04:00:00';
Here are the sample tables.
CREATE TABLE torque.test_master ( testmstr_seq_id bigserial NOT NULL, tstamp timestamp without time zone NOT NULL, system text NOT NULL, CONSTRAINT pk_testmstr_id PRIMARY KEY (testmstr_seq_id) ) WITH ( OIDS=TRUE );
CREATE TABLE torque.test_y2012m08 ( CONSTRAINT pk_test_y2012m08_id PRIMARY KEY (testmstr_seq_id),
CONSTRAINT test_y2012m08_log_tstamp_check CHECK (tstamp >= '2012-08-01 00:00:00'::timestamp without time zone AND tstamp < '2012-09-01 00:00:00'::timestamp without time zone) ) INHERITS (torque.test_master) WITH ( OIDS=TRUE );
CREATE OR REPLACE FUNCTION torque.test_child_insert() RETURNS trigger AS $BODY$ BEGIN
IF ( new.tstamp >= '2012-08-01' AND new.tstamp < '2012-09-01') THEN
INSERT INTO torque.test_y2012m08 VALUES (NEW.*);
ELSEIF ( new.tstamp >= '2015-05-01' AND new.ltstamp < '2015-06-01') THEN
INSERT INTO torque.test_y2015m05 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the test_child_insert() function!';
END IF;
RETURN NULL; END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;
CREATE TRIGGER testmaster_insert_trigger BEFORE INSERT ON torque.test_master FOR EACH ROW EXECUTE PROCEDURE torque.test_child_insert();
Since both UPDATE and DELETE are expecting the record to exist they operate in much the same way SELECT does, queries against the master are also applied to the children (and WHERE clause constraint evaluation is performed). However, INSERTing a new record basically causes an implicit ONLY to be added to the query since by default you cannot issue a single insert and cause multiple records to appear - and there is not WHERE clause. Thus it is necessary to place a trigger on the table named on the INSERT so that the new record is directed to the correct location.
David J.
