From: | rverghese <riyav(at)hotmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Partitioned postgres tables don't need update trigger?? |
Date: | 2016-06-03 21:03:13 |
Message-ID: | 1464987793976-5906403.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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: http://postgresql.nabble.com/Partitioned-postgres-tables-don-t-need-update-trigger-tp5906403.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | David Steele | 2016-06-03 21:41:35 | Re: [GENERAL] Permission Denied Error on pg_xlog/RECOVERYXLOG file |
Previous Message | Jeff Janes | 2016-06-03 20:16:15 | Re: [pg_trgm] Making similarity(?, ?) < ? use an index |