> "The modification date must be updated if any row is modified in any way." > > If that is the case shouldn't the trigger also cover UPDATE? You completely right about that! I actually have both configured, but I focused only on the INSERT to try keep the length of my post as short as possible. As Tom Lane pointed out, it's hard to get help without a complete self- contained example. I will work on writing that up tomorrow. Cheers! On 2014-02-18 15:02:41 Adrian Klaver wrote: > On 02/18/2014 02:42 PM, Samuel Gilbert wrote: > > On 2014-02-18 14:25:59 Adrian Klaver wrote: > >> On 02/18/2014 02:10 PM, Samuel Gilbert wrote: > >>> I have data warehousing DB 2 fairly big tables : one contains about 200 > >>> million rows and the other one contains about 4 billion rows. Some > >>> queries > >>> are now taking way too long to run (> 13 hours). I need to get these > >>> queries to run in an hour or so. The slowdown was gradual, but I > >>> eventually hit a wall, when the planner stopped using indexes. > >>> > >>> > >>> The other issue I'm encountering is that I also have very simple BEFORE > >>> UPDATE and BEFORE INSERT triggers that set the modification date on > >>> every > >>> single row > >>> > >>> > >>> CREATE FUNCTION set_modificationDate() RETURNS TRIGGER AS $$ > >>> BEGIN > >>> > >>> NEW.modificationDate := now(); > >>> RETURN NEW; > >>> > >>> END; > >>> $$ LANGUAGE 'plpgsql'; > >>> > >>> The modification date must be updated if any row is modified in any way. > >>> I > >>> first tried to define the triggers on the parent table. This worked, > >>> but > >>> I > >>> realized that if a queries targets explicitly a child table, it could > >>> modify a row without the date being updated. I therefore dropped the > >>> triggers on the parent table and defined them for every child. To my > >>> great surprise, the insert below failed with a message saying that NULLs > >>> are not allowed in the modificationdate column. > >>> > >>> INSERT INTO observation > >>> (dataset, station, method, startdate, duration, value) > >>> VALUES (9, 2, 128, '2014-01-01 00:00:00', '24 hours', 42); > >>> > >>> Why isn't the BEFORE INSERT trigger on the child table being executed? > >> > >> Constraints are checked before triggers are run. > >> > >>> Cheers, > >>> Samuel Gilbert > > > > I don't think that is the case since I currently have the BEFORE INSERT > > trigger working on the non-partitioned version of the table. > > Sorry for steering you wrong. I could have sworn I saw the behavior I > mentioned, previously, when trying to do what you have done. > > > The modificationdate field has a NOT NULL constraint. Even if I > > explicitly > > provide a NULL for the modificationdate column, a date gets written in the > > table. This leads me to believe that the BEFORE INSERT trigger is really > > executed before the constraint is checked. > > > > What I don't understand is why the trigger doesn't appear to be executed > > when it's defined on a child table. I'll add a RAISE NOTICE to the > > trigger function to makes sure it's not getting called. > > Still not sure what is going on, but I do have a question based on this > statement from your original post: > > "The modification date must be updated if any row is modified in any way." > > If that is the case shouldn't the trigger also cover UPDATE? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
