Thread: Trigger not firing

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

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

>> 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?

Our instructions tell them to apt-get it from the default repository. I 
can ask on tuesday for more information.

>> 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.

Ok, thanks.

>> 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?

We give them a C++ program that creates the tables, and then executes:

CREATE OR REPLACE FUNCTION generic.update_usergrouptest_from_test() 
RETURNS trigger AS $$
DECLARE
  x INTEGER;
BEGIN
  IF NEW.usergroup_ids <> OLD.usergroup_ids THEN
   DELETE FROM generic.usergroup_test WHERE test_id = NEW.id;

   FOREACH x IN ARRAY NEW.usergroup_ids LOOP
    INSERT INTO generic.usergroup_test (test_id, usergroup_id) VALUES 
(NEW.id, x);
   END LOOP;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

and then

CREATE TRIGGER update_usergrouptest_from_test
AFTER UPDATE ON generic.test
FOR EACH ROW EXECUTE PROCEDURE generic.update_usergrouptest_from_test();

(we are simplifying the use of N-M relations by putting multiple foreign 
keys into an array field. The N-M table takes care of foreign key 
constraints, but is never touched by the software. The software only 
ever looks at the array field. The _SQL_ may be simple enough for N-M 
tables, but the _C++_ is really much happier if it can treat these 
foreign keys as an array, instead of an extra table. Having real arrays 
of foreign keys would be nice, but this works too).


Hans

> you trigger can be much faster if you replace FOREACH cycle by unnest
> 
> INSERT INTO generic.usergroup_test(test_id, usergroup_id) VALUES(NEW.id, 
> UNNEST(NEW.usergroup_ids));

Thanks! Appreciated :-)


Hans

On 6/1/20 3:09 AM, Hans wrote:
>>> 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?
> 
> Our instructions tell them to apt-get it from the default repository. I 
> can ask on tuesday for more information.

But there is more to it then that as you show below. So what is the 
outline for a customer to get from clean server to one with your 
software fully installed?

The Postgres logs from the period the below was run might be 
informative. I'm guessing that is going to have to wait until Tuesday.

> 
>>> 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?
> 
> We give them a C++ program that creates the tables, and then executes:
> 
> CREATE OR REPLACE FUNCTION generic.update_usergrouptest_from_test() 
> RETURNS trigger AS $$
> DECLARE
>   x INTEGER;
> BEGIN
>   IF NEW.usergroup_ids <> OLD.usergroup_ids THEN
>    DELETE FROM generic.usergroup_test WHERE test_id = NEW.id;
> 
>    FOREACH x IN ARRAY NEW.usergroup_ids LOOP
>     INSERT INTO generic.usergroup_test (test_id, usergroup_id) VALUES 
> (NEW.id, x);
>    END LOOP;
>   END IF;
> 
>   RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
> 
> and then
> 
> CREATE TRIGGER update_usergrouptest_from_test
> AFTER UPDATE ON generic.test
> FOR EACH ROW EXECUTE PROCEDURE generic.update_usergrouptest_from_test();

> Hans
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com