Robert Haas wrote:
> It's a tool for replication solutions to use.
I was thrown by the original post referencing "data warehousing".
For replication I definitely see that it would be good to provide
some facility to grab a coherent snapshot out of the transaction
stream, but I'm still not clear on a use case where other solutions
aren't better. If you want a *particular* past snapshot, something
akin to the transactional caching that Dan Ports mentioned seems
best. If you just want a coherent snapshot like snapshot isolation,
the current mechanisms seem to work (unless I'm missing something?).
If you want solid data integrity querying the most recent replicated
data, the proposal I posted earlier in the thread is the best I can
see, so far.
> if the reconstructed transaction order inferred by SSI doesn't
> match the actual commit order, can we get a serialization anomaly
> on the standby by replaying transactions there in commit order?
Yes. If we don't do *something* to address it, the replicas
(slaves) will operate as read-only snapshot isolation, not true
serializable.
> Can you give an example and explain how your proposal would solve
> it?
I gave an example (without rigorous proof accompanying it, granted)
earlier in the thread. In that example, if you allow a selection
against a snapshot which includes the earlier commit (the update of
the control table) and before the later commits (the receipts which
used the old deposit date) you have exactly the kind of
serialization anomaly which the work in progress prevents on the
source (master) database -- the receipts *appear* to run in earlier
transactions because the see the pre-update deposit date, but they
show up out of order. As far as I'm concerned this is only a
problem if the user *requested* serializable behavior for all
transactions involved.
If we send the information I suggested in the WAL stream, then any
slave using the WAL stream could build a snapshot for a serializable
transaction which excluded serializable transactions from the source
which overlap with still-pending serializable transactions on the
source. In this example, the update of the control table would not
be visible to a serializable transaction on the slave until any
overlapping serializable transactions (which would include any
receipts using the old date) had also committed, so you could never
see the writes out of order.
I don't think that passing detailed predicate locking information
would be feasible from a performance perspective, but since the
slaves are read-only, I think it is fine to pass just the minimal
transaction-level information I described.
-Kevin