Re: Tuple visibility within a single XID

On Tue, Apr 7, 2015 at 5:59 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> My understanding is that all subtransactions get their own unique XID
> (assuming they need one), and that CommandId can't move backwards within a
> transaction. If that's correct, then shouldn't we be able to prune tuples
> where XMIN and XMAX match our *exact* XID (not all the extra stuff that
> TransactionIdIsCurrentTransactionId() does) and CommandId <
> CurrentCommandId?

No. For one thing, unique index enforcement still requires the tuples
to be treated as a conflict while the other transaction is running
IMV.

For another, this is necessary today (from ExecUpdate()):

/*
* The target tuple was already updated or deleted by the
* current command, or by a later command in the current
* transaction.  The former case is possible in a join UPDATE
* where multiple tuples join to the same target tuple. This
* is pretty questionable, but Postgres has always allowed it:
* we just execute the first update action and ignore
* additional update attempts.
*
* The latter case arises if the tuple is modified by a
* command in a BEFORE trigger, or perhaps by a command in a
* volatile function used in the query.  In such situations we
* should not ignore the update, but it is equally unsafe to
* proceed.  We don't want to discard the original UPDATE
* while keeping the triggered actions based on it; and we
* have no principled way to merge this update with the
* previous ones.  So throwing an error is the only safe
* course.
*
* If a trigger actually intends this type of interaction, it
* can re-execute the UPDATE (assuming it can figure out how)
* and then return NULL to cancel the outer update.
*/
if (hufd.cmax != estate->es_output_cid)
ereport(ERROR,            (errcode(ERRCODE_TRIGGERED_DATA_CHANGE_VIOLATION),             errmsg("tuple to be updated
wasalready modified by an
 
operation triggered by the current command"),             errhint("Consider using an AFTER trigger instead of a
BEFORE trigger to propagate changes to other rows.")));


You're not the first to consider trying something like this in
specific scenarios, but my work on UPSERT leads me to believe it isn't
workable.
-- 
Peter Geoghegan