Обсуждение: Trigger to create automated range partition table
Hi friends,
As a part of migration from Oracle to AWS Aurora postgress. I have converted the partition table using ora2pg tool.
In Oracle we have feature of automated partition table creation when we insert the data, but in postgress 10.4 that feature is not enabled.
Kindly help me and give some rough idea or example to create a trigger on table to create automated partition table when we insert the data.Thank you
Bhanu P Murari.
On Tue, Nov 20, 2018 at 10:42 PM bhanu prabhakar <bhanu1208@gmail.com> wrote:
Hi friends,As a part of migration from Oracle to AWS Aurora postgress. I have converted the partition table using ora2pg tool.In Oracle we have feature of automated partition table creation when we insert the data, but in postgress 10.4 that feature is not enabled.Kindly help me and give some rough idea or example to create a trigger on table to create automated partition table when we insert the data.Thank youBhanu P Murari.
It is not recommended that table creation for partitions be done via triggers at this time unless the write level on the table is very, very low. Since DDL is transactional in PostgreSQL, this can cause contention on very active tables when that new child table is created.
I maintain an extension called pg_partman that can help with the automatic pre-creation of child tables before they are needed for time & serial/id based partition sets.
Keith
I confirm: This is not working now for pg10 see https://www.postgresql-archive.org/BUG-15437-Segfault-during-insert-into-declarative-partitioned-table-with-a-trigger-creating-partition-td6054925.html this kind of trigger is even not supported in pg11 postgres=# CREATE TRIGGER check_pkey BEFORE INSERT ON tabpart postgres-# FOR EACH ROW EXECUTE PROCEDURE public.f_check_pkey(); ERROR: "tabpart" is a partitioned table DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers. There are many development works regarding this see all threads about ATTACH/DETACH PARTITION CONCURRENTLY for example Today the only solution is to create those partitions before the first INSERT statement ;o( Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html