Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YhyQ0-0006Kr-3X for pgsql-hackers@arkaria.postgresql.org; Tue, 14 Apr 2015 10:50:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YhyPz-00079n-Ha for pgsql-hackers@arkaria.postgresql.org; Tue, 14 Apr 2015 10:50:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YhyPx-00076y-3V for pgsql-hackers@postgresql.org; Tue, 14 Apr 2015 10:50:09 +0000 Received: from tama50.ecl.ntt.co.jp ([129.60.39.147]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YhyPn-0004Mu-60 for pgsql-hackers@postgresql.org; Tue, 14 Apr 2015 10:50:07 +0000 Received: from vc2.ecl.ntt.co.jp (vc2.ecl.ntt.co.jp [129.60.86.154]) by tama50.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id t3EAnqgb018337; Tue, 14 Apr 2015 19:49:52 +0900 Received: from vc2.ecl.ntt.co.jp (localhost [127.0.0.1]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 307BE5F611; Tue, 14 Apr 2015 19:49:52 +0900 (JST) Received: from imail3.m.ecl.ntt.co.jp (imail3.m.ecl.ntt.co.jp [129.60.5.248]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 1B4855F58E; Tue, 14 Apr 2015 19:49:52 +0900 (JST) Received: from [129.60.241.75] ([129.60.241.75]) by imail3.m.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id t3EAnpo3027206; Tue, 14 Apr 2015 19:49:51 +0900 Message-ID: <552CF0B6.8010006@lab.ntt.co.jp> Date: Tue, 14 Apr 2015 19:49:26 +0900 From: Etsuro Fujita User-Agent: Mozilla/5.0 (Windows NT 6.0; rv:31.0) Gecko/20100101 Thunderbird/31.6.0 MIME-Version: 1.0 To: Tom Lane CC: Ashutosh Bapat , Kyotaro HORIGUCHI , Heikki Linnakangas , Noah Misch , Shigeru Hanada , pgsql-hackers Subject: Re: inherit support for foreign tables References: <543D0261.6020803@lab.ntt.co.jp> <54461BEB.9000707@lab.ntt.co.jp> <5449EE98.1040704@lab.ntt.co.jp> <20141107.145713.169337282.horiguchi.kyotaro@lab.ntt.co.jp> <545CB4A2.1030303@lab.ntt.co.jp> <54645499.7050709@lab.ntt.co.jp> <5476FB4B.9060705@lab.ntt.co.jp> <5487BB9D.7070605@lab.ntt.co.jp> <30555.1418853840@sss.pgh.pa.us> <5497FE79.7090804@lab.ntt.co.jp> <19367.1419262577@sss.pgh.pa.us> <54B76DD2.1040106@lab.ntt.co.jp> <54E6DB4E.40801@lab.ntt.co.jp> <21104.1427047056@sss.pgh.pa.us> In-Reply-To: <21104.1427047056@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------030601000604060104060309" X-TM-AS-MML: disable X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------030601000604060104060309 Content-Type: text/plain; charset=iso-2022-jp Content-Transfer-Encoding: 7bit On 2015/03/23 2:57, Tom Lane wrote: > Etsuro Fujita writes: >> [ fdw-inh-8.patch ] > > I've committed this with some substantial rearrangements, notably: > > * I thought that if we were doing this at all, we should go all the way > and allow foreign tables to be both inheritance parents and children. I found that when setting a foreign table to be the parent of an inheritance set that only contains foreign tables, SELECT FOR UPDATE on the inheritance parent fails with a can't-happen error condition. Here is an example: $ createdb mydb $ psql mydb psql (9.5devel) Type "help" for help. mydb=# create table t1 (c1 int); CREATE TABLE mydb=# create table t2 (c1 int); CREATE TABLE $ psql postgres psql (9.5devel) Type "help" for help. postgres=# create extension postgres_fdw; CREATE EXTENSION postgres=# create server myserver foreign data wrapper postgres_fdw options (dbname 'mydb'); CREATE SERVER postgres=# create user mapping for current_user server myserver; CREATE USER MAPPING postgres=# create foreign table ft1 (c1 int) server myserver options (table_name 't1'); CREATE FOREIGN TABLE postgres=# create foreign table ft2 (c1 int) server myserver options (table_name 't2'); CREATE FOREIGN TABLE postgres=# alter foreign table ft2 inherit ft1; ALTER FOREIGN TABLE postgres=# select * from ft1 for update; ERROR: could not find junk tableoid1 column I think this is a bug. Attached is a patch fixing this issue. Best regards, Etsuro Fujita --------------030601000604060104060309 Content-Type: text/x-diff; name="preptlist.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="preptlist.patch" *** a/contrib/postgres_fdw/expected/postgres_fdw.out --- b/contrib/postgres_fdw/expected/postgres_fdw.out *************** *** 3193,3218 **** select * from bar where f1 in (select f1 from foo) for update; QUERY PLAN ---------------------------------------------------------------------------------------------- LockRows ! Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.* -> Hash Join ! Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.* Hash Cond: (bar.f1 = foo.f1) -> Append -> Seq Scan on public.bar ! Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.* -> Foreign Scan on public.bar2 ! Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.* Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -> Hash ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 -> HashAggregate ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 Group Key: foo.f1 -> Append -> Seq Scan on public.foo ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 -> Foreign Scan on public.foo2 ! Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1 Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 (22 rows) --- 3193,3218 ---- QUERY PLAN ---------------------------------------------------------------------------------------------- LockRows ! Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid, foo.ctid, foo.*, foo.tableoid -> Hash Join ! Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid, foo.ctid, foo.*, foo.tableoid Hash Cond: (bar.f1 = foo.f1) -> Append -> Seq Scan on public.bar ! Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid -> Foreign Scan on public.bar2 ! Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -> Hash ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 -> HashAggregate ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 Group Key: foo.f1 -> Append -> Seq Scan on public.foo ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 -> Foreign Scan on public.foo2 ! Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1 Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 (22 rows) *************** *** 3230,3255 **** select * from bar where f1 in (select f1 from foo) for share; QUERY PLAN ---------------------------------------------------------------------------------------------- LockRows ! Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.* -> Hash Join ! Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.* Hash Cond: (bar.f1 = foo.f1) -> Append -> Seq Scan on public.bar ! Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.* -> Foreign Scan on public.bar2 ! Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.* Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE -> Hash ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 -> HashAggregate ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 Group Key: foo.f1 -> Append -> Seq Scan on public.foo ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 -> Foreign Scan on public.foo2 ! Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1 Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 (22 rows) --- 3230,3255 ---- QUERY PLAN ---------------------------------------------------------------------------------------------- LockRows ! Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid, foo.ctid, foo.*, foo.tableoid -> Hash Join ! Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid, foo.ctid, foo.*, foo.tableoid Hash Cond: (bar.f1 = foo.f1) -> Append -> Seq Scan on public.bar ! Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid -> Foreign Scan on public.bar2 ! Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE -> Hash ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 -> HashAggregate ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 Group Key: foo.f1 -> Append -> Seq Scan on public.foo ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 -> Foreign Scan on public.foo2 ! Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1 Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 (22 rows) *************** *** 3272,3308 **** update bar set f2 = f2 + 100 where f1 in (select f1 from foo); Foreign Update on public.bar2 Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 -> Hash Join ! Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.tableoid, foo.* Hash Cond: (bar.f1 = foo.f1) -> Seq Scan on public.bar Output: bar.f1, bar.f2, bar.ctid -> Hash ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 -> HashAggregate ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 Group Key: foo.f1 -> Append -> Seq Scan on public.foo ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 -> Foreign Scan on public.foo2 ! Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1 Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -> Hash Join ! Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.tableoid, foo.* Hash Cond: (bar2.f1 = foo.f1) -> Foreign Scan on public.bar2 Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -> Hash ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 -> HashAggregate ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 Group Key: foo.f1 -> Append -> Seq Scan on public.foo ! Output: foo.ctid, foo.tableoid, foo.*, foo.f1 -> Foreign Scan on public.foo2 ! Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1 Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 (37 rows) --- 3272,3308 ---- Foreign Update on public.bar2 Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 -> Hash Join ! Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.*, foo.tableoid Hash Cond: (bar.f1 = foo.f1) -> Seq Scan on public.bar Output: bar.f1, bar.f2, bar.ctid -> Hash ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 -> HashAggregate ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 Group Key: foo.f1 -> Append -> Seq Scan on public.foo ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 -> Foreign Scan on public.foo2 ! Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1 Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -> Hash Join ! Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid Hash Cond: (bar2.f1 = foo.f1) -> Foreign Scan on public.bar2 Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -> Hash ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 -> HashAggregate ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 Group Key: foo.f1 -> Append -> Seq Scan on public.foo ! Output: foo.ctid, foo.*, foo.tableoid, foo.f1 -> Foreign Scan on public.foo2 ! Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1 Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 (37 rows) *** a/src/backend/optimizer/prep/preptlist.c --- b/src/backend/optimizer/prep/preptlist.c *************** *** 107,129 **** preprocess_targetlist(PlannerInfo *root, List *tlist) pstrdup(resname), true); tlist = lappend(tlist, tle); - - /* if parent of inheritance tree, need the tableoid too */ - if (rc->isParent) - { - var = makeVar(rc->rti, - TableOidAttributeNumber, - OIDOID, - -1, - InvalidOid, - 0); - snprintf(resname, sizeof(resname), "tableoid%u", rc->rowmarkId); - tle = makeTargetEntry((Expr *) var, - list_length(tlist) + 1, - pstrdup(resname), - true); - tlist = lappend(tlist, tle); - } } if (rc->allMarkTypes & (1 << ROW_MARK_COPY)) { --- 107,112 ---- *************** *** 139,144 **** preprocess_targetlist(PlannerInfo *root, List *tlist) --- 122,144 ---- true); tlist = lappend(tlist, tle); } + + /* if parent of inheritance tree, need the tableoid too */ + if (rc->isParent) + { + var = makeVar(rc->rti, + TableOidAttributeNumber, + OIDOID, + -1, + InvalidOid, + 0); + snprintf(resname, sizeof(resname), "tableoid%u", rc->rowmarkId); + tle = makeTargetEntry((Expr *) var, + list_length(tlist) + 1, + pstrdup(resname), + true); + tlist = lappend(tlist, tle); + } } /* --------------030601000604060104060309 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------030601000604060104060309--