I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table.

CREATE TABLE twitter_interactions(...);
CREATE OR REPLACE FUNCTION insert_twitter_interactions ...;
CREATE TRIGGER insert_twitter_interactions_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

CREATE OR REPLACE FUNCTION maintain_data_pointers ...;
CREATE TRIGGER maintain_data_pointers_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

I haven't fully verified, but I suspect the partitioning logic runs before the maintain trigger, and since the row doesn't end up in the parent table, then the 2nd trigger never fires.

What happens if I want to run an AFTER INSERT OR UPDATE as well? Since the row doesn't make it into the original table, then I'm at a loss to implement the after logic.


I couldn't say it better than the manual does here:

If any BEFORE or INSTEAD OF trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired (for that row).

Neither the (alphabetically) later triggers on the same event nor any AFTER triggers will fire, if the BEFORE trigger cancels.

| improve this answer | |
  • Good old RTFM... But reading the statement, if the insert trigger is last, then other triggers can fire and complete. I'll investigate this further. Thanks! – François Beausoleil Feb 10 '12 at 3:30
  • @FrançoisBeausoleil: That's right. You can squeeze in other triggers on the same event before you cancel the INSERT with RETURN NULL. You could also put more program code into the same trigger function. – Erwin Brandstetter Feb 10 '12 at 17:17

PostgreSQL executes the triggers in alphabetical order by name. So make sure to use names that get them in the order you want. From the docs

SQL specifies that multiple triggers should be fired in time-of-creation order. PostgreSQL uses name order, which was judged to be more convenient.

BTW your SQL creates the same trigger twice.

| improve this answer | |
  • Simple copy paste error. Wrote the SQL above for illustrative purposes. Thanks for the answer! – François Beausoleil Feb 9 '12 at 21:26

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.