po 1. 6. 2020 v 12:09 odesílatel Hans <hguijtra@xxxxxxxxx> 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
