Re: UPDATE of partition key

On Sat, Feb 25, 2017 at 11:11 AM, Greg Stark <stark(at)mit(dot)edu> wrote:

> On 24 February 2017 at 14:57, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > I dislike an error. I'd say that making partition "just work" here is
> > material for another patch. In this one an update of the partition key
> can
> > be documented as shorthand for delete-returning-insert with all the
> > limitations that go with that. If someone acceptably solves the ctid
> > following logic later it can be committed - I'm assuming there would be
> no
> > complaints to making things just work in a case where they only sorta
> > worked.
>
> Personally I don't think there's any hope that there will ever be
> cross-table ctids links. Maybe one day there will be a major new table
> storage format with very different capabilities than today but in the
> current architecture it seems like an impossible leap.
>

​How about making it work without a physical token dynamic? For instance,
let the server recognize the serialization error but instead of returning
it to the client the server itself tries again.​

> I would expect everyone to come to terms with the basic idea that
> partition key updates are always going to be a corner case. The user
> defined the partition key and the docs should carefully explain to
> them the impact of that definition. As long as that explanation gives
> them something they can work with and manage the consequences of
> that's going to be fine.
>
> What I'm concerned about is that silently giving "wrong" answers in
> regular queries -- not even ones doing the partition key updates -- is
> something the user can't really manage. They have no way to rewrite
> the query to avoid the problem if some other user or part of their
> system is updating partition keys. They have no way to know the
> problem is even occurring.
>
> Just to spell it out -- it's not just "no-op updates" where the user
> sees 0 records updated. If I update all records where
> username='stark', perhaps to set the "user banned" flag and get back
> "9 records updated" and later find out that I missed a record because
> someone changed the department_id while my query was running -- how
> would I even know? How could I possibly rewrite my query to avoid
> that?
>

​But my point is that this isn't a regression from current behavior. If I
deleted one of those starks and re-inserted them with a different
department_id that brand new record wouldn't be banned. In short, my take
on this patch is that it is a performance optimization. Making the UPDATE
command actually work as part of its implementation detail is a happy
byproduct.​

From the POV of an external observer it doesn't have to matter whether the
update or delete-insert SQL was used. It would be nice if the UPDATE
version could keep logical identity maintained but that is a feature
enhancement.

Failing if the other session used the UPDATE SQL isn't wrong; and I'm not
against it, I just don't believe that it is better than maintaining the
status quo semantics.

That said my concurrency-fu is not that strong and I don't really have a
practical reason to prefer one over the other - thus I fall back on
maintaining internal consistency.

IIUC ​it is already possible, for those who care to do so, to get a
serialization failure in this scenario by upgrading isolation to repeatable
read.

David J.