Subtransaction commits and Hot Standby - Mailing list pgsql-hackers

Subtransactions cause a couple of problems for Hot Standby:
* we don't record new subtransactionids in WAL, so we have no direct way
to issue updates to subtrans while in recovery mode as would normally
happen when we assign subtransaction ids (subxids)
* we don't record subtransaction commit in WAL, so we have no direct way
to issue updates to clog to show TRANSACTION_STATUS_SUB_COMMITTED while
in recovery mode

The obvious solutions are to record this in WAL by doing
(1) every new subxid create an entry in WAL, either by creating a whole
new record or by augmenting the first WAL record created by the new
subxid
(2) we issue a WAL record every time we subcommit

Lets see if we can improve on that:

If we did not have an entry in subtrans, what would happen? We would be
unable to tell whether a subtransaction was visible or not in a snapshot
because TransactionIdIsInProgress() requires it. And so, if an xid was
active at time-of-snapshot yet has now committed we would mistakenly
conclude it was visible. So we must be able to track every xid back to
our snapshot *if* there should be a linkage.

However, we only need to check subtrans if the snapshot cache has
overflowed. Is it possible to only insert entries into subtrans when
they will be required, rather than always? Hmmm, nothing clear, so we
must do (1) it seems.

If we did not have an entry in clog, what would happen? Correct clog
entries are *not* required to prove whether an xid is a subxid and part
of our snapshot, we only need subtrans for that.
XidInMVCCSnapshot() calls SubTransGetTopmostTransaction() which doesn't
touch clog at all. Clog entries are currently required when we issue
TransactionIdDidCommit() on a subxid. If clog entries were missing then
we would only make a mistake about the correct status of a subxid when
we were mid-way through updating the status of an xid to committed.

So clog update might still be needed for subtransactions, but not until
commit time, so we can completely avoid the need to generate WAL records
at time of subcommit for use in Hot Standby. And it would seem that if
we update the clog atomically, we would not need to mark the subcommit
state in clog at all, even in normal running.

Right now we lock and unlock the clog for each committed subtransaction
at commit time, which is wasteful. A better scheme: pre-scan the list of xids to derive list of pages if we have just a
singlepage to update {       update all entries on page in one action } else {       loop thru xids marking them all as
subcommittedmarktop level transaction committed       loop thus xids again marking them all as committed }
 

All clog updates would be performed page-at-a-time, in ascending xid
order.

This seems likely to work well since many subtransactions will be on
same clog page as the top-level xid and the locking will often be more
efficient than the current case of repeated single lock acquisitions. It
also means we can skip RecordSubTransactionCommit() entirely,
significantly reducing clog contention.

Anybody see a problem there?

If not, I will work on separate patches:
* re-work subtrans commit so that we use the form described above. This
should improve performance for both normal and standby modes, hence do
this as a stand-alone patch
* include code in the main hot standby patch to update subtrans during
recovery when a new subtransaction is created

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support