Re: UPDATE of partition key

On Fri, Feb 24, 2017 at 3:24 PM, Simon Riggs <simon(at)2ndquadrant(dot)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