Re: Partitioning and triggers

On Sun, Nov 17, 2013 at 8:46 AM, Edson Richter <edsonrichter(at)hotmail(dot)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