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.
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.
- 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).
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.
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?
- What could have caused pgAdmin to report the trigger as 'enabled' even
though, from what I can tell, it really wasn't?
- Is there a log file that could shed more light on this situation?
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