Master-slave visibility order - Mailing list pgsql-hackers

I'm currently implementing commit sequence number (CSN) based
snapshots and I hit a design decision that I would like to resolve
before I have too much code to rewrite.

The issue is commit visibility ordering on slaves. As a couple of
threads on hackers have already noted, currently commit order on
slaves can differ from what is seen on master. This arises from the
fact that on master commit visibility is determined by the order of
ProcArrayLock acquisition by ProcArrayEndTransaction(). On slaves
commit visibility is exactly the order of commit records in WAL.
Because XLogInsert() in RecordTransactionCommit() is not interlocked
with ProcArrayEndTransaction() these orders can differ. In case of
mixed sync and async transactions they in fact are quite likely to
differ due to the durability wait in RecordTransactionCommit().

It's not possible to change master commit order to match WAL order
because then either async transactions must either wait behind sync
transactions before returning losing the point of async; or async
transactions must return without becoming visible, changing user
visible semantics; or sync transactions must become visible before
they become durable, again changing user visible semantics.

As it's not possible to change master commit order, the slave
visibility order must change for the orders to be consistent. WAL
currently doesn't have the information to reconstruct master commit
order. Either we need to add a new WAL record for the commit order
(only necessary when wal_level=hot_standby) or add a side channel to
replication connections to communicate commit order information.

One more consideration here is the wish expressed by several hackers
that commit record LSNs could be used as CSNs. One of the most
interesting benefits of this is the property of LSNs being the same
over the whole cluster, meaning that it would be relatively simple to
create cluster wide consistent snapshots.

I currently see the following courses of action:

1. Do nothing about the inconsistency, use a transient global counter
for master commit order and commit record LSN for slaves.  Pro: doesn't change any semantics  Con: we are not making
anyprogress towards cluster wide snapshots 
or even serializable transactions on slaves.

2. Create a new WAL record type that is inserted when a transaction
becomes visible. LSN of this record determines transaction visibility
order. Async transactions can be optimized to skip this record. This
record does not need to be flushed.  Pro: cluster wide consistency, replication method agnostic  Con: one extra WAL
recordinsertion per writing transaction. (32 
bytes of WAL per tx)

3. Use a transient global counter on master, send xid-csn pairs to
slave via a side channel on the replication connection.  Pro: Less overhead than WAL records  Con: replication protocol
needs(possibly invasive) changes, WAL 
shipping based replication can't use this mechanism, lots of extra
code required.

4. Make the choice between 1 and 2 user configurable (it seems to me
that it could even be changed without a restart).

Thoughts?

Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de