Horton

RE: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: BUG #19029: Replication Slot size keeps increasing while logical subscription works fine

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>

View thread
From: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
To: 'Thadeus Anand' <thadeus@rmkv.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>, vignesh C <vignesh21@gmail.com>, Nantha kumar.T. <nanthad@gmail.com>
Date: 2025-08-27T09:38:35Z
Lists: pgsql-bugs

Attachments

Dear Thadeus,

> On a completely different note, is there any possibility for you developers at
> PostgreSQL to consider making the tables at the subscriber side read only, so that
> nobody makes changes to them accidentally? I am asking this because sometimes when
> it happens, the logical replication fails, and making the subscriber tables read
> only can reduce a lot of headache. Please consider.

How about using trigger functions? By default, data changes done by the apply
worker do not fire trigger [1].
So, you can prohibit modifications if you define a trigger function which
prohibits DMLs on the subscriber side.

Attached script set up what I said. After running the script, you can insert tuples on the pub:
```
publisher=# INSERT INTO foo VALUES (generate_series(1, 10));
INSERT 0 10
```

And you can see it on the sub. However, you cannot modify tuples via UPDATE:
```
subscriber=# SELECT count(*) FROM foo ;
 count 
-------
    10
(1 row)

subscriber=# UPDATE foo SET id = 11 WHERE id = 1;
ERROR:  changing data in foo is prohibit
CONTEXT:  PL/pgSQL function ban_foo() line 3 at RAISE
```

[1]: https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-DISABLE-ENABLE-TRIGGER

Best regards,
Hayato Kuroda
FUJITSU LIMITED