Re: Trigger not firing - Mailing list pgsql-general
From | Hans |
---|---|
Subject | Re: Trigger not firing |
Date | |
Msg-id | bf8e0535-4197-a81d-f4b2-ce2e18ec9711@xs4all.nl Whole thread Raw |
In response to | Re: Trigger not firing (Adrian Klaver <adrian.klaver@aklaver.com>) |
Responses |
Re: Trigger not firing
(Pavel Stehule <pavel.stehule@gmail.com>)
Re: Trigger not firing (Adrian Klaver <adrian.klaver@aklaver.com>) |
List | pgsql-general |
>> 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
pgsql-general by date: