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: Tue, 02 Dec 2014 11:59:41 +0900
Message-ID: <547D2B1D.1080107@lab.ntt.co.jp> (raw)
In-Reply-To: <CAFjFpRd6PR3Seg8D3t-XcXGxXyb+p2WDsk_wBGge7b=PyzP_Qw@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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
(2014/11/28 18:14), Ashutosh Bapat wrote:
> On Thu, Nov 27, 2014 at 3:52 PM, Etsuro Fujita
> <fujita.etsuro@lab.ntt.co.jp <mailto:fujita.etsuro@lab.ntt.co.jp>> wrote:
> Apart from the above, I noticed that the patch doesn't consider to
> call ExplainForeignModify during EXPLAIN for an inherited
> UPDATE/DELETE, as shown below (note that there are no UPDATE remote
> queries displayed):
> So, I'd like to modify explain.c to show those queries like this:
> 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)
> -> Seq Scan on public.parent (cost=0.00..0.00 rows=1 width=10)
> Output: (parent.a * 2), parent.ctid
> Filter: (parent.a = 5)
> Remote SQL: UPDATE public.mytable_1 SET a = $2 WHERE ctid = $1
> -> 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
> Remote SQL: UPDATE public.mytable_2 SET a = $2 WHERE ctid = $1
> -> 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)
> Two "remote SQL" under a single node would be confusing. Also the node
> is labelled as "Foreign Scan". It would be confusing to show an "UPDATE"
> command under this "scan" node.
I thought this as an extention of the existing (ie, non-inherited) case
(see the below example) to the inherited case.
postgres=# explain verbose update ft1 set a = a * 2 where a = 5;
QUERY PLAN
-------------------------------------------------------------------------------------
Update on public.ft1 (cost=100.00..140.38 rows=12 width=10)
Remote SQL: UPDATE public.mytable_1 SET a = $2 WHERE ctid = $1
-> Foreign Scan on public.ft1 (cost=100.00..140.38 rows=12 width=10)
Output: (a * 2), ctid
Remote SQL: SELECT a, ctid FROM public.mytable_1 WHERE ((a =
5)) FOR UPDATE
(5 rows)
I think we should show update commands somewhere for the inherited case
as for the non-inherited case. Alternatives to this are welcome.
> BTW, I was experimenting with DMLs being executed on multiple FDW server
> under same transaction and found that the transactions may not be atomic
> (and may be inconsistent), if one or more of the server fails to commit
> while rest of them commit the transaction. The reason for this is, we do
> not "rollback" the already "committed" changes to the foreign server, if
> one or more of them fail to "commit" a transaction. With foreign tables
> under inheritance hierarchy a single DML can span across multiple
> servers and the result may not be atomic (and may be inconsistent). So,
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.
> either we have to disable DMLs on an inheritance hierarchy which spans
> multiple servers. OR make sure that such transactions follow 2PC norms.
-1 for disabling update queries on such an inheritance hierarchy because
I think we should leave that to the user's judgment. And I think 2PC is
definitely a separate patch.
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: <547D2B1D.1080107@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