Re: Trigger not firing

po 1. 6. 2020 v 12:09 odesílatel Hans <hguijtra(at)xs4all(dot)nl> 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
>
>
>
>