Re: Single Index Tuple Chain (SITC) method

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Here is an overview of the SITC method:
> http://momjian.us/cgi-bin/pgsitc

A pretty fundamental problem is that the method assumes it's OK to
change the CTID of a live tuple (by swapping its item pointer with some
expired version). It is not --- this will break:
* active UPDATEs and DELETEs that may have fetched the CTID
but not yet completed processing to decide whether to change
the tuple;
* pending AFTER ROW triggers, such as foreign key checks;
* ODBC as well as other applications that assume CTID is a
usable unique row identifier within transactions.
VACUUM FULL can get away with moving tuples to new CTIDs because it takes
AccessExclusiveLock, so there can be no open transactions with knowledge
of current CTIDs in the table. This is not OK for something that's
supposed to happen in plain UPDATEs, though.

Another problem is you can't recycle tuples, nor item ids, without
taking a VACUUM-style lock on the page (LockBufferForCleanup). If
anyone else is holding a pin on the page they risk getting totally
confused --- for instance, a seqscan will either miss a tuple or scan it
twice depending on which direction you're juggling item ids around it.
The concurrency loss involved in LockBufferForCleanup is OK for
background-maintenance operations like VACUUM, but I seriously doubt
anyone will find it acceptable for UPDATE. It could easily create
application-level deadlocks, too. (VACUUM is safe against that because
it only holds one lock.)

regards, tom lane