Re: [GENERAL] 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 g...@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
Re: [GENERAL] session_replication_role `replica` behavior
-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 g...@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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] session_replication_role 'replica' behavior
Hello all,
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.
A simple example is given bellow:
dynacom=# create table parent (id serial primary key, name text not null);
dynacom=# create table child (id serial primary key, name text not null,pid
int NOT NULL REFERENCES parent(id) ON DELETE CASCADE);
dynacom=# insert into parent (name) values ('test 1');
INSERT 0 1
dynacom=# insert into parent (name) values ('test 2');
INSERT 0 1
dynacom=# insert into child (name,pid) values ('test kid2',2);
INSERT 0 1
dynacom=# begin ;
BEGIN
dynacom=# set session_replication_role TO 'replica';
SET
dynacom=# delete from parent where id=2;
DELETE 1
dynacom=# commit ;
COMMIT
dynacom=# select * from child;
id | name| pid
+---+-
2 | test kid2 | 2
(1 row)
dynacom=# select * from parent;
id | name
+--
(0 rows)
So we are a left, basically, with an inconsistent database.
1. 9.2 documentation (
http://www.postgresql.org/docs/9.2/static/sql-altertable.html) in the
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER section, makes a distinction
between USER (non system-constraint related) and ALL triggers, but does not
state that simply(??) enabled system (non-user) constraint triggers will
not fire in case of session_replication_role = replica. Shouldn't non-user
triggers *not* be affected by session_replication_role ?
2. Is there any way to just find the name of the FK constraint trigger and
convert it to
ENABLE ALWAYS?
For the above test we used postgresql 9.2, currently we are running
postgresql 9.0 in production.
Kind Regards,
manos
[GENERAL] session_replication_role
Version 8.3.1: Is there a distinction between ORIGIN and LOCAL as related to session_replication_role, and if so, what is it? I am unable to understand from the documentation any distinction between the two settings. TIA -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] session_replication_role
[EMAIL PROTECTED] (Terry Lee Tucker) writes: Is there a distinction between ORIGIN and LOCAL as related to session_replication_role, and if so, what is it? I am unable to understand from the documentation any distinction between the two settings. The intent is that a system that is the origin for replication changes (e.g. - a database where you'll be collecting INSERT/UPDATE/DELETE information to replicate elsewhere) would run in the origin role, normally. The distinction from a practical perspective will take place when stored functions that implement replication stuff detect what role the system is in, and may behave differently. -- (format nil [EMAIL PROTECTED] cbbrowne acm.org) http://linuxdatabases.info/info/multiplexor.html Rules of the Evil Overlord #7. When I've captured my adversary and he says, Look, before you kill me, will you at least tell me what this is all about? I'll say, No. and shoot him. No, on second thought I'll shoot him then say No. http://www.eviloverlord.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] session_replication_role
On Tuesday 15 April 2008 14:26, Chris Browne wrote: [EMAIL PROTECTED] (Terry Lee Tucker) writes: Is there a distinction between ORIGIN and LOCAL as related to session_replication_role, and if so, what is it? I am unable to understand from the documentation any distinction between the two settings. The intent is that a system that is the origin for replication changes (e.g. - a database where you'll be collecting INSERT/UPDATE/DELETE information to replicate elsewhere) would run in the origin role, normally. The distinction from a practical perspective will take place when stored functions that implement replication stuff detect what role the system is in, and may behave differently. -- Thanks for the reply. I was unable to detect any change in trigger operation when setting the variable to origin or local. I understand that you are saying that the distinction only exists if my code is written to operate differently based on the setting. Thanks for the input... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
