On Tue, Jan 30, 2018 at 3:36 AM, Laurenz Albe <laurenz.a...@cybertec.at>
wrote:

> Luca Ferrari wrote:
> > now this should be trivial, but I cannot udnerstand what is the
> > purpose of session_replication_role
> > or better, when I should use it in a way different from 'origin'.
>
> It is used to enable or disable triggers.
>
> By default, tables are created with all triggers enabled, which means
> that they fire with the default setting "session_replication_role =
> origin".
>
> You can change "session_replication_role" to "replica" to disable the
> firing
> of triggers (unless they are set ENABLE REPLICA or ENABLE ALWAYS).
> This is done by the logical replication apply worker, but you can also
> use it to bypass triggers, e.g. to speed up operation, if you know what
> you are doing.
>
> What is confusing is that there are three settings for
> "session_replication_role",
> but the two settings "local" and "origin" have the same meaning.
> Maybe that was meant to change at some point, but I see no explanation in
> the original discussion.
>

All of the above does also apply to referential integrity triggers. That
means that under session_replication_role='replica' you replication system
can replicate things out of order with respect to foreign keys. It also
means that if you don't replicate the primary key table you can get the
target database inconsistent.

The setting of 'local' has indeed the same meaning for everything in stock
PostgreSQL. The Slony log and deny-access triggers react to it by
suppressing their actions. An application working under 'local' can modify
the origin without the changes being replicated and modify the replica
without the deny-access trigger aborting the transaction. The Slony engine
uses that mode when running SQL scripts through the EXECUTE DDL feature.
That way you can perform bulk operations like pruning without the
individual row changes being replicated.

The setting of 'replica' is very important if you have triggers that for
example do auditing or stuff like stamping created and last update
timestamps or session users. You certainly don't want to overwrite the real
last update timestamp or session user with the replication engine user and
time.


Regards, Jan



>
> Yours,
> Laurenz Albe
>
>


-- 
Jan Wieck
Senior Postgres Architect
http://pgblog.wi3ck.info

Reply via email to