Обсуждение: Auto create partition with declarative partitioning
Trying to make this work but am having difficulties.
I have created a table with a timestamp column that i wish to partition by range using the timestamp column.
My objective is to test if a partition exists for the date range and create it if it does not.
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.
Therefore I cannot call the function to check if the partition exists.
Other than creating a bunch of partitions in advance, what are my options?
--
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.
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.
> On Oct 19, 2018, at 2:52 PM, Jorge Torralba <jorge.torralba@gmail.com> wrote: > > Partitioned tables cannot have BEFORE / FOR EACH ROW triggers. I believe 11 fixes this?
I am doing this on 11 :(
On Fri, Oct 19, 2018 at 1:57 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Oct 19, 2018, at 2:52 PM, Jorge Torralba <jorge.torralba@gmail.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.
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.
On Fri, Oct 19, 2018 at 5:02 PM Jorge Torralba <jorge.torralba@gmail.com> wrote:
I am doing this on 11 :(On Fri, Oct 19, 2018 at 1:57 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:> On Oct 19, 2018, at 2:52 PM, Jorge Torralba <jorge.torralba@gmail.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.
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