Horton

Re: UPDATE of partition key

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Amit Khandekar <amitdkhan.pg@gmail.com>, Greg Stark <stark@mit.edu>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-02-24T10:20:51Z
Lists: pgsql-hackers 
On Fri, Feb 24, 2017 at 3:24 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> I'd give the view that we cannot silently ignore this issue, bearing
> in mind the point that we're expecting partitioned tables to behave
> exactly like normal tables.

At the risk of repeating myself, I don't expect that, and I don't
think it's a reasonable expectation.  It's reasonable to expect
partitioning to be notably better than inheritance (which I think it
already is) and to provide a good base for future work (which I think
it does), but I think getting them to behave exactly like normal
tables (except for the things we want to be different) will take
another ten years of development work.

> In my understanding the issue is that UPDATEs will fail to update a
> row when a valid row exists in the case where a row moved between
> partitions; that behaviour will be different to a standard table.

Right, when at READ COMMITTED and EvalPlanQual would have happened otherwise.

> It is of course very good that we have something ready for this
> release and can make a choice of what to do.
>
> Thoughts
>
> 1. Reuse the tuple state HEAP_MOVED_OFF which IIRC represent exactly
> almost exactly the same thing. An UPDATE which gets to a
> HEAP_MOVED_OFF tuple will know to re-find the tuple via the partition
> metadata, or I might be persuaded that in-this-release it is
> acceptable to fail when this occurs with an ERROR and a retryable
> SQLCODE, since the UPDATE will succeed on next execution.

I've got my doubts about whether we can make that bit work that way,
considering that we still support pg_upgrade (possibly in multiple
steps) from old releases that had VACUUM FULL.  We really ought to put
some work into reclaiming those old bits, but there's probably no time
for that in v10.

> 2. I know that DB2 handles this by having the user specify WITH ROW
> MOVEMENT to explicitly indicate they accept the issue and want update
> to work even with that. We could have an explicit option to allow
> that. This appears to be the only way we could avoid silent errors for
> foreign table partitions.

Yeah, that's a thought.  We could give people a choice between (a)
updates that cause rows to move between partitions just fail and (b)
such updates work but with EPQ-related deficiencies.  I had previously
thought that, given those two choices, everybody would like (b) better
than (a), but maybe not.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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.