Trigger not firing

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Trigger not firing

Hans-2
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





Reply | Threaded
Open this post in threaded view
|

Re: Trigger not firing

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Trigger not firing

Hans-2
>> 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



Reply | Threaded
Open this post in threaded view
|

Re: Trigger not firing

Pavel Stehule


po 1. 6. 2020 v 12:09 odesílatel Hans <[hidden email]> napsal:
>> 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;

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));

Regards

Pavel

 
  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



Reply | Threaded
Open this post in threaded view
|

Re: Trigger not firing

Hans-2

> 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




Reply | Threaded
Open this post in threaded view
|

Re: Trigger not firing

Adrian Klaver-4
In reply to this post by Hans-2
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
[hidden email]