Re: Issues with patitionning and triggers

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?

>
>

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