Search Postgresql Archives

Re: session_replication_role `replica` behavior

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



-----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@xxxxxxxxxxxx
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-----




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux