Created
February 5, 2009 22:34
-
-
Save copiousfreetime/59067 to your computer and use it in GitHub Desktop.
Postgres Partitioning with RETURNING on insert
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- A method to have RETURNING work if you are partitioning data using trigger. | |
-- The method to this madness is: | |
-- | |
-- 1) Use the normal trigger mechanism to insert the data into the child tables, but | |
-- Instead of the trigger function returning NULL so that the row does not get⋅ | |
-- inserted into the master table, it returns the row inserted into the child | |
-- table | |
-- | |
-- 2) Postgres will insert the new row from the trigger into the master table | |
-- | |
-- 3) Have an 'after insert' trigger on the master table that deletes from the⋅ | |
-- master table with RETURNING. | |
-- | |
-- This allows for the following type of statement to insert into the master table: | |
--⋅ | |
INSERT INTO TABLE measurement( city_id, logdate, peaktemp, unitsales )⋅ | |
VALUES ( 42, 'today'::date, 12, 400 ) RETURNING *; | |
-- And the row will be partitioned into the appropriate child table | |
--- Master measurement table | |
CREATE TABLE measurement ( | |
id int not null, | |
city_id int not null, | |
logdate date not null, | |
peaktemp int, | |
unitsales int | |
); | |
-- Child partition tables | |
CREATE SEQUENCE measurement_y2007m02_seq start with 1 increment by 1 minvalue 1 cache 1; | |
CREATE TABLE measurement_y2007m02 ( | |
id int nextval('measurement_y2007m02_seq'::regclass), | |
CHECK ( logdate >= DATE '2007-02-01' AND logdate < DATE '2007-03-01' ) | |
) INHERITS (measurement); | |
CREATE SEQUENCE measurement_y2007m03_seq start with 1 increment by 1 minvalue 1 cache 1; | |
CREATE TABLE measurement_y2007m03 ( | |
id int nextval('measurement_y2007m03_seq'::regclass), | |
CHECK ( logdate >= DATE '2007-03-01' AND logdate < DATE '2007-04-01' | |
) | |
) INHERITS (measurement); | |
... | |
CREATE SEQUENCE measurement_y2009m01_seq start with 1 increment by 1 minvalue 1 cache 1; | |
CREATE TABLE measurement_y2009m01 ( | |
id int nextval('measurement_y2009m01_seq'::regclass), | |
CHECK ( logdate >= DATE '2009-01-01' AND logdate < DATE '2009-02-01' ) | |
) INHERITS (measurement); | |
-- Trigger function to split out between the various child partition tables | |
CREATE OR REPLACE FUNCTION measurement_insert_trigger() | |
RETURNS TRIGGER AS $$ | |
DECLARE | |
r measurement%rowtype; | |
BEGIN | |
IF ( NEW.logdate >= DATE '2007-02-01' AND NEW.logdate < DATE '2007-03-01' ) THEN | |
INSERT INTO measurement_y2006m02 VALUES (NEW.*) RETURNING * INTO r; | |
ELSIF ( NEW.logdate >= DATE '2007-03-01' AND NEW.logdate < DATE '2007-04-01' ) THEN | |
INSERT INTO measurement_y2007m03 VALUES (NEW.*) RETURNING * INTO r; | |
... | |
ELSIF ( NEW.logdate >= DATE '2009-01-01' AND NEW.logdate < DATE '2009-02-01' ) THEN | |
INSERT INTO measurement_y2009m01 VALUES (NEW.*) RETURNING * INTO r; | |
ELSE | |
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; | |
END IF; | |
RETURN r; | |
END; | |
$$ | |
LANGUAGE plpgsql; | |
-- Trigger to invoke the insert trigger | |
CREATE TRIGGER insert_measurement_trigger | |
BEFORE INSERT ON measurement | |
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger(); | |
-- Trigger function to delete from the master table after the insert | |
CREATE OR REPLACE FUNCTION measurement_delete_master() RETURNS trigger | |
AS $$ | |
DECLARE | |
r measurement%rowtype; | |
BEGIN | |
DELETE FROM ONLY measurement where id = new.id returning * into r; | |
RETURN r; | |
end; | |
$$ | |
LANGUAGE plpgsql; | |
-- Create the after insert trigger | |
create trigger after_insert_measurement_trigger | |
after insert on measurement | |
for each row | |
execute procedure measurement_delete_master(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@ReinsBrain: RhodiumToad in #postgresql on FreeNode came up with this method that uses views instead: https://gist.github.com/RhodiumToad/b82aac9aa4e3fbdda967d89b1e418aa4 . In this case, the view would become the target of all activity (select/insert/update/delete).