Re: [v9.3] writable foreign tables

Kohei KaiGai wrote:
>> I came up with one more query that causes a problem:
[...]
>> This causes a deadlock, but one that is not detected;
>> the query just keeps hanging.
>>
>> The UPDATE in the CTE has the rows locked, so the
>> SELECT ... FOR UPDATE issued via the FDW connection will hang
>> indefinitely.
>>
>> I wonder if that's just a pathological corner case that we shouldn't
>> worry about.  Loopback connections for FDWs themselves might not
>> be so rare, for example as a substitute for autonomous subtransactions.
>>
>> I guess it is not easily possible to detect such a situation or
>> to do something reasonable about it.
>
> It is not avoidable problem due to the nature of distributed database system,
> not only loopback scenario.
>
> In my personal opinion, I'd like to wait for someone implements distributed
> lock/transaction manager on top of the background worker framework being
> recently committed, to intermediate lock request.
> However, it will take massive amount of efforts to existing lock/transaction
> management layer, not only enhancement of FDW APIs. It is obviously out
> of scope in this patch.
>
> So, I'd like to suggest authors of FDW that support writable features to put
> mention about possible deadlock scenario in their documentation.
> At least, above writable CTE example is a situation that two different sessions
> concurrently update the "test" relation, thus, one shall be blocked.

Fair enough.

>> I tried to overhaul the documentation, see the attached patch.
>>
>> There was one thing that I was not certain of:
>> You say that for writable foreign tables, BeginForeignModify
>> and EndForeignModify *must* be implemented.
>> I thought that these were optional, and if you can do your work
> with just, say, ExecForeignDelete, you could do that.
>
> Yes, that's right. What I wrote was incorrect.
> If FDW driver does not require any state during modification of
> foreign tables, indeed, these are not mandatory handler.

I have updated the documentation, that was all I changed in the
attached patches.

> OK. I split the patch into two portion, part-1 is the APIs relevant
> patch, part-2 is relevant to postgres_fdw patch.

Great.

I'll mark the patch as "ready for committer".

Yours,
Laurenz Albe