Re: Issues with patitionning and triggers

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.