Re: inherit support for foreign tables

On Thu, Dec 4, 2014 at 9:05 AM, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

> (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)
>
>
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