<div dir="ltr"><div><div><div><div><div>Hello all,<br><br></div>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.<br>
<br></div>A simple example is given bellow:<br><br>dynacom=# create table parent (id serial primary key, name text not null);<br><br>dynacom=#
create table child (id serial primary key, name text not null,pid int
NOT NULL REFERENCES parent(id) ON DELETE CASCADE);<br>
<br>dynacom=# insert into parent (name) values ('test 1');<br>INSERT 0 1<br><br>dynacom=# insert into parent (name) values ('test 2');<br>INSERT 0 1<br><br>dynacom=# insert into child (name,pid) values ('test kid2',2);<br>
INSERT 0 1<br>dynacom=# begin ;<br>BEGIN<br>dynacom=# set session_replication_role TO 'replica';<br>
SET<br></div><div>dynacom=# delete from parent where id=2;<br>DELETE 1<br>dynacom=# commit ;<br>COMMIT<br><br>dynacom=# select * from child;<br> id | name | pid <br>----+-----------+-----<br> 2 | test kid2 | 2<br>
(1 row)<br>
<br>dynacom=# select * from parent;<br> id | name <br>----+------<br>(0 rows)<br><br></div></div></div><div>So we are a left, basically, with an inconsistent database.<br></div><div><br></div>1. 9.2 documentation (<a href="http://www.postgresql.org/docs/9.2/static/sql-altertable.html" target="_blank">http://www.postgresql.org/docs/9.2/static/sql-altertable.html</a>) in the<tt> </tt>"<tt>DISABLE</tt>/<tt>ENABLE [ REPLICA | ALWAYS ] TRIGGER</tt>"
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 ?<br>
<br>2. Is there any way to just find the name of the FK constraint trigger and convert it to <br>ENABLE ALWAYS?<br><br>For the above test we used postgresql 9.2, currently we are running postgresql 9.0 in production.<br>
<br>Kind Regards,<br>manos</div>