On 7/21/24 04:13, Tom Lane wrote:
> David Rowley <dgrowleyml@gmail.com> writes:
>> No resource links, but if you move a tuple to another page then you
>> must also adjust the index. If you have no exclusive lock on the
>> table, then you must assume older transactions still need the old
>> tuple version, so you need to create another index entry rather than
>> re-pointing the existing index entry's ctid to the new tuple version.
>
> The actually tricky part about that is that you have to ensure that
> any concurrent scan will see one of the two copies --- not both,
> and not neither. This is fairly hard when the concurrent query
> might be using any of several scan methods, and might or might not
> have visited the original tuple before you commenced the move.
> You can solve it by treating the move more like an UPDATE, that
> is the new tuple gets a new XID, but that has its own downsides;
> notably that it must block/be blocked by concurrent real UPDATEs.
>
True, but the UPDATE approach probably comes with it's own set of
issues. For example, it likely breaks tracking of commit timestamps, and
if an application depends on that e.g. for conflict resolution ...
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company