INSERT RETURNING vs Partitioning

From PostgreSQL wiki

Jump to: navigation, search

A common complaint with the normal approach of using a BEFORE INSERT trigger for partitioning is that the return NULL; in the trigger prevents the normal functioning of INSERT … RETURNING from working.

One workaround is to revert to using currval() for finding inserted ids; this unfortunately only works for single-row inserts. Another workaround is to let the insert into the parent table succeed, but then immediately delete the row again; this has obvious drawbacks.

Here is an example of an alternative workaround, which uses a view with an INSTEAD trigger:

-- create a simple partitioned table
 
CREATE TABLE mydata_real (id serial, rowdate DATE, content text);
CREATE TABLE mydata_real_y2015 (CHECK (rowdate >= '2015-01-01' AND rowdate < '2016-01-01')) inherits (mydata_real);
CREATE TABLE mydata_real_y2016 (CHECK (rowdate >= '2016-01-01' AND rowdate < '2017-01-01')) inherits (mydata_real);
 
-- block attempts to insert on the actual parent table
 
CREATE FUNCTION mydata_nope() RETURNS TRIGGER LANGUAGE plpgsql
AS $f$
  BEGIN
    raise exception 'insert on wrong table';
    RETURN NULL;
  END;
$f$;
 
CREATE TRIGGER mydata_nope BEFORE INSERT ON mydata_real EXECUTE PROCEDURE mydata_nope();
 
-- create the view, which will be used for all access to the table:
 
CREATE VIEW mydata AS SELECT * FROM mydata_real;
 
-- need to copy any defaults from mydata_real to the view
 
ALTER VIEW mydata ALTER COLUMN id SET DEFAULT NEXTVAL('mydata_real_id_seq');
 
-- this is the actual partition insert trigger:
 
CREATE FUNCTION mydata_partition() RETURNS TRIGGER LANGUAGE plpgsql
AS $f$
  BEGIN
    CASE EXTRACT(YEAR FROM NEW.rowdate)
      WHEN 2015
        THEN INSERT INTO mydata_real_y2015 SELECT NEW.*;
      WHEN 2016
        THEN INSERT INTO mydata_real_y2016 SELECT NEW.*;
      ELSE
        raise exception 'date % out of range', NEW.DATE;
    END CASE;
    RETURN NEW;
  END;
$f$;
 
CREATE TRIGGER mydata_partition instead OF INSERT ON mydata
  FOR each ROW EXECUTE PROCEDURE mydata_partition();
 
-- sample insert
 
INSERT INTO mydata(rowdate,content)
  VALUES ('2015-01-10','foo'),('2015-12-12','bar'),('2016-02-02','baz')
  returning id;
Personal tools