Re: inherit support for foreign tables

(2014/12/03 19:35), Ashutosh Bapat wrote:
> On Tue, Dec 2, 2014 at 8:29 AM, Etsuro Fujita
> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp <mailto:fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)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