Skip to content

Instantly share code, notes, and snippets.

@copiousfreetime
Created February 5, 2009 22:34
Show Gist options
  • Save copiousfreetime/59067 to your computer and use it in GitHub Desktop.
Save copiousfreetime/59067 to your computer and use it in GitHub Desktop.
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();
@cweibel
Copy link

cweibel commented Apr 23, 2013

The missing link to what I needed, thanks for posting this.

@tmccarty
Copy link

tmccarty commented May 1, 2013

Thanks for the workaround - just what we needed.

@travijuu
Copy link

travijuu commented Oct 8, 2013

if you say RETURN r in trigger function, this will cause to insert same row both partitioned table and master table.

@nuklea
Copy link

nuklea commented Oct 25, 2013

Have better solution?

@coderdan
Copy link

coderdan commented Nov 4, 2013

@travliju yes that is right. This solution won't work :(

@aherok
Copy link

aherok commented Jan 23, 2014

@travijuu, @coderdan, there is measurement_delete_master trigger, which sort of handles this situation - deletes row from master table.

@ReinsBrain
Copy link

We need a better solution to this problem. Another alternative suggested is partitioning using rules but there are some disadvantages that make the solution suggested by @copiousfreetime preferable in the cases I've encountered.

@travijuu, @coderdan - line 82 RETURN r seems like you'll end up with the same problem but I tested and it works as proposed - probably because it's an after insert trigger not a before insert trigger...

@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