Re: Trigger not firing - Mailing list pgsql-general

On 5/31/20 6:19 AM, Hans wrote:
> Hi,
> 
> 
> I've had a weird problem in a production system. The customer had 
> installed a new server with our software on it. The software installs a 
> Postgres database schema that includes a number of triggers. The 
> triggers perform inserts into an additional table.

How is the install done?

More questions/comments below.

> 
> In this installation, from what I can tell, some triggers somehow got 
> into a disabled state:
> 
> - they were confirmed to be present (checked using pgAdmin 4).
> 
> - In the trigger property window of pgAdmin 4, the triggers were listed 
> as enabled.

When in doubt use psql to look at the table. So:

\d table_name.

That will show you the state of the triggers.

> 
> - However, attempts to trigger them (by performing an appropriate insert 
> or update) didn't visibly result in the trigger running (no entries in 
> the additional table were created). The insert/update itself worked fine.
> 
> I asked the customer to look in pg_trigger. The triggers were listed 
> with tgenabled set to 'O' (but I'm not sure if that is the right thing 
> to look at).

That is related to replication:

https://www.postgresql.org/docs/12/catalog-pg-trigger.html

"Controls in which session_replication_role modes the trigger fires. O = 
trigger fires in “origin” and “local” modes, D = trigger is disabled, R 
= trigger fires in “replica” mode, A = trigger fires always.
"

https://www.postgresql.org/docs/12/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE

"session_replication_role (enum)

     Controls firing of replication-related triggers and rules for the 
current session. Setting this variable requires superuser privilege and 
results in discarding any previously cached query plans. Possible values 
are origin (the default), replica and local.

...

"

> 
> Our software contains no code for disabling triggers. It creates them 
> once, during database initialisation (i.e. before any data is put in), 
> and then leaves them alone. I have no reason to believe the customer 
> messed with the database either.

Exactly how is that done?

> 
> How we resolved this: after using "triggers -> enable all" in pgAdmin on 
> that table, the triggers were now found to be working as expected. So 
> the trigger code is correct and works, but it (somehow) wasn't enabled. 
> Moreover, pgAdmin apparently reported this state incorrectly. My 
> question is: what could have happened?
> 
> Postgres 10.3, 64-bits, Ubuntu (I think 18.04).
> 
> - Are triggers always automatically created in the 'enabled' state, or 
> are there conditions that could cause them to start as 'disabled', or in 
> some other way inactive?

In the normal dump/restore cycle they set to the state they where in teh 
source database.

> 
> - What could have caused pgAdmin to report the trigger as 'enabled' even 
> though, from what I can tell, it really wasn't?

That is question for the pgAdmin folks:

https://www.pgadmin.org/support/list/

> 
> - Is there a log file that could shed more light on this situation?

The Postgres log file. Should be in:

/var/log/postgresql/

> 
> The customer is willing to try another installation, to see if the 
> problem can be replicated. I can ask for additional logging to be 
> enabled if that helps in understanding this problem. What would be 
> helpful in troubleshooting this?
> 
> 
> Thanks in advance for any insight you may have,
> 
> Hans
> 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com