Horton

Re: UPDATE of partition key

David G. Johnston <david.g.johnston@gmail.com>

View thread View on postgr.es
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Greg Stark <stark@mit.edu>
Cc: Simon Riggs <simon@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, Amit Khandekar <amitdkhan.pg@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-02-25T19:01:42Z
Lists: pgsql-hackers 
On Sat, Feb 25, 2017 at 11:11 AM, Greg Stark <stark@mit.edu> wrote:

> On 24 February 2017 at 14:57, David G. Johnston
> <david.g.johnston@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.

Commits

  1. Avoid referencing off the end of subplan_partition_offsets.

  2. Allow UPDATE to move rows between partitions.

  3. Remove useless lookup of root partitioned rel in ExecInitModifyTable().

  4. Factor error generation out of ExecPartitionCheck.

  5. Minor preparatory refactoring for UPDATE row movement.

  6. Simplify and encapsulate tuple routing support code.

  7. Avoid coercing a whole-row variable that is already coerced.

  8. Use ResultRelInfo ** rather than ResultRelInfo * for tuple routing.

  9. Make RelationGetPartitionDispatchInfo expand depth-first.

  10. Expand partitioned tables in PartDesc order.

  11. Use a real RT index when setting up partition tuple routing.

  12. Fix transition tables for partition/inheritance.

  13. Fix confusion about number of subplans in partitioned INSERT setup.

  14. Prevent BEFORE triggers from violating partitioning constraints.

  15. Fire per-statement triggers on partitioned tables.

  16. Fix reporting of violations in ExecConstraints, again.

  17. Don't scan partitioned tables.

  18. Allow FDWs to push down quals without breaking EvalPlanQual rechecks.