Re: inherit support for foreign tables - Mailing list pgsql-hackers

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

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

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