BUG #14310: Triggers do not fire

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

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

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/
docs/9.4/static/catalog-pg-trigger.html

Cheers,

--
Willy-Bas Loos