Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Appearance settings

Commit 6753a5b

Browse filesBrowse files
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
Copy full SHA for 6753a5b

File tree

Expand file treeCollapse file tree

3 files changed

+126
-2
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+126
-2
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/expected/postgres_fdw.out
+86Lines changed: 86 additions & 0 deletions
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

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/sql/postgres_fdw.sql
+21Lines changed: 21 additions & 0 deletions
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

Copy file name to clipboardExpand all lines: src/backend/optimizer/util/inherit.c
+19-2Lines changed: 19 additions & 2 deletions
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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.