Re: Issues with patitionning and triggers

On 2014-02-18 17:59:35 Tom Lane wrote:
> Samuel Gilbert <samuel(dot)gilbert(at)ec(dot)gc(dot)ca> writes:
> > All of this was done on PostgreSQL 9.2.0 64-bit compiled from the official
>
> > source. Significant changes in postgresql.conf :
> Why in the world are you using 9.2.0? You're missing a year and a half
> worth of bug fixes, some of them quite serious.

Yes. I know and I bear the pain and shame not running at least the latest
revision of the 9.2 branch. Unfortunately, it's hard to get my manager to
view the update of software that "just works" as something to prioritize.

The good news is that your reply is a good argument to do so! :)

Cheers!

>
> > INSERT ... RETURNING does not work with partitioned tables, since the
> > trigger function on the parent that dispatches new rows to the children
> > tables, must return NULL. If the trigger function on the parent ends
> > with "RETURN NEW", INSERT ... RETURNING works, but new rows are
> > duplicated; they are inserted both in the parent and child tables.
> >
> > Is there a way to make INSERT ... RETURNING work without duplicating the
> > rows?
> Fraid not --- it only shows what got inserted into the parent table, which
> is nothing if you're using this technique.
>
> > 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.
>
> You'd have to provide a self-contained example for anyone to help you with
> that. The most obvious explanation is that you forgot to attach the
> trigger to the specific child table ...
>
> regards, tom lane