Re: Issues with patitionning and triggers - Mailing list pgsql-general

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