Обсуждение: Tuple visibility within a single XID

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?

I thought of this because of a post to -general. It's certainly not a 
common case, but it seems like there's not much downside...
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

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

On 4/7/15 8:11 PM, Peter Geoghegan wrote:
> 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.

Yeah, every time I get into the really nitty-gritty details of this 
stuff it gets scary. That's why I didn't even bother with a patch before 
asking.

Thanks for setting me straight. :)
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On Tue, Apr 7, 2015 at 6:11 PM, Peter Geoghegan <pg@heroku.com> wrote:
> No. For one thing, unique index enforcement still requires the tuples
> to be treated as a conflict while the other transaction is running
> IMV.
>

Not sure if I understand correctly: in uniqueness check, we see all
possible tuples with a dirty snapshot.  For a tuple version inserted
and updated by myself, it is surely dead no matter how the transaction
ends. So I interpret that we can safely pruning the version.

Early pruning may cause some behavior change though. For example, here is a T1:

begin;  -- loop the following statements many times  insert into pk values (1);  -- uniqueness defined  delete from
pk;
abort;

If another transaction T2 coming later than T1, and if we prune early,
then T1 can suddenly hang on insertion waiting for T2 to complete. But
does this violate any isolation rule?
Thanks,
Qingqing

On Tue, Apr 7, 2015 at 7:16 PM, Qingqing Zhou <zhouqq.postgres@gmail.com> wrote:
> If another transaction T2 coming later than T1, and if we prune early,
> then T1 can suddenly hang on insertion waiting for T2 to complete. But
> does this violate any isolation rule?

Well, it means that you don't lock a row that you delete (and values
that appeared in that row if they're constrained by a unique index)
just because you happened to also insert that row. That would probably
break client code.

-- 
Peter Geoghegan

Qingqing Zhou <zhouqq.postgres@gmail.com> writes:
> Not sure if I understand correctly: in uniqueness check, we see all
> possible tuples with a dirty snapshot.  For a tuple version inserted
> and updated by myself, it is surely dead no matter how the transaction
> ends. So I interpret that we can safely pruning the version.

It may be dead in the future, but unless you can prove that your session
does not still contain a snapshot that could "see" the row, you can't
prune it.  Considering only the current query is a fundamental error.
        regards, tom lane