Skip to content

Commit 6753a5b

Browse files
tglsfdctglsfdc
committed
Fix planner's row-mark code for inheritance from a foreign table.
Commit 428b260 broke planning of cases where row marks are needed (SELECT FOR UPDATE, etc) and one of the query's tables is a foreign table that has regular table(s) as inheritance children. We got the reverse case right, but apparently were thinking that foreign tables couldn't be inheritance parents. Not so; so we need to be able to add a CTID junk column while adding a new child, not only a wholerow junk column. Back-patch to v12 where the faulty code came in. Amit Langote Discussion: https://postgr.es/m/CA+HiwqEmo3FV1LAQ4TVyS2h1WM=kMkZUmbNuZSCnfHvMcUcPeA@mail.gmail.com
1 parent e5b0fff commit 6753a5b

File tree

3 files changed

+126
-2
lines changed

3 files changed

+126
-2
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

+86
Original file line numberDiff line numberDiff line change
@@ -7220,6 +7220,92 @@ select * from bar where f1 in (select f1 from foo) for share;
72207220
4 | 44
72217221
(4 rows)
72227222

7223+
-- Now check SELECT FOR UPDATE/SHARE with an inherited source table,
7224+
-- where the parent is itself a foreign table
7225+
create table loct4 (f1 int, f2 int, f3 int);
7226+
create foreign table foo2child (f3 int) inherits (foo2)
7227+
server loopback options (table_name 'loct4');
7228+
NOTICE: moving and merging column "f3" with inherited definition
7229+
DETAIL: User-specified column moved to the position of the inherited column.
7230+
explain (verbose, costs off)
7231+
select * from bar where f1 in (select f1 from foo2) for share;
7232+
QUERY PLAN
7233+
--------------------------------------------------------------------------------------
7234+
LockRows
7235+
Output: bar.f1, bar.f2, bar.ctid, foo2.*, bar.*, bar.tableoid, foo2.tableoid
7236+
-> Hash Join
7237+
Output: bar.f1, bar.f2, bar.ctid, foo2.*, bar.*, bar.tableoid, foo2.tableoid
7238+
Inner Unique: true
7239+
Hash Cond: (bar.f1 = foo2.f1)
7240+
-> Append
7241+
-> Seq Scan on public.bar bar_1
7242+
Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid
7243+
-> Foreign Scan on public.bar2 bar_2
7244+
Output: bar_2.f1, bar_2.f2, bar_2.ctid, bar_2.*, bar_2.tableoid
7245+
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE
7246+
-> Hash
7247+
Output: foo2.*, foo2.f1, foo2.tableoid
7248+
-> HashAggregate
7249+
Output: foo2.*, foo2.f1, foo2.tableoid
7250+
Group Key: foo2.f1
7251+
-> Append
7252+
-> Foreign Scan on public.foo2 foo2_1
7253+
Output: foo2_1.*, foo2_1.f1, foo2_1.tableoid
7254+
Remote SQL: SELECT f1, f2, f3 FROM public.loct1
7255+
-> Foreign Scan on public.foo2child foo2_2
7256+
Output: foo2_2.*, foo2_2.f1, foo2_2.tableoid
7257+
Remote SQL: SELECT f1, f2, f3 FROM public.loct4
7258+
(24 rows)
7259+
7260+
select * from bar where f1 in (select f1 from foo2) for share;
7261+
f1 | f2
7262+
----+----
7263+
2 | 22
7264+
4 | 44
7265+
(2 rows)
7266+
7267+
drop foreign table foo2child;
7268+
-- And with a local child relation of the foreign table parent
7269+
create table foo2child (f3 int) inherits (foo2);
7270+
NOTICE: moving and merging column "f3" with inherited definition
7271+
DETAIL: User-specified column moved to the position of the inherited column.
7272+
explain (verbose, costs off)
7273+
select * from bar where f1 in (select f1 from foo2) for share;
7274+
QUERY PLAN
7275+
-------------------------------------------------------------------------------------------------
7276+
LockRows
7277+
Output: bar.f1, bar.f2, bar.ctid, foo2.*, bar.*, bar.tableoid, foo2.ctid, foo2.tableoid
7278+
-> Hash Join
7279+
Output: bar.f1, bar.f2, bar.ctid, foo2.*, bar.*, bar.tableoid, foo2.ctid, foo2.tableoid
7280+
Inner Unique: true
7281+
Hash Cond: (bar.f1 = foo2.f1)
7282+
-> Append
7283+
-> Seq Scan on public.bar bar_1
7284+
Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid
7285+
-> Foreign Scan on public.bar2 bar_2
7286+
Output: bar_2.f1, bar_2.f2, bar_2.ctid, bar_2.*, bar_2.tableoid
7287+
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE
7288+
-> Hash
7289+
Output: foo2.*, foo2.f1, foo2.ctid, foo2.tableoid
7290+
-> HashAggregate
7291+
Output: foo2.*, foo2.f1, foo2.ctid, foo2.tableoid
7292+
Group Key: foo2.f1
7293+
-> Append
7294+
-> Foreign Scan on public.foo2 foo2_1
7295+
Output: foo2_1.*, foo2_1.f1, foo2_1.ctid, foo2_1.tableoid
7296+
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
7297+
-> Seq Scan on public.foo2child foo2_2
7298+
Output: foo2_2.*, foo2_2.f1, foo2_2.ctid, foo2_2.tableoid
7299+
(23 rows)
7300+
7301+
select * from bar where f1 in (select f1 from foo2) for share;
7302+
f1 | f2
7303+
----+----
7304+
2 | 22
7305+
4 | 44
7306+
(2 rows)
7307+
7308+
drop table foo2child;
72237309
-- Check UPDATE with inherited target and an inherited source table
72247310
explain (verbose, costs off)
72257311
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);

