Re: What needs to be done for real Partitioning?

On L, 2005-03-19 at 19:03 -0500, Tom Lane wrote:
> 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.
>

If we go with my proposal (other post in this thread) of doing most of
the partitioning in the level between logical file and physikal 1Gb
storage files, then adding a partition should be nearly the same as
crossing the 1G boundary is now.

removing the partition would be just plain vacuum (if we can make pg
shring each 1G subfile independently)

> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Hannu Krosing <hannu(at)tm(dot)ee>