Re: Auto create partition with declarative partitioning

On Fri, Oct 19, 2018 at 5:02 PM Jorge Torralba <jorge(dot)torralba(at)gmail(dot)com>
wrote:

> I am doing this on 11 :(
>
> On Fri, Oct 19, 2018 at 1:57 PM Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
> wrote:
>
>> > On Oct 19, 2018, at 2:52 PM, Jorge Torralba <jorge(dot)torralba(at)gmail(dot)com>
>> wrote:
>> >
>> > Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.
>>
>> I believe 11 fixes this?
>>
>
>
> --
> Thanks,
>
> Jorge Torralba
> ----------------------------
>
> Note: This communication may contain privileged or other confidential
> information. If you are not the intended recipient, please do not print,
> copy, retransmit, disseminate or otherwise use the information. Please
> indicate to the sender that you have received this email in error and
> delete the copy you received. Thank You.
>

It is not recommended that you create partitions with triggers, either at
the time they are needed or even future ones. This can cause contention
when even the slightly write load is on that partition set and 1 or more
transactions require the creation of a new child table. All following
transactions will be put into a wait state until the new table is created.

I would recommend a separate process that pre-creates the tables in enough
time before they are needed. The pg_partman tool that I've been working on
can do all this for you if you're able to install third-party extensions.
As of version 4.0.0, it has full support of all the new features in 11.

https://github.com/pgpartman/pg_partman

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com