(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)
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.
Thanks,
Best regards,
Etsuro Fujita
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
