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. 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
