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 - Mailing list pgsql-bugs

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