Thread: Logical decoding of sequence advances, part II
Hi all
If the sequence is ALTERed in a way that changes pg_class that's event triggers' job and sequence decoding doesn't care. If it's ALTERed in a way that changes Form_pg_sequence we replay the change immediately, using the last committed snapshot to get the sequence details, so the change will take immediate effect and is retained whether or not any pg_class changes are committed. This reflects how it happens on the upstream.
--
My earlier efforts at logical decoding of sequence advances were too simplistic[1], falling afoul of issues with sequences being both transactional and not transactional depending on whether the sequence is created in the current xact or not.
TL;DR of solution:
* extend SeqTableData and xl_seq_rec with xid
* set xid in SeqTableData during DefineSequence()
* if SeqTableData set, write it in xl_seq_rec when writing xlog
* assign sequence update to specified xid's reorder buffer in decoding if xid set, otherwise decode immediately
The problem:
If the sequence is created in the current xact (i.e. uncommitted) we have to add the sequence updates to that xact to be replayed only if it commits. The sequence is visible only to the toplevel xact that created the sequence so advances of it can only come from that xact and its children. The actual CREATE SEQUENCE is presumed to be handled separately by an event trigger or similar.
If the new sequence is committed we must replay sequence advances immediately and non-transactionally to ensure they're not lost due to xact rollback or replayed in the wrong order due to xact commit order.
If the sequence is ALTERed in a way that changes pg_class that's event triggers' job and sequence decoding doesn't care. If it's ALTERed in a way that changes Form_pg_sequence we replay the change immediately, using the last committed snapshot to get the sequence details, so the change will take immediate effect and is retained whether or not any pg_class changes are committed. This reflects how it happens on the upstream.
Planned solution:
Extend xl_seq_rec with a created_in_xid TransactionId field. If created_in_xid != InvalidTransactionId, logical decoding associates the sequence advance with the given toplevel xact and adds it to the reorder buffer instead of immediately invoking the sequence decoding callback. The decoding callback then gets invoked during ReorderBufferCommit processing at the appropriate time, like any other transactional change.
To determine whether to log an xid for the sequence advance we need some backend local state to determine whether the sequence is new in this xact. Handily we already have one, the seqhashtab of SeqTableData in sequence.c, just where it's needed. So all that's needed is to add a TransactionId field that we set if we created that sequence in this session. If it's set we test it for TransactionIsInProgress() when xlog'ing a sequence advance; if it is, log that xid. If not in progress, clear the xid in SeqTableData entry so we don't check again.
Another approach would be, during decoding, to look up the relfilenode of the sequence to get the sequence oid and do a pg_class lookup. Check to see whether xmin is part of an in-progress xact. If so, add the sequence advance to that xact's reorder buffer, otherwise decode it immediately. The problem is that
(a) I think we lack relfilenode-to-oid mapping information at decoding-time. RelidByRelfilenode() needs a snapshot and is invoked during ReorderBufferCommit(). We have make the transactional vs nontransactional decision in LogicalDecodingProcessRecord() when I'm pretty sure we don't have a snapshot.
(b) It also has issues with ALTER TRANSACTION. We must replay decoded xact updates immediately even if some in-flight xact has modified the pg_class entry for the sequence. So we can't just check whether the xmin is one of our xact's (sub)xids, we must also check whether some older tuple for the same sequence oid has a corresponding xmax and keep walking backwards until we determine whether we originally CREATEd the sequence in this xact or only ALTERed it.
So yeah. I think extending SeqTableData and xl_seq_rec with xid is the way to go. Objections?
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
PostgreSQL Development, 24x7 Support, Training & Services
On 22 August 2016 at 11:13, Craig Ringer <craig@2ndquadrant.com> wrote:
So yeah. I think extending SeqTableData and xl_seq_rec with xid is the way to go. Objections?
CREATE TABLE x(id serial not null);
SELECT nextval('x_id_seq'); => 1
BEGIN;
SELECT nextval('x_id_seq'); => 2
SAVEPOINT sp1;
SELECT nextval('x_id_seq'); => 3
TRUNCATE TABLE x RESTART IDENTITY;
SELECT nextval('x_id_seq'); => 1
ROLLBACK TO SAVEPOINT sp1;
SELECT nextval('x_id_seq'); => 4
sequence.c:init_sequence() detects this by noticing that the relfilenode has changed and discarding cached values, resuming at last_value. Knowledge of whether we created the sequence relfilenode is not retained so we can't do something similar.
Unless anyone has any clever (or obvious but not to me) solutions to this, I'll probably need to maintain a separate map of sequence relfilenodes we created and which xid we created them in, so we can test whether that xid is still in progress when logging a change. It's still pretty much free when wal_level < logical or the current xact hasn't created any sequences.
Otherwise I could store a List of xids in the SeqTableData for the sequence and check that for in-progress xids. It'd usually be NIL. If not, it'll almost always be a 1-item List, the creating / resetting xid. If subxacts are involved it'll become a stack. We walk down the stack checking whether xacts are in progress and popping them if not until we find an in-progress entry or run out of stack and set it to NIL.
Either will produce the same desired result: the correct subxact xid for the innermost in-progress xact that created or reset this sequence, if any.
(I initially planned to just punt on TRUNCATE and let event triggers handle it, but the need to roll back sequence advances if a TRUNCATE ... RESTART IDENTITY is rolled back means sequence decoding must pay attention to it).
I'm also having trouble working out how to get a historical snapshot for the most recent committed xact in a decoding session so the sequence's name can be looked up by oid in the relcache during decoding. Advice would be welcome if anyone can spare a moment.
I'll keep working on this concurrent with some higher priority work. Suggestions, advice, or screams of horror welcomed. I think we really, really need logical decoding of sequence advances...
On Sun, Aug 21, 2016 at 11:13 PM, Craig Ringer <craig@2ndquadrant.com> wrote: > If the sequence is created in the current xact (i.e. uncommitted) we have to > add the sequence updates to that xact to be replayed only if it commits. The > sequence is visible only to the toplevel xact that created the sequence so > advances of it can only come from that xact and its children. The actual > CREATE SEQUENCE is presumed to be handled separately by an event trigger or > similar. > > If the new sequence is committed we must replay sequence advances > immediately and non-transactionally to ensure they're not lost due to xact > rollback or replayed in the wrong order due to xact commit order. So, I wish I could give you some better advice on this topic, but sadly I am not an expert in this area. However, it seems to me that this is just one facet of a much more general problem: given two transactions T1 and T2, the order of replay must match the order of commit unless you can prove that there are no dependencies between them. I don't see why it matters whether the operations are sequence operations or data operations; it's just a question of whether they're modifying the same "stuff". Of course, it's possible I'm missing something important here... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2016-08-22 16:29:12 -0400, Robert Haas wrote: > So, I wish I could give you some better advice on this topic, but > sadly I am not an expert in this area. However, it seems to me that > this is just one facet of a much more general problem: given two > transactions T1 and T2, the order of replay must match the order of > commit unless you can prove that there are no dependencies between > them. I don't see why it matters whether the operations are sequence > operations or data operations; it's just a question of whether they're > modifying the same "stuff". > > Of course, it's possible I'm missing something important here... Maybe that normally logical decoding outputs stuff in commit order? Andres
On Mon, Aug 22, 2016 at 3:29 PM, Robert Haas <robertmhaas@gmail.com> wrote: > it seems to me that > this is just one facet of a much more general problem: given two > transactions T1 and T2, the order of replay must match the order of > commit unless you can prove that there are no dependencies between > them. I don't see why it matters whether the operations are sequence > operations or data operations; it's just a question of whether they're > modifying the same "stuff". The commit order is the simplest and safest *unless* there is a read-write anti-dependency a/k/a read-write dependency a/k/a rw-conflict: where a read from one transaction sees the "before" version of data modified by the other transaction. In such a case it is necessary for correct serializable transaction behavior for the transaction that read the "before" image to be replayed before the write it didn't see, regardless of commit order. If you're not trying to avoid serialization anomalies, it is less clear to me what is best. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
<p dir="ltr"><p dir="ltr">On 23 Aug 2016 05:43, "Kevin Grittner" <<a href="mailto:kgrittn@gmail.com">kgrittn@gmail.com</a>>wrote:<br /> ><br /> > On Mon, Aug 22, 2016 at 3:29 PM, RobertHaas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>> wrote:<br /> ><br /> > > itseems to me that<br /> > > this is just one facet of a much more general problem: given two<br /> > > transactionsT1 and T2, the order of replay must match the order of<br /> > > commit unless you can prove that thereare no dependencies between<br /> > > them. I don't see why it matters whether the operations are sequence<br/> > > operations or data operations; it's just a question of whether they're<br /> > > modifyingthe same "stuff".<p dir="ltr">It matters because sequence operations aren't transactional in pg. Except when theyare - operations on a newly CREATEd sequence or one where we did a TRUNCATE ...RESTART IDENTITY. <p dir="ltr">But wedon't store the xid of the xact associated with a transactional sequence update along with the sequence update anywhere.We just rely on nk other xact knowing to look at the sequence relfilenode we're changing. Doesn't work so well inlogical rep.<p dir="ltr">We also don't store knowledge of whether or not the sequence advance is transactional. Again importantbecause for two xacts t1 and t2:<p dir="ltr">* Sequence last value is 50<p dir="ltr">* T1 calls nextval. Needs anew chunk because all cached values have been used. Writes sequence wal advancing seq last_value to 100, returns 51.<p dir="ltr">*T2 calls nextval, gets cached value 52.<p dir="ltr">* T2 commits<p dir="ltr">* Master crashes and we fail overto replica.<p dir="ltr">This is fine for physical rep. We replay the sequence advance and all is well.<p dir="ltr">Butfor logical rep the sequence can't be treated as part of t1. If t1 rolls back or we fail over before replyingit we might return value 52 from nextval even though we replayed and committed t2 that used value 52. Oops.<p dir="ltr">Howeverif some xact t3 creates a sequence we can't replay updates to it until the sequence relation is committed.And it's even more fun with TRUNCATE ... RESTART IDENTITY where we need rollback behaviour too.<p dir="ltr">Makesense? It's hard because sequences are sometimes but not always exrmpt from transactional behaviour and pgdoesn't record when, since it can rely on physical wal redo order and can apply sequence advances before the sequence relationis committed yet.<p dir="ltr">><br /> > The commit order is the simplest and safest *unless* there is a<br/> > read-write anti-dependency a/k/a read-write dependency a/k/a<br /> > rw-conflict: where a read from one transactionsees the "before"<br /> > version of data modified by the other transaction. In such a case<br /> > itis necessary for correct serializable transaction behavior for<br /> > the transaction that read the "before" imageto be replayed before<br /> > the write it didn't see, regardless of commit order. If you're not<br /> > tryingto avoid serialization anomalies, it is less clear to me<br /> > what is best.<p dir="ltr">Could you provide anexample of a case where xacts replayed in commit order will produce incorrect results? <p dir="ltr">Remember that we aren'tdoing statement based replication in pg logical decoding/replication. We don't care how a row got changed, only thatwe make consistent transitions from before state to after state to for each transaction, such that the data committedand visible on the master is visible on the standby and no uncommitted or not yet visible data on the master iscommitted/visible on the replica. The replica should have visible committed data matching the master as it was when itoriginally executed the xact we most recently replayed. <p dir="ltr">No locking is decoded or replayed. It is not expectedthat a normal non replication client executing some other concurrent xact will have the same effect if run on standbyas on master.<p dir="ltr">It's replication not tightly coupled clustering. If/when we have things like parallel decodingand replay of concurrent xacts then issues like the dependencies you mention will start to become a concern. Weare a long way from there.<p dir="ltr">For sequences the requirement IMO is that the sequence advances on the replica toor past the position it was at on the master when the first xact that saw those sequence values committed. We should neversee the sequence 'behind' such that calling nextval on the replica can produce a value already seen and stored by somecommitted xact on the replica. Being a bit ahead is ok, much like pg discards sequence values on crash.<p dir="ltr">That'snot that hard. The problems arise when the sequence it's self isn't committed yet, per above.<br />
On Mon, Aug 22, 2016 at 6:39 PM, Craig Ringer <craig@2ndquadrant.com> wrote: > On 23 Aug 2016 05:43, "Kevin Grittner" <kgrittn@gmail.com> wrote: >> On Mon, Aug 22, 2016 at 3:29 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> >>> it seems to me that >>> this is just one facet of a much more general problem: given two >>> transactions T1 and T2, the order of replay must match the order of >>> commit unless you can prove that there are no dependencies between >>> them. I don't see why it matters whether the operations are sequence >>> operations or data operations; it's just a question of whether they're >>> modifying the same "stuff". >> The commit order is the simplest and safest *unless* there is a >> read-write anti-dependency a/k/a read-write dependency a/k/a >> rw-conflict: where a read from one transaction sees the "before" >> version of data modified by the other transaction. In such a case >> it is necessary for correct serializable transaction behavior for >> the transaction that read the "before" image to be replayed before >> the write it didn't see, regardless of commit order. If you're not >> trying to avoid serialization anomalies, it is less clear to me >> what is best. > > Could you provide an example of a case where xacts replayed in > commit order will produce incorrect results? https://wiki.postgresql.org/wiki/SSI#Deposit_Report ... where T3 is on the replication target. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Aug 23, 2016 at 7:10 AM, Kevin Grittner <kgrittn@gmail.com> wrote: > On Mon, Aug 22, 2016 at 6:39 PM, Craig Ringer <craig@2ndquadrant.com> wrote: >> Could you provide an example of a case where xacts replayed in >> commit order will produce incorrect results? > > https://wiki.postgresql.org/wiki/SSI#Deposit_Report > > ... where T3 is on the replication target. I should, perhaps, have mentioned that the cases where this is are problem are "eventually consistent" -- it's a matter of being able to see a state that violates business rule invariants or where data which is "locked down" according to one part of the database is still changing. Such problems are prevented on a single database, but would not be prevented on a logical replica where transactions are applied in commit order. Given enough time, the replica would eventually settle into a state without the anomalies, similar to some other products with eventual consistency. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
<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 />
On Mon, Aug 22, 2016 at 4:32 PM, Andres Freund <andres@anarazel.de> wrote: > On 2016-08-22 16:29:12 -0400, Robert Haas wrote: >> So, I wish I could give you some better advice on this topic, but >> sadly I am not an expert in this area. However, it seems to me that >> this is just one facet of a much more general problem: given two >> transactions T1 and T2, the order of replay must match the order of >> commit unless you can prove that there are no dependencies between >> them. I don't see why it matters whether the operations are sequence >> operations or data operations; it's just a question of whether they're >> modifying the same "stuff". >> >> Of course, it's possible I'm missing something important here... > > Maybe that normally logical decoding outputs stuff in commit order? As slow as I sometimes am, I did know that. :-) I think what I was missing is that nextval() operations are non-transactional. Craig describes them as non-transactional except when they are transactional, but I think that's not really the right way of looking at it. Creating the sequence is transactional, and updating the value is not. What seems to be causing trouble for Craig is that if the nextval() operation is inserted into the replication stream non-transactionally, it can happen before the sequence actually gets created. I'm wondering if we can't find a way to make it so that it's OK for those operations to happen out of order, rather than trying to make the nextval() operation sometimes transactional and other times non-transactional. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Aug 23, 2016 at 7:40 AM, Craig Ringer <craig@2ndquadrant.com> wrote: > On 23 Aug 2016 20:10, "Kevin Grittner" <kgrittn@gmail.com> wrote: >> >> On Mon, Aug 22, 2016 at 6:39 PM, Craig Ringer <craig@2ndquadrant.com> >>> Could you provide an example of a case where xacts replayed in >>> commit order will produce incorrect results? >> >> https://wiki.postgresql.org/wiki/SSI#Deposit_Report >> >> ... where T3 is on the replication target. > > Right. But we don't attempt to replicate locking let alone SSI state. As I > said this is expected. If T1, T2 and T3 run in the master in either READ > COMMITTED or SERIALIZABLE we will correctly replay whatever got committed > and leave the replica in the same state as the master. Eventually. Between the commit of T3 and T2 a state can be seen on the replica which would not have been allowed on the source. > It is row level replication so there is no simple way to detect this > anomaly. That is probably true, but there is a way to *prevent* it. > We would have to send a lot of co-ordination data *in both > directions*, right? No. The source has all the information about both commit order and read-write dependencies, and could produce a stream of transaction IDs to specify the safe order for applying transactions to prevent the anomaly from appearing on the replica. In this case the commit order is T1->T3->T2, but the apparent order of execution (AOoE) is T1->T2->T3. If the source communicated that to the replica, and the replica held up application of any changes from T3 until T2 was committed there would be no chance to read incorrect results. It would not matter if T2 and T3 were committed on the replica simultaneously or in AOoE, as long as the work of T3 does not appear before the work of T2. The replica does not need to send anything back to the source for this to work. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Aug 23, 2016 at 9:07 AM, Kevin Grittner <kgrittn@gmail.com> wrote: > On Tue, Aug 23, 2016 at 7:40 AM, Craig Ringer <craig@2ndquadrant.com> wrote: >> On 23 Aug 2016 20:10, "Kevin Grittner" <kgrittn@gmail.com> wrote: >>> >>> On Mon, Aug 22, 2016 at 6:39 PM, Craig Ringer <craig@2ndquadrant.com> > >>>> Could you provide an example of a case where xacts replayed in >>>> commit order will produce incorrect results? >>> >>> https://wiki.postgresql.org/wiki/SSI#Deposit_Report >>> >>> ... where T3 is on the replication target. >> >> Right. But we don't attempt to replicate locking let alone SSI state. As I >> said this is expected. If T1, T2 and T3 run in the master in either READ >> COMMITTED or SERIALIZABLE we will correctly replay whatever got committed >> and leave the replica in the same state as the master. > > Eventually. Between the commit of T3 and T2 a state can be seen on > the replica which would not have been allowed on the source. > >> It is row level replication so there is no simple way to detect this >> anomaly. > > That is probably true, but there is a way to *prevent* it. > >> We would have to send a lot of co-ordination data *in both >> directions*, right? > > No. The source has all the information about both commit order and > read-write dependencies, and could produce a stream of transaction > IDs to specify the safe order for applying transactions to prevent > the anomaly from appearing on the replica. In this case the commit > order is T1->T3->T2, but the apparent order of execution (AOoE) is > T1->T2->T3. Sorry, trying to keep this conversation going while doing something else and sent a response there which doesn't really make sense, since the issue is whether to allow T3 to run *on the replica*. I'll take another look when I'm less distracted. You may be right. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 23/08/16 15:59, Robert Haas wrote: > On Mon, Aug 22, 2016 at 4:32 PM, Andres Freund <andres@anarazel.de> wrote: >> On 2016-08-22 16:29:12 -0400, Robert Haas wrote: >>> So, I wish I could give you some better advice on this topic, but >>> sadly I am not an expert in this area. However, it seems to me that >>> this is just one facet of a much more general problem: given two >>> transactions T1 and T2, the order of replay must match the order of >>> commit unless you can prove that there are no dependencies between >>> them. I don't see why it matters whether the operations are sequence >>> operations or data operations; it's just a question of whether they're >>> modifying the same "stuff". >>> >>> Of course, it's possible I'm missing something important here... >> >> Maybe that normally logical decoding outputs stuff in commit order? > > As slow as I sometimes am, I did know that. :-) > > I think what I was missing is that nextval() operations are > non-transactional. Craig describes them as non-transactional except > when they are transactional, but I think that's not really the right > way of looking at it. Creating the sequence is transactional, and > updating the value is not. What seems to be causing trouble for Craig > is that if the nextval() operation is inserted into the replication > stream non-transactionally, it can happen before the sequence actually > gets created. I'm wondering if we can't find a way to make it so that > it's OK for those operations to happen out of order, rather than > trying to make the nextval() operation sometimes transactional and > other times non-transactional. > Well, that's what Craig is trying to do by tracking if the transactional change has happend on a sequence in current transaction, no? -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Aug 23, 2016 at 9:36 AM, Kevin Grittner <kgrittn@gmail.com> wrote: > On Tue, Aug 23, 2016 at 9:07 AM, Kevin Grittner <kgrittn@gmail.com> wrote: >> On Tue, Aug 23, 2016 at 7:40 AM, Craig Ringer <craig@2ndquadrant.com> wrote: >>> On 23 Aug 2016 20:10, "Kevin Grittner" <kgrittn@gmail.com> wrote: >>>> >>>> On Mon, Aug 22, 2016 at 6:39 PM, Craig Ringer <craig@2ndquadrant.com> >> >>>>> Could you provide an example of a case where xacts replayed in >>>>> commit order will produce incorrect results? >>>> >>>> https://wiki.postgresql.org/wiki/SSI#Deposit_Report >>>> >>>> ... where T3 is on the replication target. >>> >>> Right. But we don't attempt to replicate locking let alone SSI state. As I >>> said this is expected. If T1, T2 and T3 run in the master in either READ >>> COMMITTED or SERIALIZABLE we will correctly replay whatever got committed >>> and leave the replica in the same state as the master. >> >> Eventually. Between the commit of T3 and T2 a state can be seen on >> the replica which would not have been allowed on the source. >> >>> It is row level replication so there is no simple way to detect this >>> anomaly. >> >> That is probably true, but there is a way to *prevent* it. >> >>> We would have to send a lot of co-ordination data *in both >>> directions*, right? >> >> No. The source has all the information about both commit order and >> read-write dependencies, and could produce a stream of transaction >> IDs to specify the safe order for applying transactions to prevent >> the anomaly from appearing on the replica. In this case the commit >> order is T1->T3->T2, but the apparent order of execution (AOoE) is >> T1->T2->T3. > > Sorry, trying to keep this conversation going while doing something > else and sent a response there which doesn't really make sense, > since the issue is whether to allow T3 to run *on the replica*. > I'll take another look when I'm less distracted. You may be right. I had the right idea, but messed up the details. The source has commit order T2->T1 and AOoE T1->T2. So as long as making any work from T2 visible is held up until at least the point where the work of T1 is visible, T3 on the replica cannot see an anomalous state. It is still true that a one-way stream of information from the primary to the replica regarding AOoE would be sufficient communication. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 24 August 2016 at 00:26, Kevin Grittner <kgrittn@gmail.com> wrote:
I had the right idea, but messed up the details. The source hasOn Tue, Aug 23, 2016 at 9:36 AM, Kevin Grittner <kgrittn@gmail.com> wrote:
> On Tue, Aug 23, 2016 at 9:07 AM, Kevin Grittner <kgrittn@gmail.com> wrote:
>> On Tue, Aug 23, 2016 at 7:40 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
>>> On 23 Aug 2016 20:10, "Kevin Grittner" <kgrittn@gmail.com> wrote:
>>>>
>>>> On Mon, Aug 22, 2016 at 6:39 PM, Craig Ringer <craig@2ndquadrant.com>
>>
>>>>> Could you provide an example of a case where xacts replayed in
>>>>> commit order will produce incorrect results?
>>>>
>>>> https://wiki.postgresql.org/wiki/SSI#Deposit_Report
>>>>
>>>> ... where T3 is on the replication target.
>>>
>>> Right. But we don't attempt to replicate locking let alone SSI state. As I
>>> said this is expected. If T1, T2 and T3 run in the master in either READ
>>> COMMITTED or SERIALIZABLE we will correctly replay whatever got committed
>>> and leave the replica in the same state as the master.
>>
>> Eventually. Between the commit of T3 and T2 a state can be seen on
>> the replica which would not have been allowed on the source.
>>
>>> It is row level replication so there is no simple way to detect this
>>> anomaly.
>>
>> That is probably true, but there is a way to *prevent* it.
>>
>>> We would have to send a lot of co-ordination data *in both
>>> directions*, right?
>>
>> No. The source has all the information about both commit order and
>> read-write dependencies, and could produce a stream of transaction
>> IDs to specify the safe order for applying transactions to prevent
>> the anomaly from appearing on the replica. In this case the commit
>> order is T1->T3->T2, but the apparent order of execution (AOoE) is
>> T1->T2->T3.
>
> Sorry, trying to keep this conversation going while doing something
> else and sent a response there which doesn't really make sense,
> since the issue is whether to allow T3 to run *on the replica*.
> I'll take another look when I'm less distracted. You may be right.
commit order T2->T1 and AOoE T1->T2. So as long as making any work
from T2 visible is held up until at least the point where the work
of T1 is visible, T3 on the replica cannot see an anomalous state.
It is still true that a one-way stream of information from the
primary to the replica regarding AOoE would be sufficient
communication.
Hm. That's really interesting. So we could achieve SSI-like properties for read-only xacts on a replica if we could defer xact decoding, or buffer the xact on the downstream and defer apply, based on SSI info from the upstream. That'd be pretty cool.
On 2016-08-23 07:26:31 -0500, Kevin Grittner wrote: > On Tue, Aug 23, 2016 at 7:10 AM, Kevin Grittner <kgrittn@gmail.com> wrote: > > On Mon, Aug 22, 2016 at 6:39 PM, Craig Ringer <craig@2ndquadrant.com> wrote: > > >> Could you provide an example of a case where xacts replayed in > >> commit order will produce incorrect results? > > > > https://wiki.postgresql.org/wiki/SSI#Deposit_Report > > > > ... where T3 is on the replication target. > > I should, perhaps, have mentioned that the cases where this is are > problem are "eventually consistent" -- it's a matter of being able > to see a state that violates business rule invariants or where data > which is "locked down" according to one part of the database is > still changing. Such problems are prevented on a single database, > but would not be prevented on a logical replica where transactions > are applied in commit order. Given enough time, the replica would > eventually settle into a state without the anomalies, similar to > some other products with eventual consistency. I've generally a bit of difficulty to see this as a significant problem for logical rep, as long as hot-standby, and crash-recovery in general, also has this problem...
On 24 August 2016 at 00:50, Andres Freund <andres@anarazel.de> wrote:
On 2016-08-23 07:26:31 -0500, Kevin Grittner wrote:
> On Tue, Aug 23, 2016 at 7:10 AM, Kevin Grittner <kgrittn@gmail.com> wrote:
> [an explanation of SSI anomalies]
I've generally a bit of difficulty to see this as a significant problem
for logical rep, as long as hot-standby, and crash-recovery in general,
also has this problem...
Same here, as commented upthread. I think it'd be cool to be able to deliver SSI-alike behaviour on a standby, but it's far from a priority, and it applies to phys ans well as logical rep.
On Tue, Aug 23, 2016 at 11:50 AM, Andres Freund <andres@anarazel.de> wrote: > On 2016-08-23 07:26:31 -0500, Kevin Grittner wrote: >> On Tue, Aug 23, 2016 at 7:10 AM, Kevin Grittner <kgrittn@gmail.com> wrote: >> > On Mon, Aug 22, 2016 at 6:39 PM, Craig Ringer <craig@2ndquadrant.com> wrote: >> >> >> Could you provide an example of a case where xacts replayed in >> >> commit order will produce incorrect results? >> > >> > https://wiki.postgresql.org/wiki/SSI#Deposit_Report >> > >> > ... where T3 is on the replication target. >> >> I should, perhaps, have mentioned that the cases where this is are >> problem are "eventually consistent" -- it's a matter of being able >> to see a state that violates business rule invariants or where data >> which is "locked down" according to one part of the database is >> still changing. Such problems are prevented on a single database, >> but would not be prevented on a logical replica where transactions >> are applied in commit order. Given enough time, the replica would >> eventually settle into a state without the anomalies, similar to >> some other products with eventual consistency. > > I've generally a bit of difficulty to see this as a significant problem > for logical rep, as long as hot-standby, and crash-recovery in general, > also has this problem... Serialization anomalies cannot be seen on a hot standby nor on crash recovery. Granted, the mechanism which prevents it on the hot standby is that we don't allow the transaction isolation level to be set to SERIALIZABLE, to prevent the expectation that queries will be free of anomalies, which is pretty crude. On crash recovery you cannot see any anomalies as far as I'm aware -- what has given you the idea that it is possible? I'm not sure that the same technique I described for logical replication could be made to work for page-level physical replication, but a "safe snapshot" technique has been previously discussed on the lists which would work for physical replication (although *that* technique seems unsuited to logical replication). -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company