Thread: session_replication_role meaning?
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
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 mightwant 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 simplyenabled 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 ordernot 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
Senior Postgres Architect
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
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