Re: Logical decoding of sequence advances, part II - Mailing list pgsql-hackers

<p dir="ltr"><p dir="ltr">On 23 Aug 2016 20:10, "Kevin Grittner" <<a
href="mailto:kgrittn@gmail.com">kgrittn@gmail.com</a>>wrote:<br /> ><br /> > On Mon, Aug 22, 2016 at 6:39 PM,
CraigRinger <<a href="mailto:craig@2ndquadrant.com">craig@2ndquadrant.com</a>> wrote:<br /> > > On 23 Aug
201605:43, "Kevin Grittner" <<a href="mailto:kgrittn@gmail.com">kgrittn@gmail.com</a>> wrote:<br /> > >>
OnMon, Aug 22, 2016 at 3:29 PM, Robert Haas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>
wrote:<br/> > >><br /> > >>> it seems to me that<br /> > >>> this is just one facet of
amuch more general problem: given two<br /> > >>> transactions T1 and T2, the order of replay must match
theorder of<br /> > >>> commit unless you can prove that there are no dependencies between<br /> >
>>>them.  I don't see why it matters whether the operations are sequence<br /> > >>> operations or
dataoperations; it's just a question of whether they're<br /> > >>> modifying the same "stuff".<br />
><br/> > >> The commit order is the simplest and safest *unless* there is a<br /> > >> read-write
anti-dependencya/k/a read-write dependency a/k/a<br /> > >> rw-conflict: where a read from one transaction
seesthe "before"<br /> > >> version of data modified by the other transaction.  In such a case<br /> >
>>it is necessary for correct serializable transaction behavior for<br /> > >> the transaction that read
the"before" image to be replayed before<br /> > >> the write it didn't see, regardless of commit order.  If
you'renot<br /> > >> trying to avoid serialization anomalies, it is less clear to me<br /> > >> what
isbest.<br /> > ><br /> > > Could you provide an example of a case where xacts replayed in<br /> > >
commitorder will produce incorrect results?<br /> ><br /> > <a
href="https://wiki.postgresql.org/wiki/SSI#Deposit_Report">https://wiki.postgresql.org/wiki/SSI#Deposit_Report</a><br/>
><br/> > ... where T3 is on the replication target.<p dir="ltr">Right. But we don't attempt to replicate locking
letalone SSI state. As I said this is expected. If T1, T2 and T3 run in the master in either READ COMMITTED or
SERIALIZABLEwe will correctly replay whatever got committed and leave the replica in the same state as the master.<p
dir="ltr">Itis row level replication so there is no simple way to detect this anomaly. We would have to send a lot of
co-ordinationdata *in both directions*, right?<p dir="ltr">Sounds like a job for tightly coupled clustering with a GTM,
GLMetc. We're a very very long way from that.<p dir="ltr">The docs probably need to be fairly explicit about the
guaranteesmade and not made and how behavior of queries on replicas can differ from queries on master. The same is true
forphysical replication though, right?<p dir="ltr">Good point that xacts run on replicas can differ in results from the
samequery on master though. At least in read only SERIALIZABLE xacts which are effectively downgraded to REPEATABLE
READ(snapshot) with respect to xacts on master. But continue to get full SSI with respect to other (possibly r/w) xacts
onthe replica. We can't really just disallow SERIALIZABLE isolation xacts because they remain useful. But we might want
tobe able to at least warn if they touch replicated (subscriber) tables.<br />