Skip site navigation (1) Skip section navigation (2)

Re: [v9.3] writable foreign tables

From: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Kohei KaiGai *EXTERN*" <kaigai(at)kaigai(dot)gr(dot)jp>
Cc: Atri Sharma <atri(dot)jiit(at)gmail(dot)com>, Alexander Korotkov *EXTERN*<aekorotkov(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas<robertmhaas(at)gmail(dot)com>, PgHacker <pgsql-hackers(at)postgresql(dot)org>, "Shigeru Hanada" <shigeru(dot)hanada(at)gmail(dot)com>
Subject: Re: [v9.3] writable foreign tables
Date: 2012-12-11 15:38:48
Message-ID: A737B7A37273E048B164557ADEF4A58B05788237@ntex2010i.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-hackers
Kohei KaiGai wrote:
>> Weird, that fails for me.

> Both of the troubles you reported was fixed with attached patch.
> I also added relevant test cases into regression test, please check it.

It passes the regression tests, and solves the problems I found.

I came up with one more query that causes a problem:

CREATE TABLE test(
   id integer PRIMARY KEY,
   val text NOT NULL
);

INSERT INTO test(id, val) VALUES (1, 'one');

CREATE FOREIGN TABLE rtest(
   id integer not null,
   val text not null
) SERVER loopback OPTIONS (relname 'test');

/* loopback points to the same database */

WITH ch AS (
   UPDATE test
     SET val='changed'
     RETURNING id
) UPDATE rtest
  SET val='new'
  FROM ch
  WHERE rtest.id = ch.id;

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.

>> I took a brief look at the documentation; that will need some more
>> work.
>
> Yep, I believe it should be revised prior to this patch being committed.

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.

I left that paragraph roughly as it is, please change it if
appropriate.

I also changed the misspelled "resultRelaion" and updated a
few comments.

May I suggest to split the patch in two parts, one for
all the parts that affect postgres_fdw and one for the rest?
That might make the committer's work easier, since
postgres_fdw is not applied (yet).

Yours,
Laurenz Albe

Attachment: pgsql-v9.3-writable-fdw-poc.v7.patch
Description: application/octet-stream (170.1 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Andres FreundDate: 2012-12-11 15:43:12
Subject: Re: Re: [PATCH 02/14] Add support for a generic wal reading facility dubbed XLogReader
Previous:From: Tom LaneDate: 2012-12-11 15:21:47
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group