Re: Issues with patitionning and triggers

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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com