Improve default partition
Hello all,
I was just playing around with table partitioning and noticed
1. When one inserts into a parent table with no partitions defined
yet, it errors out
2. Similarly, if we try to insert into a parent table a value which is
not covered in any partition and has no default partition defined, it
errors out
This does not sound very convenient. I was thinking of having some
mechanism for such insertions which automatically creates a default
partition and gives a notice for the user to know that it is going to
the default partition. Basically, always having a default partition.
After all default is something that remains there by default, isn't
it?
I will be happy to know your thoughts on this.
--
Regards,
Rafia Sabih
On Tue, Aug 20, 2019 at 4:45 PM Rafia Sabih <rafia.pghackers@gmail.com> wrote:
This does not sound very convenient. I was thinking of having some
mechanism for such insertions which automatically creates a default
partition and gives a notice for the user to know that it is going to
the default partition.
If I remember correctly, there is a side effect when it's impossible to create
any new partitions, that intersects with values from default partition (one has
to detach a default partition, remove rows and attach it back). That could
probably be not always a desired outcome.
Btw, there is a somewhat similar discussion in the ongoing thread [1]/messages/by-id/alpine.DEB.2.21.1907150711080.22273@lancre.
[1]: /messages/by-id/alpine.DEB.2.21.1907150711080.22273@lancre
On 2019-Aug-20, Rafia Sabih wrote:
This does not sound very convenient. I was thinking of having some
mechanism for such insertions which automatically creates a default
partition and gives a notice for the user to know that it is going to
the default partition. Basically, always having a default partition.
After all default is something that remains there by default, isn't
it?
There are shortcomings to having a default partition, so I don't think
this would be an universally welcome feature.
I think auto-creation of partitions might be useful in some cases, but
not by default since there are caveats (possibility of deadlocks) and
not of the default partition.
The major problem I see with the default partition is that there's no
way to create a partition that overlaps existing contents of the default
partition, short of blocking everyone.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services