Skip to content

Instantly share code, notes, and snippets.

@copiousfreetime
Created February 5, 2009 22:34
  • Star 15 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Embed
What would you like to do?
Postgres Partitioning with RETURNING on insert
-- 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();
@davidblewett
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment