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://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-DISABLE-ENABLE-TRIGGER

Best regards,
Hayato Kuroda
FUJITSU LIMITED