Re: UPDATE of partition key

On Wed, Feb 15, 2017 at 01:06:32PM +0530, Amit Khandekar wrote:
> On 14 February 2017 at 22:24, David Fetter <david(at)fetter(dot)org> wrote:
> > On Mon, Feb 13, 2017 at 05:31:56PM +0530, Amit Khandekar wrote:
> >> Currently, an update of a partition key of a partition is not
> >> allowed, since it requires to move the row(s) into the applicable
> >> partition.
> >>
> >> Attached is a WIP patch (update-partition-key.patch) that removes
> >> this restriction. When an UPDATE causes the row of a partition to
> >> violate its partition constraint, then a partition is searched in
> >> that subtree that can accommodate this row, and if found, the row
> >> is deleted from the old partition and inserted in the new
> >> partition. If not found, an error is reported.
> >
> > This is great!
> >
> > Would it be really invasive to HINT something when the subtree is
> > a proper subtree?
>
> I am not quite sure I understood this question. Can you please
> explain it a bit more ...

Sorry. When an UPDATE can't happen, there are often ways to hint at
what went wrong and how to correct it. Violating a uniqueness
constraint would be one example.

When an UPDATE can't happen and the depth of the subtree is a
plausible candidate for what prevents it, there might be a way to say
so.

Let's imagine a table called log with partitions on "stamp" log_YYYY
and subpartitions, also on "stamp", log_YYYYMM. If you do something
like

UPDATE log_2017 SET "stamp"='2016-11-08 23:03:00' WHERE ...

it's possible to know that it might have worked had the UPDATE taken
place on log rather than on log_2017.

Does that make sense, and if so, is it super invasive to HINT that?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate