Re: Transactions involving multiple postgres foreign servers, take 2

On Mon, Jun 15, 2020 at 12:30 PM Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote:
>
> >> Another approach to the atomic visibility problem is to control
> >> snapshot acquisition timing and commit timing (plus using REPEATABLE
> >> READ). In the REPEATABLE READ transaction isolation level, PostgreSQL
> >> assigns a snapshot at the time when the first command is executed in a
> >> transaction. If we could prevent any commit while any transaction is
> >> acquiring snapshot, and we could prevent any snapshot acquisition while
> >> committing, visibility inconsistency which Amit explained can be
> >> avoided.
> >>
> >
> > I think the problem mentioned above can occur with this as well or if
> > I am missing something then can you explain in further detail how it
> > won't create problem in the scenario I have used above?
>
> So the problem you mentioned above is like this? (S1/S2 denotes
> transactions (sessions), N1/N2 is the postgreSQL servers). Since S1
> already committed on N1, S2 sees the row on N1. However S2 does not
> see the row on N2 since S1 has not committed on N2 yet.
>

Yeah, something on these lines but S2 can execute the query on N1
directly which should fetch the data from both N1 and N2. Even if
there is a solution using REPEATABLE READ isolation level we might not
prefer to use that as the only level for distributed transactions, it
might be too costly but let us first see how does it solve the
problem?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com