Re: [CAUTION: SUSPECT SENDER] 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

That's a great tip. Will definitely make use of it. Thanks.

Thadeus.


On Wed, Aug 27, 2025 at 3:08 PM Hayato Kuroda (Fujitsu) <
kuroda.hayato@fujitsu.com> wrote:

> 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://ap-south-1.protection.sophos.com?d=postgresql.org&u=aHR0cHM6Ly93d3cucG9zdGdyZXNxbC5vcmcvZG9jcy9jdXJyZW50L3NxbC1hbHRlcnRhYmxlLmh0bWwjU1FMLUFMVEVSVEFCTEUtREVTQy1ESVNBQkxFLUVOQUJMRS1UUklHR0VS&i=NjM3ZjE2NjA3YjE0ZTAwZTUzOWZhNWU4&t=R3BFaFNMNzVxRFE3by9BajdZUndkRU9PTnpBNWpJdGU2blZrbTN1ejlMND0=&h=c39403d7aeea456b92aeb6203afb9128&s=AVNPUEhUT0NFTkNSWVBUSVb7Ui6ZU7wjaq6zUGJyKluO_pTslCZ6E_4N2mTMoaLlhw
>
> Best regards,
> Hayato Kuroda
> FUJITSU LIMITED
>
>