Thread: BUG #14310: Triggers do not fire

BUG #14310: Triggers do not fire

From
iurii.i.popov@gmail.com
Date:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMxMApMb2dnZWQgYnk6ICAg
ICAgICAgIEl1cmlpIFBvcG92CkVtYWlsIGFkZHJlc3M6ICAgICAgaXVyaWku
aS5wb3BvdkBnbWFpbC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjQuOQpP
cGVyYXRpbmcgc3lzdGVtOiAgIExpbnV4IDggKGplc3NpZSkKRGVzY3JpcHRp
b246ICAgICAgICAKCldlIGhhdmUgYSByZXBlYXRpbmcgcHJvYmxlbSB3aXRo
IHRyaWdnZXJzIGFuZCBoYW5kbGVzIGluIGZvcmVpZ24ga2V5cyBzaW5jZQp3
ZSBoYXZlIHVwZGF0ZWQgUG9zdGdyZVNRTC4NCg0KVGhlcmUgYXJlIHR3byBw
cm9qZWN0cyB3aXRoIFBvc3RncmVTUUwgOS4yLjE3IGFuZCA5LjQuOSByZXNw
ZWN0aXZlbHkuIFdlIGRvCm5vdCBoYXZlIHRoZSBwcm9ibGVtIHdpdGggOS4y
LjE2IGFuZCA5LjQuNCB2ZXJzaW9ucy4NCg0KQnkgYWNjaWRlbnQgYSBCRUZP
UkUgSU5TRVJUIEZPUiBFQUNIIFJPVyB0cmlnZ2VycyBkbyBub3QgZmlyZSBh
bmQgaGFuZGxlcyBpbgpmb3JlaWduIGtleXMgKE9OIERFTEVURSBTRVQgTlVM
TCBhbmQgT04gREVMRVRFIENBU0NBREUpIGRvIG5vdCBmaXJlLg0KDQpJZiB3
ZSBkbyBhY3Rpb25zIGJ5IGhhbmRzIChJTlNFUlQgb3IgREVMRVRFKSBldmVy
eXRoaW5nIHdvcmtzIGJ1dCB3aXRoCnBlb3BsZSBvbmxpbmUgd2UgaGF2ZSB0
aGlzIHByb2JsZW0gYnkgYWNjaWRlbnQuCgo=

Re: BUG #14310: Triggers do not fire

From
Tom Lane
Date:
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

Re: BUG #14310: Triggers do not fire

From
Iurii Popov
Date:
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

Re: BUG #14310: Triggers do not fire

From
Iurii Popov
Date:
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

Re: [BUGS] BUG #14310: Triggers do not fire

From
Willy-Bas Loos
Date:
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/docs/9.4/static/catalog-pg-trigger.html

Cheers,

--
Willy-Bas Loos