pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>
Cc: Noah Misch <noah@leadboat.com>
Cc: Shigeru Hanada <shigeru.hanada@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: inherit support for foreign tables
Date: Tue, 14 Apr 2015 19:49:26 +0900
Message-ID: <552CF0B6.8010006@lab.ntt.co.jp> (raw )
In-Reply-To: <21104.1427047056@sss.pgh.pa.us >
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 >
<CAFjFpRcVLtU6jE8h_N1Rc+Y-pDFbmR7YvBvxOXg6aTFrbZ1Z8Q@mail.gmail.com >
<54645499.7050709@lab.ntt.co.jp >
<CAFjFpRcBFVO0YP0nmfygpJOFfWKMkAN1Z8JmrkwyEWXDyMt5Hw@mail.gmail.com >
<CAFjFpRcd=F8Jqx+9yioHKVUA71LXBxO41ia61acjzyzQ4MZqVQ@mail.gmail.com >
<5476FB4B.9060705@lab.ntt.co.jp >
<CAFjFpRd6PR3Seg8D3t-XcXGxXyb+p2WDsk_wBGge7b=PyzP_Qw@mail.gmail.com >
<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 >
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 2015/03/23 2:57, Tom Lane wrote:
> Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachments:
[text/x-diff] preptlist.patch (11.4K, ../552CF0B6.8010006@lab.ntt.co.jp/2-preptlist.patch)
download | inline diff:
*** 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);
+ }
}
/*
view thread (179+ messages) latest in thread
Message-ID: <552CF0B6.8010006@lab.ntt.co.jp>
Permalink: ../552CF0B6.8010006@lab.ntt.co.jp/
Also on: postgresql.org/message-id/552CF0B6.8010006@lab.ntt.co.jp
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: fujita.etsuro@lab.ntt.co.jp, tgl@sss.pgh.pa.us, ashutosh.bapat@enterprisedb.com, horiguchi.kyotaro@lab.ntt.co.jp, hlinnakangas@vmware.com, noah@leadboat.com, shigeru.hanada@gmail.com
Subject: Re: inherit support for foreign tables
In-Reply-To: <552CF0B6.8010006@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox