Обсуждение: 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]. [1]: https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre
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