Re: Exposing the Xact commit order to the user

On Mon, May 24, 2010 at 4:03 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> 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.

Well, AIUI, what you're really trying to do is derive the delta
between an old snapshot and a newer snapshot.

>> 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.

Yep, I see it now.

>  As far as I'm concerned this is only a
> problem if the user *requested* serializable behavior for all
> transactions involved.

Agreed.

> 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.

I suspect that's still going to be sort of hard on performance, but
maybe we should get serializable working and committed on one node
first and then worry about how to distribute it.  I think there might
be other approaches to this problem (global transaction coordinator?
standby requests snapshot from primary?).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company