pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger to create automated range partition table
3+ messages / 3 participants
[nested] [flat]
* Trigger to create automated range partition table
@ 2018-11-21 03:41 bhanu prabhakar <bhanu1208@gmail.com>
2018-11-21 06:40 ` Re: Trigger to create automated range partition table Keith <keith@keithf4.com>
0 siblings, 1 reply; 3+ messages in thread
From: bhanu prabhakar @ 2018-11-21 03:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger to create automated range partition table
2018-11-21 03:41 Trigger to create automated range partition table bhanu prabhakar <bhanu1208@gmail.com>
@ 2018-11-21 06:40 ` Keith <keith@keithf4.com>
2018-11-21 08:09 ` Re: Trigger to create automated range partition table legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Keith @ 2018-11-21 06:40 UTC (permalink / raw)
To: bhanu1208@gmail.com; +Cc: pgsql-admin@lists.postgresql.org
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 you
>
> Bhanu 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.
https://github.com/pgpartman/pg_partman
Keith
http://www.keithf4.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger to create automated range partition table
2018-11-21 03:41 Trigger to create automated range partition table bhanu prabhakar <bhanu1208@gmail.com>
2018-11-21 06:40 ` Re: Trigger to create automated range partition table Keith <keith@keithf4.com>
@ 2018-11-21 08:09 ` legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: legrand legrand @ 2018-11-21 08:09 UTC (permalink / raw)
To: pgsql-admin
I confirm: This is not working now
for pg10 see
https://www.postgresql-archive.org/BUG-15437-Segfault-during-insert-into-declarative-partitioned-tab...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-11-21 08:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-21 03:41 Trigger to create automated range partition table bhanu prabhakar <bhanu1208@gmail.com>
2018-11-21 06:40 ` Keith <keith@keithf4.com>
2018-11-21 08:09 ` legrand legrand <legrand_legrand@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox