On Sat, Feb 25, 2017 at 11:11 AM, Greg Stark <st...@mit.edu> wrote: > On 24 February 2017 at 14:57, David G. Johnston > <david.g.johns...@gmail.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.