Partitioned postgres tables don't need update trigger??

Поиск
Список
Период
Сортировка
От rverghese
Тема Partitioned postgres tables don't need update trigger??
Дата
Msg-id 1464987793976-5906403.post@n5.nabble.com
обсуждение исходный текст
Ответы Re: Partitioned postgres tables don't need update trigger??  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список 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: Partitioned postgres tables don't need update trigger??
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: [pg_trgm] Making similarity(?, ?) < ? use an index
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Partitioned postgres tables don't need update trigger??