On 2015/03/23 2:57, Tom Lane wrote: > Etsuro Fujita <fujita.ets...@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
*** 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); + } } /*
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers