Thread: Partitioning and triggers

Dear community,

In documentation, when partitioning tables, it is said that "Optionally,
define a trigger or rule to redirect data inserted into the master table
to the appropriate partition."
Is the trigger creation optional? I mean, partitioning will not work as
expected if we don't have the trigger, right? Or will PostgreSQL "decide
automatically" which table to use?
If the trigger is not there, when I add a row to "measurement" table
(like in docs example), it will be phisically inserted into
"measurement" table, not in the childs tables.
Am I right in this understanding?

Thanks,

Edson

On Sun, Nov 17, 2013 at 8:46 AM, Edson Richter <edsonrichter@hotmail.com> wrote:

Dear community,

In documentation, when partitioning tables, it is said that "Optionally, define a trigger or rule to redirect data inserted into the master table to the appropriate partition."
Is the trigger creation optional? I mean, partitioning will not work as expected if we don't have the trigger, right?

That depends on what you expect :)

 

Or will PostgreSQL "decide automatically" which table to use?
If the trigger is not there, when I add a row to "measurement" table (like in docs example), it will be phisically inserted into "measurement" table, not in the childs tables.
Am I right in this understanding?

Yes.  And if there is a constraint that blocks it from going into the parent table, you will get an error.  If you have the constraint but not the trigger, that means it is the application's job to insert into the correct partition.  This can make sense as triggers have a lot of overhead, whereas the application can usually do this with less overhead.  Or you can have neither the trigger nor the constraint, and have it go into the parent table by design.  Whether that is a good idea depends on why you are partitioning.

Cheers,

Jeff