Partitioned postgres tables don't need update trigger??

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.

Responses

Browse pgsql-general by date

  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