session_replication_role meaning?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

session_replication_role meaning?

Luca Ferrari-2
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>
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?

Thanks,
Luca

Reply | Threaded
Open this post in threaded view
|

Re: session_replication_role meaning?

Achilleas Mantzios
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>
> 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


Reply | Threaded
Open this post in threaded view
|

Re: session_replication_role meaning?

Laurenz Albe
In reply to this post by Luca Ferrari-2
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

Reply | Threaded
Open this post in threaded view
|

Re: session_replication_role meaning?

Jan Wieck-3


On Tue, Jan 30, 2018 at 3:36 AM, Laurenz Albe <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: session_replication_role meaning?

Peter Eisentraut-6
In reply to this post by Luca Ferrari-2
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>
> 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

Reply | Threaded
Open this post in threaded view
|

Re: session_replication_role meaning?

Luca Ferrari-2
On Wed, Jan 31, 2018 at 5:19 AM, Peter Eisentraut
<[hidden email]> 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

Previous Thread Next Thread