‎contrib/postgres_fdw/sql/postgres_fdw.sql

+21
Original file line numberDiff line numberDiff line change
@@ -1859,6 +1859,27 @@ explain (verbose, costs off)
18591859
select * from bar where f1 in (select f1 from foo) for share;
18601860
select * from bar where f1 in (select f1 from foo) for share;
18611861

1862+
-- Now check SELECT FOR UPDATE/SHARE with an inherited source table,
1863+
-- where the parent is itself a foreign table
1864+
create table loct4 (f1 int, f2 int, f3 int);
1865+
create foreign table foo2child (f3 int) inherits (foo2)
1866+
server loopback options (table_name 'loct4');
1867+
1868+
explain (verbose, costs off)
1869+
select * from bar where f1 in (select f1 from foo2) for share;
1870+
select * from bar where f1 in (select f1 from foo2) for share;
1871+
1872+
drop foreign table foo2child;
1873+
1874+
-- And with a local child relation of the foreign table parent
1875+
create table foo2child (f3 int) inherits (foo2);
1876+
1877+
explain (verbose, costs off)
1878+
select * from bar where f1 in (select f1 from foo2) for share;
1879+
select * from bar where f1 in (select f1 from foo2) for share;
1880+
1881+
drop table foo2child;
1882+
18621883
-- Check UPDATE with inherited target and an inherited source table
18631884
explain (verbose, costs off)
18641885
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);

‎src/backend/optimizer/util/inherit.c

+19-2
Original file line numberDiff line numberDiff line change
@@ -231,8 +231,25 @@ expand_inherited_rtentry(PlannerInfo *root, RelOptInfo *rel,
231231
char resname[32];
232232
List *newvars = NIL;
233233

234-
/* The old PlanRowMark should already have necessitated adding TID */
235-
Assert(old_allMarkTypes & ~(1 << ROW_MARK_COPY));
234+
/* Add TID junk Var if needed, unless we had it already */
235+
if (new_allMarkTypes & ~(1 << ROW_MARK_COPY) &&
236+
!(old_allMarkTypes & ~(1 << ROW_MARK_COPY)))
237+
{
238+
/* Need to fetch TID */
239+
var = makeVar(oldrc->rti,
240+
SelfItemPointerAttributeNumber,
241+
TIDOID,
242+
-1,
243+
InvalidOid,
244+
0);
245+
snprintf(resname, sizeof(resname), "ctid%u", oldrc->rowmarkId);
246+
tle = makeTargetEntry((Expr *) var,
247+
list_length(root->processed_tlist) + 1,
248+
pstrdup(resname),
249+
true);
250+
root->processed_tlist = lappend(root->processed_tlist, tle);
251+
newvars = lappend(newvars, var);
252+
}
236253

237254
/* Add whole-row junk Var if needed, unless we had it already */
238255
if ((new_allMarkTypes & (1 << ROW_MARK_COPY)) &&

0 commit comments

Comments
 (0)