po 1. 6. 2020 v 12:09 odesÃlatel Hans <hguij...@xs4all.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 > > > >