PostgreSQL Trigger to override insert on table with inheritance

Question

We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the correct child table and then return NULL to abort the pipeline so it is only inserted into the child table, though (you guessed it), this makes inserting documents a pain as you have to re-query for the created ids. We've looked into using an INSTEAD OF trigger type, but they cannot be applied to tables, only views.

Here is our current function used by the trigger:

CREATE OR REPLACE FUNCTION create_article_partition_and_insert() RETURNS TRIGGER
 LANGUAGE plpgsql
AS
$$
DECLARE
 partition_date TEXT;
 partition TEXT;
 resultId BIGINT;
BEGIN
 partition_date := to_char(NEW.updated, 'YYYY_MM');
 partition := TG_RELNAME || '_' || partition_date;
 IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = partition)
 THEN
 RAISE NOTICE 'A partition has been created %',partition;
 EXECUTE 'CREATE TABLE ' || partition || ' (LIKE ' || TG_RELNAME || ' INCLUDING ALL) INHERITS (' || TG_RELNAME ||
 '); ';
 END IF;
 RAISE NOTICE 'Inserting into %',partition;
 EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING id;'
 INTO resultId;
 NEW.id := resultId;
 RETURN NEW;
END;
$$;

Is there a better way to partition inserts by time and return the inserted row of the child table without also inserting into the parent table?

Using PG version 9.6.11.

Thank you!

Cross-posting from SO

• Jasen: whe you upgrade to postgres 11 you'll be able to use native partitioning instead of inheritance and this won't be a problem.
• austin_ce: Thanks! We'll look into that but won't be able to upgrade for some time.

Answer 1

You could possibly use lastval() or currval() to get the last value from the sequence. or use nextval() to get the id before the insert and then insert that value explicitly (instead of allowing the default value).

The third option is to ignore that column and instead use a natural key. That you are able to get the ID by doing a select suggests that there is a natural key for this table.

Lastval and currval are private to your connection, so must be issued on the same connection that issued the insert.

• austin_ce: Would race conditions not be an issue using these functions if we have multiple writes in parallel?
• austin_ce: And also, even if using a transaction to potentially solve the race condition, how would this work for bulk insertions?
• Jasen: There are no race conditions to those functions beacuse they only see the results of actions in the session they are executed in (if you are using a connection pooler check the pooler documentation) , also sequences are unaffected by trasactuions.
• austin_ce: Ok, thanks! Going to leave this open for a little bit longer to see if anyone else has any other solutions, but will accept this if not.