0

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

  • whe you upgrade to postgres 11 you'll be able to use native partitioning instead of inheritance and this won't be a problem. – Jasen May 23 at 0:09
  • Thanks! We'll look into that but won't be able to upgrade for some time. – austince May 23 at 14:16
0

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.

  • Would race conditions not be an issue using these functions if we have multiple writes in parallel? – austince May 23 at 14:16
  • And also, even if using a transaction to potentially solve the race condition, how would this work for bulk insertions? – austince May 23 at 17:38
  • 1
    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. – Jasen May 23 at 21:41
  • 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. – austince May 24 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.