Thread: BUG #14310: Triggers do not fire
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMxMApMb2dnZWQgYnk6ICAg ICAgICAgIEl1cmlpIFBvcG92CkVtYWlsIGFkZHJlc3M6ICAgICAgaXVyaWku aS5wb3BvdkBnbWFpbC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjQuOQpP cGVyYXRpbmcgc3lzdGVtOiAgIExpbnV4IDggKGplc3NpZSkKRGVzY3JpcHRp b246ICAgICAgICAKCldlIGhhdmUgYSByZXBlYXRpbmcgcHJvYmxlbSB3aXRo IHRyaWdnZXJzIGFuZCBoYW5kbGVzIGluIGZvcmVpZ24ga2V5cyBzaW5jZQp3 ZSBoYXZlIHVwZGF0ZWQgUG9zdGdyZVNRTC4NCg0KVGhlcmUgYXJlIHR3byBw cm9qZWN0cyB3aXRoIFBvc3RncmVTUUwgOS4yLjE3IGFuZCA5LjQuOSByZXNw ZWN0aXZlbHkuIFdlIGRvCm5vdCBoYXZlIHRoZSBwcm9ibGVtIHdpdGggOS4y LjE2IGFuZCA5LjQuNCB2ZXJzaW9ucy4NCg0KQnkgYWNjaWRlbnQgYSBCRUZP UkUgSU5TRVJUIEZPUiBFQUNIIFJPVyB0cmlnZ2VycyBkbyBub3QgZmlyZSBh bmQgaGFuZGxlcyBpbgpmb3JlaWduIGtleXMgKE9OIERFTEVURSBTRVQgTlVM TCBhbmQgT04gREVMRVRFIENBU0NBREUpIGRvIG5vdCBmaXJlLg0KDQpJZiB3 ZSBkbyBhY3Rpb25zIGJ5IGhhbmRzIChJTlNFUlQgb3IgREVMRVRFKSBldmVy eXRoaW5nIHdvcmtzIGJ1dCB3aXRoCnBlb3BsZSBvbmxpbmUgd2UgaGF2ZSB0 aGlzIHByb2JsZW0gYnkgYWNjaWRlbnQuCgo=
iurii.i.popov@gmail.com writes: > We have a repeating problem with triggers and handles in foreign keys since > we have updated PostgreSQL. > There are two projects with PostgreSQL 9.2.17 and 9.4.9 respectively. We do > not have the problem with 9.2.16 and 9.4.4 versions. > By accident a BEFORE INSERT FOR EACH ROW triggers do not fire and handles in > foreign keys (ON DELETE SET NULL and ON DELETE CASCADE) do not fire. > If we do actions by hands (INSERT or DELETE) everything works but with > people online we have this problem by accident. This is not enough information for anyone to reproduce your problem, or even really to understand what you're talking about. For hints on how to submit a useful bug report, please see https://www.postgresql.org/docs/9.4/static/bug-reporting.html https://wiki.postgresql.org/wiki/Guide_to_reporting_problems regards, tom lane
The problem was with pgbouncer: https://pgbouncer.github.io/changelog.html WARNING: Since version 1.7, server_reset_query is not executed when database is in transaction-pooling mode. Seems this was not highlighted enough in 1.7 announcement. If your apps depend on that happening, use server_reset_query_always to restore previous behaviour. We use pool_mode = transaction Adding server_reset_query_always = 1 solves the problem. But I have not understand a reason yet. 2016-09-05 18:05 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>: > iurii.i.popov@gmail.com writes: > > We have a repeating problem with triggers and handles in foreign keys > since > > we have updated PostgreSQL. > > > There are two projects with PostgreSQL 9.2.17 and 9.4.9 respectively. We > do > > not have the problem with 9.2.16 and 9.4.4 versions. > > > By accident a BEFORE INSERT FOR EACH ROW triggers do not fire and > handles in > > foreign keys (ON DELETE SET NULL and ON DELETE CASCADE) do not fire. > > > If we do actions by hands (INSERT or DELETE) everything works but with > > people online we have this problem by accident. > > This is not enough information for anyone to reproduce your problem, or > even really to understand what you're talking about. > > For hints on how to submit a useful bug report, please see > > https://www.postgresql.org/docs/9.4/static/bug-reporting.html > > https://wiki.postgresql.org/wiki/Guide_to_reporting_problems > > regards, tom lane > -- Iurii Popov
We also use Londiste 3 from Skytools. It seems londiste disables triggers via set session_replication_role = 'replica' and associated with the connection transactions do not fire triggers. 2016-09-26 12:38 GMT+03:00 Iurii Popov <iurii.i.popov@gmail.com>: > The problem was with pgbouncer: https://pgbouncer.github.io/changelog.html > WARNING: Since version 1.7, server_reset_query is not executed when > database is in transaction-pooling mode. Seems this was not highlighted > enough in 1.7 announcement. If your apps depend on that happening, use > server_reset_query_always to restore previous behaviour. > > We use pool_mode = transaction > > Adding server_reset_query_always = 1 solves the problem. But I have not > understand a reason yet. > > 2016-09-05 18:05 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>: > >> iurii.i.popov@gmail.com writes: >> > We have a repeating problem with triggers and handles in foreign keys >> since >> > we have updated PostgreSQL. >> >> > There are two projects with PostgreSQL 9.2.17 and 9.4.9 respectively. >> We do >> > not have the problem with 9.2.16 and 9.4.4 versions. >> >> > By accident a BEFORE INSERT FOR EACH ROW triggers do not fire and >> handles in >> > foreign keys (ON DELETE SET NULL and ON DELETE CASCADE) do not fire. >> >> > If we do actions by hands (INSERT or DELETE) everything works but with >> > people online we have this problem by accident. >> >> This is not enough information for anyone to reproduce your problem, or >> even really to understand what you're talking about. >> >> For hints on how to submit a useful bug report, please see >> >> https://www.postgresql.org/docs/9.4/static/bug-reporting.html >> >> https://wiki.postgresql.org/wiki/Guide_to_reporting_problems >> >> regards, tom lane >> > > > > -- > Iurii Popov > -- Iurii Popov
On Mon, Sep 26, 2016 at 4:29 PM, Iurii Popov <iurii.i.popov@gmail.com> wrote:
We also use Londiste 3 from Skytools. It seems londiste disables triggers via set session_replication_role = 'replica' and associated with the connection transactions do not fire triggers.--Iurii Popov
Hi, this comes a bit late, but it might be handy for future reference.
You can set tgenabled='A' for the trigger with an update query and it will make the trigger fire, regardless of the session_replication_role.
Specifics can be found in the docs: http://www.postgresql.org/
Cheers,You can set tgenabled='A' for the trigger with an update query and it will make the trigger fire, regardless of the session_replication_role.
Specifics can be found in the docs: http://www.postgresql.org/
--
Willy-Bas Loos