Re: inherit support for foreign tables

Sorry, here's the script.

On Thu, Dec 4, 2014 at 10:00 AM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

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

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company