Josh Berkus <email@example.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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend