Re: What needs to be done for real Partitioning?

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>> -- INSERT INTO should automatically create new partitions where necessary
>>> -- DELETE FROM should automatically drop empty partitions
>>
>> I am not sure I agree with either of those, and the reason is that they
>> would turn low-lock operations into high-lock operations.

> For INSERT, I think that's a problem we need to work through.

Possibly, but I'm concerned about locking and deadlock issues. The
reason that this is iffy is you would start the operation with only
an INSERT-grade lock, and then discover that you needed to add a
partition, which is surely something that needs an exclusive-grade
lock (consider two sessions trying to add the same partition at the
same time). So I don't see how to do it without lock upgrading,
and lock upgrading is always a recipe for deadlocks.

The DELETE case is even worse because you can't physically release
storage until you're sure nothing in it is needed anymore by any open
transaction --- that introduces VACUUM-like issues as well as the
deadlock problem.

> Or are you proposing, instead, that attempts to insert beyond the
> range raise an error?

That was what I had in mind --- then adding partitions would require
a manual operation. This would certainly be good enough for "phase I"
IMHO.

regards, tom lane