Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit1177ab1

Browse files
committed
Make new regression test case parallel-safe, and improve its output.
The test case added by commit1f6d515 fails on buildfarm members thathave force_parallel_mode turned on, because we currently don't report sortperformance details from worker processes back to the master. To fix that,just make the test table be temp rather than regular; that's a good ideaanyway to forestall any possible interference from auto-analyze.(The restriction that workers can't access temp tables might go awaysomeday, but almost certainly not before the other thing gets fixed.)Also, improve the test so that we retain as much as possible of theEXPLAIN ANALYZE output. This aids debugging failures, and might alsoexpose problems that the preceding version masked.Discussion:http://postgr.es/m/CADE5jYLuugnEEUsyW6Q_4mZFYTxHxaVCQmGAsF0yiY8ZDggi-w@mail.gmail.com
1 parent27b8987 commit1177ab1

File tree

2 files changed

+66
-78
lines changed

2 files changed

+66
-78
lines changed

‎src/test/regress/expected/subselect.out

Lines changed: 37 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -1042,48 +1042,45 @@ NOTICE: x = 9, y = 13
10421042

10431043
drop function tattle(x int, y int);
10441044
--
1045-
-- Test that LIMIT can be pushed to SORT through a subquery that just
1046-
-- projects columns
1045+
-- Test that LIMIT can be pushed to SORT through a subquery that just projects
1046+
-- columns. We check for that having happened by looking to see if EXPLAIN
1047+
-- ANALYZE shows that a top-N sort was used. We must suppress or filter away
1048+
-- all the non-invariant parts of the EXPLAIN ANALYZE output.
10471049
--
1048-
create table sq_limit (pk int primary key, c1 int, c2 int);
1050+
createtemptable sq_limit (pk int primary key, c1 int, c2 int);
10491051
insert into sq_limit values
1050-
(1, 1, 1),
1051-
(2, 2, 2),
1052-
(3, 3, 3),
1053-
(4, 4, 4),
1054-
(5, 1, 1),
1055-
(6, 2, 2),
1056-
(7, 3, 3),
1057-
(8, 4, 4);
1058-
-- The explain contains data that may not be invariant, so
1059-
-- filter for just the interesting bits. The goal here is that
1060-
-- we should see three notices, in order:
1061-
-- NOTICE: Limit
1062-
-- NOTICE: Subquery
1063-
-- NOTICE: Top-N Sort
1064-
-- A missing step, or steps out of order means we have a problem.
1065-
do $$
1066-
declare x text;
1067-
begin
1068-
for x in
1069-
explain (analyze, summary off, timing off, costs off)
1070-
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
1071-
loop
1072-
if (left(ltrim(x), 5) = 'Limit') then
1073-
raise notice 'Limit';
1074-
end if;
1075-
if (left(ltrim(x), 12) = '-> Subquery') then
1076-
raise notice 'Subquery';
1077-
end if;
1078-
if (left(ltrim(x), 18) = 'Sort Method: top-N') then
1079-
raise notice 'Top-N Sort';
1080-
end if;
1081-
end loop;
1082-
end;
1052+
(1, 1, 1),
1053+
(2, 2, 2),
1054+
(3, 3, 3),
1055+
(4, 4, 4),
1056+
(5, 1, 1),
1057+
(6, 2, 2),
1058+
(7, 3, 3),
1059+
(8, 4, 4);
1060+
create function explain_sq_limit() returns setof text language plpgsql as
1061+
$$
1062+
declare ln text;
1063+
begin
1064+
for ln in
1065+
explain (analyze, summary off, timing off, costs off)
1066+
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
1067+
loop
1068+
ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
1069+
return next ln;
1070+
end loop;
1071+
end;
10831072
$$;
1084-
NOTICE: Limit
1085-
NOTICE: Subquery
1086-
NOTICE: Top-N Sort
1073+
select * from explain_sq_limit();
1074+
explain_sq_limit
1075+
----------------------------------------------------------------
1076+
Limit (actual rows=3 loops=1)
1077+
-> Subquery Scan on x (actual rows=3 loops=1)
1078+
-> Sort (actual rows=3 loops=1)
1079+
Sort Key: sq_limit.c1, sq_limit.pk
1080+
Sort Method: top-N heapsort Memory: xxx
1081+
-> Seq Scan on sq_limit (actual rows=8 loops=1)
1082+
(6 rows)
1083+
10871084
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
10881085
pk | c2
10891086
----+----
@@ -1092,4 +1089,4 @@ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
10921089
2 | 2
10931090
(3 rows)
10941091

1095-
droptable sq_limit;
1092+
dropfunction explain_sq_limit();

‎src/test/regress/sql/subselect.sql

Lines changed: 29 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -542,47 +542,38 @@ select * from
542542
dropfunction tattle(xint, yint);
543543

544544
--
545-
-- Test that LIMIT can be pushed to SORT through a subquery that just
546-
-- projects columns
545+
-- Test that LIMIT can be pushed to SORT through a subquery that just projects
546+
-- columns. We check for that having happened by looking to see if EXPLAIN
547+
-- ANALYZE shows that a top-N sort was used. We must suppress or filter away
548+
-- all the non-invariant parts of the EXPLAIN ANALYZE output.
547549
--
548-
createtablesq_limit (pkintprimary key, c1int, c2int);
550+
createtemptable sq_limit (pkintprimary key, c1int, c2int);
549551
insert into sq_limitvalues
550-
(1,1,1),
551-
(2,2,2),
552-
(3,3,3),
553-
(4,4,4),
554-
(5,1,1),
555-
(6,2,2),
556-
(7,3,3),
557-
(8,4,4);
558-
559-
-- The explain contains data that may not be invariant, so
560-
-- filter for just the interesting bits. The goal here is that
561-
-- we should see three notices, in order:
562-
-- NOTICE: Limit
563-
-- NOTICE: Subquery
564-
-- NOTICE: Top-N Sort
565-
-- A missing step, or steps out of order means we have a problem.
566-
do $$
567-
declare xtext;
568-
begin
569-
for xin
570-
explain (analyze, summary off, timing off, costs off)
571-
select*from (select pk,c2from sq_limitorder by c1,pk)as xlimit3
572-
loop
573-
if (left(ltrim(x),5)='Limit') then
574-
raise notice'Limit';
575-
end if;
576-
if (left(ltrim(x),12)='-> Subquery') then
577-
raise notice'Subquery';
578-
end if;
579-
if (left(ltrim(x),18)='Sort Method: top-N') then
580-
raise notice'Top-N Sort';
581-
end if;
582-
end loop;
583-
end;
552+
(1,1,1),
553+
(2,2,2),
554+
(3,3,3),
555+
(4,4,4),
556+
(5,1,1),
557+
(6,2,2),
558+
(7,3,3),
559+
(8,4,4);
560+
561+
createfunctionexplain_sq_limit() returns setoftext language plpgsqlas
562+
$$
563+
declare lntext;
564+
begin
565+
for lnin
566+
explain (analyze, summary off, timing off, costs off)
567+
select*from (select pk,c2from sq_limitorder by c1,pk)as xlimit3
568+
loop
569+
ln := regexp_replace(ln,'Memory:\S*','Memory: xxx');
570+
return next ln;
571+
end loop;
572+
end;
584573
$$;
585574

575+
select*from explain_sq_limit();
576+
586577
select*from (select pk,c2from sq_limitorder by c1,pk)as xlimit3;
587578

588-
droptable sq_limit;
579+
dropfunction explain_sq_limit();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp