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
