0

I have a test scenario of two postgresql databases connected via logical replication. DB "A" is publishing inserts and updates for table "a1" and DB "B" is subscribed and receiving everything without problems.

I added a trigger before update for table "a1" on DB "B". It is working for update statements executed locally on "B" but it is not triggering when update comes via logical replication. Here is the function and trigger i'm using to test it.

CREATE OR REPLACE FUNCTION update_tg_func()
RETURNS TRIGGER AS $$
BEGIN
    -- Extract the id value from the update query
    -- Assuming that the 'id' column is being updated
    RAISE WARNING 'Got OP %, id %', TG_OP, NEW.id;
    -- For other operations, just return the NEW row
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER update_tg
BEFORE UPDATE ON a1
FOR EACH STATEMENT
EXECUTE FUNCTION update_tg_func();

ALTER TABLE a1 ENABLE ALWAYS TRIGGER update_tg ;

Postgres version is 12.1, not tested in newer versions.


EDIT

Thanks to @Melkij I noted that my trigger is not firing because my table is empty. And my table is empty because I have another trigger BEFORE INSERT that "redirects" the inserted rows to other tables just like partitioning would do, but without using real partitioning.

I thought that using FOR EACH STATEMENT would fire the trigger even if no rows are affected. Documentation says:

a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies https://www.postgresql.org/docs/12/sql-createtrigger.html

I removed the trigger that "redirects" the inserted rows so my table on DB B has some rows and now the trigger do fires.

I need the trigger to trigger even if the table is empty. Any hints?

8
  • A ist master and B is child, so the update trigger must be defined in A not B
    – nbk
    Mar 4 at 13:37
  • No, I need the trigger to be in "B". Mar 4 at 13:38
  • so you want no updates in B, maybe you could clarify the concept, you trying
    – nbk
    Mar 4 at 13:40
  • I apologize for any confusion. Let me try to clarify my scenario. In my setup, Database B is receiving inserts from Database A via logical replication. For each inserted row, a function is triggered in Database B, which essentially redirects the row to the appropriate table based on a key. Think of it as manual partitioning. This setup functions smoothly for inserts. However, I'm encountering an issue with updates not being firing the trigger. Mar 4 at 13:56
  • did you mean the built-in publication and subscription logical replication or using an pglogical extension as stated in the tags?
    – Melkij
    Mar 4 at 13:56

1 Answer 1

0

It may be tricky to find, but it's documented here

The logical replication apply process currently only fires row triggers, not statement triggers.

Although, looking at the attempt to use NEW.id in the trigger function, I think that you wanted to create a FOR EACH ROW trigger instead of FOR EACH STATEMENT. Try with row trigger:

CREATE TRIGGER update_tg
BEFORE UPDATE ON a1
FOR EACH ROW
EXECUTE FUNCTION update_tg_func();

ALTER TABLE a1 ENABLE ALWAYS TRIGGER update_tg ;

PS: it's a really bad idea to ignore minor updates for so many years. Consider installing 12.18

3
  • The reason I'm using FOR EACH STATEMENT instead of FOR EACH ROW is because the destination table on Database B is initially empty. The inserted rows from Database A, which are replicated to Database B, are essentially redirected to other tables, functioning similarly to a partitioning scheme (albeit without using real partitions). If I were to use FOR EACH ROW, the trigger would evaluate affected rows based on the received statement, potentially resulting in zero changes on Database B, thus rendering the trigger ineffective. Mar 4 at 14:21
  • I edited my question to add more information and tests. Hope it clarifies my scneario. Mar 4 at 14:38
  • I marked it as correct answer, even if it doesn't fix my problem. But it explained half of it, so, take it into account if you're coming from the future. Mar 12 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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