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
