Re: Auto create partition with declarative partitioning

On 2018-Oct-19, Jorge Torralba wrote:

> In the past I have had a before insert trigger which I would send to a
> create partition function. Extract the info from NEW.createdate, and use
> some logic to create the partition if it did not exists for the create date.
>
> However, now, with the partition by range, I get the error :
>
> Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.

Interesting. I don't think this was ever mentioned as a possible use
case for BEFORE triggers on partitioned tables, during the pg11
development. Basically we forbade BEFORE triggers on partitioned
tables. (You can find the reasons for that in the thread that discussed
row trigger development).

> Other than creating a bunch of partitions in advance, what are my options?

Create *one* partition in advance?

Another option would be to contribute the feature of automatically
creating the partitions for Postgres 12 :-)

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services