Re: Partitioned postgres tables don't need update trigger??

On Fri, Jun 3, 2016 at 5:03 PM, rverghese <riyav(at)hotmail(dot)com> wrote:

> 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.