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();
View this message in context:
Partitioned postgres tables don't need update trigger?? Sent from the
PostgreSQL - general mailing list archive at Nabble.com.