Re: Issues with patitionning and triggers

Samuel Gilbert <samuel.gilbert@ec.gc.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.

> 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