Re: session_replication_role `replica` behavior

Point taken, thanx,

however from the docs, it is far from explicit that setting session_replication_role to 'replica'

can disable FK constraints (RI) and finally result in an incosistent database.

It might be that RI in postgres is implemented via triggers, but to the user,

that is just an implementation detail, and in any case this is not reflected in the docs.

Furthermore from the docs, same page :

"

Simply enabled triggers will fire when the replication role is "origin" (the default) or "local".

Triggers configured as ENABLE REPLICA will only fire if the session is in "replica" mode, and

triggers configured as ENABLE ALWAYS will fire regardless of the current replication mode.

"

In the second sentence above the word "only" is used, and the meaning is precisely delivered.

However this same word is missing from the first sentence, and might confuse quite a lot of users.

IMHO this section needs some modifications in order to express the whole behavior correctly.

On Ðåì 25 Áðñ 2013 15:49:55 Greg Sabino Mullane wrote:

>

> -----BEGIN PGP SIGNED MESSAGE-----

> Hash: RIPEMD160

>

>

> manos tsahakis wrote:

> > In our application we are enabling session_replication_role TO 'replica' in

> > certain situations so that triggers will not fire in a table during DML

> > operations. However, we observed that when setting session_replication_role

> > TO 'replica' referential integrity constraints will not fire on a table

> > either.

> ...

> > Shouldn't non-user triggers *not* be affected by session_replication_role ?

>

> No. The design of session_replication_role was to enable quick disabling

> of *all* triggers and rules, including system ones. When you enter that mode,

> it is assumed that you know what you are doing enough to not create an

> inconsistency. With Slony and Bucardo, for example, all tables affected

> by the triggers (e.g. a cascaded delete from a FK) are changed together.

>

> > 2. Is there any way to just find the name of the FK constraint trigger and

> > convert it to ENABLE ALWAYS?

>

> I think you are approaching this in the wrong way. If you want the constraint

> triggers to fire, but not other user triggers, your best bet is to do:

>

> ALTER TABLE foo DISABLE TRIGGER USER;

>

> This has a heavy table locking cost, but does exactly what you want: disables

> all non-system/FK triggers.

>

> Your next best bet is probably to emulate the effects of the FK trigger yourself,

> e.g. deleting from the child table while in 'replica' mode.

>

> A further option may be to give your user functions some brains, such that

> they will not execute when session_replication_role is set to 'local', for example.

>

> While I do think session_replication_role needs some more granularity, it's

> also a little hard to say more without knowing your exact requirements.

>

> - --

> Greg Sabino Mullane greg@turnstep.com

> End Point Corporation http://www.endpoint.com/

> PGP Key: 0x14964AC8 201304251145

> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

>

> -----BEGIN PGP SIGNATURE-----

>

> iEYEAREDAAYFAlF5UHAACgkQvJuQZxSWSsjm+ACeOT2v7EF90tFr7K892UxIAqnl

> WpwAoKPkIMC7HTTtvOMj/XbtOVGXe0Fl

> =2bjH

> -----END PGP SIGNATURE-----

>

>

>

>

>

-

Achilleas Mantzios

IT DEV

IT DEPT

Dynacom Tankers Mgmt