session_replication_role meaning?

On 30/01/2018 09:48, Luca Ferrari wrote:

Hi all,
now this should be trivial, but I cannot udnerstand what is the
purpose of session_replication_role
<https://www.postgresql.org/docs/10/static/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE&gt;
or better, when I should use it in a way different from 'origin'.
I've a logical replication setup and both master and client
connections default to 'origin', so it has to be specified manually.
What is its meaning?

This has an effect on dictating how triggers are fired.
For instance if you have built your proprietary replication mechanism based on triggers (e.g. Slony, DBmirror) then you might want at certain occasions (e.g. reading xactions from a master) to avoid
firing triggers in order e.g. to prevent bounce back messages to the originator. If your replication trigger is a simply enabled trigger, then by setting session_replication_role to replica will
prevent the trigger from firing. This could be useful in the code where you execute SQL originating from the master, in order not to send those back and cause an endless loop.

Thanks,
Luca

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

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.

Yours,
Laurenz Albe

On Tue, Jan 30, 2018 at 3:36 AM, Laurenz Albe <laurenz.albe@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

On 1/30/18 02:48, Luca Ferrari wrote:

Hi all,
now this should be trivial, but I cannot udnerstand what is the
purpose of session_replication_role
<https://www.postgresql.org/docs/10/static/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE&gt;
or better, when I should use it in a way different from 'origin'.

The documentation was recently updated in the master branch, so maybe
you will find this explanation a bit more detailed:
https://www.postgresql.org/docs/devel/static/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE

I've a logical replication setup and both master and client
connections default to 'origin', so it has to be specified manually.

The global setting for an instance is not affected by whether you are
replicating. The replication system is supposed to set the parameter
when it is applying changes, e.g.,
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/replication/logical/worker.c;h=eedc3a8816bc2f157e62a664bcc57b5f87530be9;hb=958fe549884928cd3bdf009993e9a05df5fd6cee#l1521

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Jan 31, 2018 at 5:19 AM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:

The documentation was recently updated in the master branch, so maybe
you will find this explanation a bit more detailed:
https://www.postgresql.org/docs/devel/static/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE

Looks a lot more clear to me.

The global setting for an instance is not affected by whether you are
replicating. The replication system is supposed to set the parameter
when it is applying changes, e.g.,
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/replication/logical/worker.c;h=eedc3a8816bc2f157e62a664bcc57b5f87530be9;hb=958fe549884928cd3bdf009993e9a05df5fd6cee#l1521

Yes, it is clearer from the documentation and the code what the purpose is.

Thanks,
Luca