Sorry, here's the script. On Thu, Dec 4, 2014 at 10:00 AM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote:
> > > On Thu, Dec 4, 2014 at 9:05 AM, Etsuro Fujita <fujita.ets...@lab.ntt.co.jp > > wrote: > >> (2014/12/03 19:35), Ashutosh Bapat wrote: >> >>> On Tue, Dec 2, 2014 at 8:29 AM, Etsuro Fujita >>> <fujita.ets...@lab.ntt.co.jp <mailto:fujita.ets...@lab.ntt.co.jp>> >>> wrote: >>> >> >> This is not exactly extension of non-inheritance case. non-inheritance >>> case doesn't show two remote SQLs under the same plan node. May be you >>> can rename the label Remote SQL as Remote UPDATE/INSERT/DELETE (or >>> something to that effect) for the DML command and the Foreign plan node >>> should be renamed to Foreign access node or something to indicate that >>> it does both the scan as well as DML. I am not keen about the actual >>> terminology, but I think a reader of plan shouldn't get confused. >>> >>> We can leave this for committer's judgement. >>> >> >> Thanks for the proposal! I think that would be a good idea. But I think >> there would be another idea. An example will be shown below. We show the >> update commands below the ModifyTable node, not above the corresponding >> ForeignScan nodes, so maybe less confusing. If there are no objections of >> you and others, I'll update the patch this way. >> >> postgres=# explain verbose update parent set a = a * 2 where a = 5; >> QUERY PLAN >> ------------------------------------------------------------ >> ------------------------- >> Update on public.parent (cost=0.00..280.77 rows=25 width=10) >> On public.ft1 >> Remote SQL: UPDATE public.mytable_1 SET a = $2 WHERE ctid = $1 >> On public.ft2 >> Remote SQL: UPDATE public.mytable_2 SET a = $2 WHERE ctid = $1 >> -> Seq Scan on public.parent (cost=0.00..0.00 rows=1 width=10) >> Output: (parent.a * 2), parent.ctid >> Filter: (parent.a = 5) >> -> Foreign Scan on public.ft1 (cost=100.00..140.38 rows=12 width=10) >> Output: (ft1.a * 2), ft1.ctid >> Remote SQL: SELECT a, ctid FROM public.mytable_1 WHERE ((a = 5)) >> FOR UPDATE >> -> Foreign Scan on public.ft2 (cost=100.00..140.38 rows=12 width=10) >> Output: (ft2.a * 2), ft2.ctid >> Remote SQL: SELECT a, ctid FROM public.mytable_2 WHERE ((a = 5)) >> FOR UPDATE >> (12 rows) >> >> > Looks better. > > >> IIUC, even the transactions over the local and the *single* remote >>> server are not guaranteed to be executed atomically in the current >>> form. It is possible that the remote transaction succeeds and the >>> local one fails, for example, resulting in data inconsistency >>> between the local and the remote. >>> >> >> IIUC, while committing transactions involving a single remote server, >>> the steps taken are as follows >>> 1. the local changes are brought to PRE-COMMIT stage, which means that >>> the transaction *will* succeed locally after successful completion of >>> this phase, >>> 2. COMMIT message is sent to the foreign server >>> 3. If step two succeeds, local changes are committed and successful >>> commit is conveyed to the client >>> 4. if step two fails, local changes are rolled back and abort status is >>> conveyed to the client >>> 5. If step 1 itself fails, the remote changes are rolled back. >>> This is as per one phase commit protocol which guarantees ACID for >>> single foreign data source. So, the changes involving local and a single >>> foreign server seem to be atomic and consistent. >>> >> >> Really? Maybe I'm missing something, but I don't think the current >> implementation for committing transactions has such a mechanism stated in >> step 1. So, I think it's possible that the local transaction fails in >> step3 while the remote transaction succeeds, as mentioned above. >> >> > PFA a script attached which shows this. You may want to check the code in > pgfdw_xact_callback() for actions taken by postgres_fdw on various events. > CommitTransaction() for how those events are generated. The code there > complies with the sequence above. > > >> >> Thanks, >> >> Best regards, >> Etsuro Fujita >> > > > > -- > Best Wishes, > Ashutosh Bapat > EnterpriseDB Corporation > The Postgres Database Company > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
tran_inconsistency.sql
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
