0

Overview

I have the following setup using Azure PostgreSQL flexible server v17:

  • a main database containing a certain table included in the table list for a logical replication publication
  • a replica database containing a subscription to the main

In the replica, one of the replicated tables contains an after INSERT trigger:

  • fires when I directly insert in the replica database table (RAISE NOTICE displays the text and I got the record in the target table)
  • does not fire when the data is inserted through the replication process

Details

The trigger and its function look like this (as scripted by pgAdmin):

CREATE OR REPLACE TRIGGER "MessageInsertTrigger"
    AFTER INSERT
    ON public."Message"
    REFERENCING NEW TABLE AS "TmpChangedMessage"
    FOR EACH STATEMENT
    EXECUTE FUNCTION public."EnqueueMessageIdsForEtl"();

ALTER TABLE public."Message"
    ENABLE ALWAYS TRIGGER "MessageInsertTrigger";

CREATE OR REPLACE FUNCTION public."EnqueueMessageIdsForEtl"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
    -- This is the new line --
    RAISE NOTICE 'MessageInsertTrigger FIRED on replica. Processing...';

    INSERT INTO jobs."ChangedMessage" ("MessageId", "ChangeTimestamp")
    SELECT "MessageId", now() AT TIME ZONE 'UTC' FROM "TmpChangedMessage";
    -- ON CONFLICT ("MessageId") DO NOTHING;

    RETURN NULL;
END;
$BODY$;

The following query confirms the trigger's always option:

SELECT
    tgname AS trigger_name,
    tgenabled AS enabled_status_code
FROM
    pg_trigger
WHERE
    tgrelid = 'public."Message"'::regclass
    AND tgname = 'MessageInsertTrigger';
"trigger_name" "enabled_status_code"
"MessageInsertTrigger" "A"

The subscription information (subconninfo) looks like this: dbname=main-db host=the-main-db.database.azure.com port=5432 user=replication-user password=***

I have checked the rolconfig for the username that I am using (only one for setting up the replication, the db objects and querying in pgAdmin):

SELECT rolname, rolconfig
FROM pg_roles
WHERE rolname = 'TheUser';
rolname rolconfig
TheUser [null]

I have also checked the versions of both the main and the replica servers:

  • main: PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
  • replica: PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.2.0, 64-bit

I have enabled the ultimate debug level (DEBUG5) and the logs show the replication commands, but there is nothing related to the trigger being fired or not:

2025-10-31 07:54:59 UTC-690317fe.56c-DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0","context": "processing remote data for replication origin \"pg_27860\" during message type \"INSERT\" in transaction 288742265, finished at 53C/E6006640
2025-10-31 07:54:59 UTC-690317fe.56c-DEBUG:  Security context:0","context": "processing remote data for replication origin \"pg_27860\" during message type \"INSERT\" for replication target relation \"public.Message\" column \"Col1\" in transaction 288742265, finished at 53C/E6006640
2025-10-31 07:54:59 UTC-690317fe.56c-DEBUG:  Security context:0","context": "processing remote data for replication origin \"pg_27860\" during message type \"INSERT\" for replication target relation \"public.Message\" column \"Col2\" in transaction 288742265, finished at 53C/E6006640
2025-10-31 07:54:59 UTC-690317fe.56c-DEBUG:  Security context:0","context": "processing remote data for replication origin \"pg_27860\" during message type \"INSERT\" for replication target relation \"public.Message\" column \"Col3\" in transaction 288742265, finished at 53C/E6006640
....

2025-10-31 07:54:59 UTC-690317fe.56c-DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 16818219/1/1","context": "processing remote data for replication origin \"pg_27860\" during message type \"COMMIT\" in transaction 288742265, finished at 53C/E6006640
2025-10-31 07:54:59 UTC-690317fe.56c-DEBUG:  sending feedback (force 0) to recv 53C/E6006670, write 53C/E6006670, flush 53C/E6000948
2025-10-31 07:54:59 UTC-690317fe.55d-DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-10-31 07:54:59 UTC-690317fe.55d-DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-10-31 07:54:59 UTC-6903178d.7-DEBUG:  postmaster received pmsignal signal
2025-10-31 07:54:59 UTC-69046b53.f1da-DEBUG:  InitPostgres
2025-10-31 07:54:59 UTC-69046b53.f1da-DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-10-31 07:54:59 UTC-69046b53.f1da-DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0

Any idea what I might try to understand what is happening?

P.S. This used to work several days ago, something changes which has broken the logical replication + trigger firing, but I cannot identity that.

Improve this question
2
  • 1
    You could try to use RAISE EXCEPTION in the trigger function. Then you can certainly tell if the function is called. Commented Oct 31 at 11:31
  • Yes. Anyway, if understand correctly, the replication operation (i.e. insert in my case) executes in the same transaction as the trigger, so I guess that any trigger failure would lead to also replication failure. However, the data is correctly replicated in my case. Commented Oct 31 at 13:07

1 Answer 1

Reset to default
2

CREATE OR REPLACE TRIGGER "MessageInsertTrigger" .... FOR EACH STATEMENT

Statement triggers are not fired from logical replication. Logical replication limitations are not always easy to find in the documentation, this limitation is documented here:

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

You need the ALWAYS trigger, that's true; without it, the trigger won't be fired. But it also needs to be for each row.

Improve this answer
2
  • 1
    It worked. However, I was tricked by the following behavior: - when setting up the replication and ran to catch up, the statement-level trigger worked fine - after the replication gets into the streaming mode and I performed some inserts on the main, the data was replicated, but the trigger did not fire Somehow, the replication behaved differently between these modes. Commented Oct 31 at 16:31
  • 1
    Found the explanation in the docs for what I see: The initial table synchronization, however, is implemented like a COPY command and thus fires both row and statement triggers for INSERT. Commented Nov 1 at 6:39

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.