PostgreSQL Trigger to override insert on table with inheritance

inheritancepartitioningpostgresqlpostgresql-9.6trigger

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

Best Answer

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.

Related Solutions

Postgresql – Triggers not being run on a partitioned table

The trigger is AFTER INSERT OR UPDATE. As you can see in your function, it ends with:

RETURN NULL;

That means that the row is discarded and any changes to the row are void. Actually, the row returned from an AFTER trigger is discarded in any case. The RETURN NULL is just a reminder.

You need to do this in a BEFORE trigger or issue an explicit UPDATE statement. The second option is very tricky, however, because you could end up with an endless loop. Better make it a BEFORE trigger.

As an aside: the conditional expression could be simplified with a CASE statement (since version 8.4):

CASE NEW.timeleft
WHEN 'SHORT' THEN
    NEW.expires := NEW.updated + interval '30 minutes';
WHEN 'MEDIUM' THEN
    NEW.expires := NEW.updated + interval '2 hours';
...
ELSE
    RAISE EXCEPTION 'timeleft was unrecognised. ...';
END CASE;

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers                            
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying | 
 foo       | text              | 

db=> \d temp_person_two 
   Column    |         Type         |                            Modifiers                            
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    | 
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.

Best Answer

Related Solutions

Postgresql – Triggers not being run on a partitioned table

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Related Question