Skip to content

Commit 2fe9777

Browse files
tglsfdctglsfdc
committed
YA attempt to stabilize the results of the postgres_fdw regression test.
We've made multiple attempts to stabilize the plans shown by commit 1bc0100, with little success so far. The reason for the remaining instability seems to be that if a transaction (such as auto-analyze) is running concurrently with the test, then get_actual_variable_range may return a maximum value for "T 1"."C 1" that's far away from the actual max, as a result of our having transiently inserted such a value earlier in the test. Because we use a non-MVCC snapshot to fetch the value (for performance reasons), the presence of other transactions can cause that function to return entries that are actually dead. To fix, use a less extreme value in the earlier transient insertion, so that whether it is visible or not won't affect the selectivity estimate. The use of 9999 there seems to have been picked with the aid of a dartboard anyway, rather than having a specific reason. Discussion: https://postgr.es/m/16962.1523551784@sss.pgh.pa.us
1 parent a4d56f5 commit 2fe9777

File tree

2 files changed

+15
-15
lines changed

2 files changed

+15
-15
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -5367,49 +5367,49 @@ SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
53675367
(819 rows)
53685368

53695369
EXPLAIN (verbose, costs off)
5370-
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
5370+
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
53715371
QUERY PLAN
53725372
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
53735373
Insert on public.ft2
53745374
Output: (tableoid)::regclass
53755375
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
53765376
-> Result
5377-
Output: 9999, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2 '::character(10), NULL::user_enum
5377+
Output: 1200, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2 '::character(10), NULL::user_enum
53785378
(5 rows)
53795379

5380-
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
5380+
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
53815381
tableoid
53825382
----------
53835383
ft2
53845384
(1 row)
53855385

53865386
EXPLAIN (verbose, costs off)
5387-
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
5387+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
53885388
QUERY PLAN
53895389
------------------------------------------------------------------------------------
53905390
Update on public.ft2
53915391
Output: (tableoid)::regclass
53925392
-> Foreign Update on public.ft2
5393-
Remote SQL: UPDATE "S 1"."T 1" SET c3 = 'bar'::text WHERE (("C 1" = 9999))
5393+
Remote SQL: UPDATE "S 1"."T 1" SET c3 = 'bar'::text WHERE (("C 1" = 1200))
53945394
(4 rows)
53955395

5396-
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
5396+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass;
53975397
tableoid
53985398
----------
53995399
ft2
54005400
(1 row)
54015401

54025402
EXPLAIN (verbose, costs off)
5403-
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
5403+
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
54045404
QUERY PLAN
54055405
--------------------------------------------------------------------
54065406
Delete on public.ft2
54075407
Output: (tableoid)::regclass
54085408
-> Foreign Delete on public.ft2
5409-
Remote SQL: DELETE FROM "S 1"."T 1" WHERE (("C 1" = 9999))
5409+
Remote SQL: DELETE FROM "S 1"."T 1" WHERE (("C 1" = 1200))
54105410
(4 rows)
54115411

5412-
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
5412+
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
54135413
tableoid
54145414
----------
54155415
ft2

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1099,14 +1099,14 @@ DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
10991099
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
11001100
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
11011101
EXPLAIN (verbose, costs off)
1102-
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
1103-
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
1102+
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
1103+
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
11041104
EXPLAIN (verbose, costs off)
1105-
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
1106-
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
1105+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
1106+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass;
11071107
EXPLAIN (verbose, costs off)
1108-
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
1109-
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
1108+
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
1109+
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
11101110

11111111
-- Test UPDATE/DELETE with RETURNING on a three-table join
11121112
INSERT INTO ft2 (c1,c2,c3)

0 commit comments

Comments
 (0)