2

Let's say I have started to keep a log of all the postcards that I send so I define the table postcards:

                                            Table "postcards"
       Column         |            Type             |                Modifiers                 | Storage
----------------------+-----------------------------+------------------------------------------+----------
postcard_code         | bigint                      | not null                                 | plain    
postcard_recipient    | character varying(750)      | not null                                 | extended 
date_posted           | timestamp without time zone |                                          | plain    

Triggers:
    insert_date_posted BEFORE INSERT ON postcards FOR EACH ROW EXECUTE PROCEDURE insert_date_posted()
    update_date_posted BEFORE UPDATE ON postcards FOR EACH ROW EXECUTE PROCEDURE update_date_posted()

Child tables:
    postcards_unsent,
    postcards_2018,
    postcards_2019,
    postcards_2020

Because of the amount of postcards that I plan to send I have set up partitions via table inheritance so that all the postcards that I send this year will be logged in postcards_2018 and so on. The child tables are defined as, for example:

                                            Table "postcards_2018"
       Column         |            Type             |                Modifiers                 | Storage  
----------------------+-----------------------------+------------------------------------------+----------
postcard_code         | bigint                      | not null                                 | plain    
postcard_recipient    | character varying(750)      | not null                                 | extended 
date_posted           | timestamp without time zone |                                          | plain    

Indexes:
    "postcards_2018_pkey" PRIMARY KEY, btree ( postcard_code, postcard_recipient )

Check constraints:
    "postcards_2018_date_posted_check" CHECK (
    date_posted >= '2018-01-01 00:00:00'::timestamp without time zone AND
    date_posted < '2019-01-01 00:00:00'::timestamp without time zone )

Inherits: postcards

Everytime I buy a new postcard and send it, I add it to the table using:

INSERT INTO postcards ( postcard_code, postcard_recipient, date_posted )
VALUES ( 1, 'Adam', current_date );

The trigger insert_date_posted calls the trigger procedure insert_date_posted() and writes the row in the correct partition. The trigger procedure insert_date_posted() is:

CREATE OR REPLACE FUNCTION insert_date_posted()
    RETURNS trigger AS
$BODY$
BEGIN
    IF ( NEW.date_posted IS NULL ) THEN
        INSERT INTO postcards_unsent VALUES ( NEW.* );
    IF ( NEW.date_posted  >= '2018-01-01' AND NEW.date_posted < '2019-01-01') THEN
        INSERT INTO postcards_2018 values ( NEW.* );
    ELSIF ( NEW.date_posted  >= '2019-01-01' AND NEW.date_posted < '2020-01-01') THEN
        INSERT INTO postcards_2019 values ( NEW.* );
    ELSIF ( NEW.date_posted  >= '2020-01-01' AND NEW.date_posted < '2021-01-01') THEN
        INSERT INTO postcards_2020 values ( NEW.* );
    ELSE
        RAISE EXCEPTION 'Date out of range. Check postcards_insert_trigger() procedure.';
    END IF;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

I add the trigger using:

CREATE TRIGGER insert_date_posted
BEFORE INSERT
ON postcards
FOR EACH ROW
EXECUTE PROCEDURE insert_date_posted();

I also have a backlog of postcards that I want to send when I get around to it. I add them to the table and set date_posted to NULL to keep track of them. When I eventually do get around to sending a postcard from the backlog, I want to update the date_posted column using:

UPDATE postcards
SET date_posted = current_date
WHERE postcard_code = x
AND postcard_recipient = y
AND date_posted IS NULL;

I want the trigger update_date_posted to call the trigger procedure update_date_posted() that will:

  • copy the row to the correct partition
  • delete the row from the postcards_unsent partition
  • trigger the insert_date_posted trigger.

The trigger procedure update_date_posted() is:

CREATE OR REPLACE FUNCTION update_date_posted()
   RETURNS trigger AS
$BODY$
BEGIN
    IF ( OLD.date_posted IS NULL )
    THEN
        -- Copy this row to the right partition by assigning a non NULL date_posted value to it
        INSERT INTO postcards (
            postcard_code
            , postcard_recipient
            , date_posted )
        SELECT
            OLD.postcard_code
            , OLD.postcard_recipient
            , current_date
        FROM postcards
        WHERE ( date_posted IS NULL )
        AND postcard_code = OLD.postcard_code
        AND postcard_recipient = OLD.postcard_recipient
        -- Delete original entry to prevent duplication
        DELETE FROM postcards
        WHERE ( date_posted IS NULL )
        AND postcard_code = OLD.postcard_code
        AND postcard_recipient = OLD.postcard_recipient
    END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

I then add this trigger using:

CREATE TRIGGER update_date_posted
BEFORE UPDATE
ON postcards
FOR EACH ROW
EXECUTE PROCEDURE update_date_posted();

This does not work and the update_date_posted never gets called, the row is not copied to the postcards_2018 partition, it is not deleted from the postcards_unsent partition and the insert_date_posted trigger is not called either.

The error message I see is:

ERROR: new row for relation "postcards_unsent" violates check constraint "postcards_unsent_date_posted_check"
DETAIL:  Failing row contains ( x, y, current_date )

The postcards_unsent_date_posted_check constraint is:

Check constraints:
    "postcards_unsent_date_posted_check" CHECK ( date_posted  IS NULL )

It would appear as if the check constraint is executed first but I am not sure if that is the case and I don't see why this is not working.

  • Somehow I'm missing a link. I cannot see any update or insert for postcards_unsent apart from the insert in insert_date_posted(). The latter cannot cause the constraint violation as it's only executed when NEW.date_posted IS NULL. But I also see no delete for that table. But you want to delete there, so there must be a DELETE FROM postcards_unsent somewhere. Can you give me a pointer of have you forgotten to post some parts (here, not in snail mail ;))? – sticky bit Apr 28 '18 at 1:01
  • @stickybit The INSERT INTO and DELETE statements are in the update_date_posted() trigger procedure and the idea is that the trigger is applied to the parent table and not on the child tables. – dw8547 Apr 30 '18 at 8:50
0

We got some clarity on what the issue here was and I am finally getting around to posting this. It boils down to how triggers behave in combination with partitions (and indirectly, inheritance ?) and the clue was in the docs (5.10.6. Caveats) relating to partitioning:

The schemes shown here assume that the partition key column(s) of a row never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the partition tables [...]

With our trigger, defined on the parent table, we were attempting to update the column date_posted which is the partition key and as per the snippet from the docs above, the CHECK constraint kept failing. When we defined the trigger to the child tables, it functioned as expected.

It did not occur to me sooner to try and define the trigger on the child tables as I was under the impression that FOR EACH ROW BEFORE triggers were not allowed on child tables.

We are using version 9.5 and my senior mentioned that this changes in the subsequent releases. I cloned the PostgreSQL Git repository and checked the commit logs for any clues and found:

This commit states that:

Previously, FOR EACH ROW triggers were not allowed in partitioned tables. Now we allow AFTER triggers on them, and on trigger creation we cascade to create an identical trigger in each partition.

Except we used row level (FOR EACH ROW) BEFORE triggers and the version 10 docs state that:

These types of triggers may only be defined on non-partitioned tables [...]

However, the version 9.5 and version 9.6 docs say:

These types of triggers may only be defined on tables [...]

Hence, we only got some clarity. In the meantime, the trigger works.

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.