Thread: 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