From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>
Cc: Noah Misch <noah@leadboat.com>
Cc: Shigeru Hanada <shigeru.hanada@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: inherit support for foreign tables
Date: Thu, 04 Dec 2014 12:35:54 +0900
Message-ID: <547FD69A.8090308@lab.ntt.co.jp> (raw)
In-Reply-To: <CAFjFpRcYN5pMphZ=ATPh9Ya6X4h+fX0LVv1nG9fJZKpL2kopeA@mail.gmail.com>
References: <543D0261.6020803@lab.ntt.co.jp>
<54461BEB.9000707@lab.ntt.co.jp>
<5449EE98.1040704@lab.ntt.co.jp>
<20141107.145713.169337282.horiguchi.kyotaro@lab.ntt.co.jp>
<545CB4A2.1030303@lab.ntt.co.jp>
<CAFjFpRcVLtU6jE8h_N1Rc+Y-pDFbmR7YvBvxOXg6aTFrbZ1Z8Q@mail.gmail.com>
<54645499.7050709@lab.ntt.co.jp>
<CAFjFpRcBFVO0YP0nmfygpJOFfWKMkAN1Z8JmrkwyEWXDyMt5Hw@mail.gmail.com>
<CAFjFpRcd=F8Jqx+9yioHKVUA71LXBxO41ia61acjzyzQ4MZqVQ@mail.gmail.com>
<5476FB4B.9060705@lab.ntt.co.jp>
<CAFjFpRd6PR3Seg8D3t-XcXGxXyb+p2WDsk_wBGge7b=PyzP_Qw@mail.gmail.com>
<547D2B1D.1080107@lab.ntt.co.jp>
<CAFjFpRcYN5pMphZ=ATPh9Ya6X4h+fX0LVv1nG9fJZKpL2kopeA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
(2014/12/03 19:35), Ashutosh Bapat wrote:
> On Tue, Dec 2, 2014 at 8:29 AM, Etsuro Fujita
> <fujita.etsuro@lab.ntt.co.jp <mailto:fujita.etsuro@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: fujita.etsuro@lab.ntt.co.jp, ashutosh.bapat@enterprisedb.com, horiguchi.kyotaro@lab.ntt.co.jp, hlinnakangas@vmware.com, noah@leadboat.com, shigeru.hanada@gmail.com
Subject: Re: inherit support for foreign tables
In-Reply-To: <547FD69A.8090308@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox