# Checking port 56473 # Found port 56473 Name: primary Data directory: /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_primary_data/pgdata Backup directory: /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_primary_data/backup Archive directory: /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_primary_data/archives Connection string: port=56473 host=/tmp/SxybDgAP0Z Log file: /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/log/027_stream_regress_primary.log [18:13:22.138](0.040s) # initializing database system by copying initdb template # Running: cp -RPp /tmp/cirrus-ci-build/build-32/tmp_install/initdb-template /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_primary_data/pgdata # Running: /tmp/cirrus-ci-build/build-32/src/test/regress/pg_regress --config-auth /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_primary_data/pgdata ### Starting node "primary" # Running: pg_ctl -w -D /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_primary_data/pgdata -l /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/log/027_stream_regress_primary.log -o --cluster-name=primary start waiting for server to start.... done server started # Postmaster PID for node "primary" is 66199 (standby_1,) [18:13:22.563](0.425s) ok 1 - physical slot created on primary # Taking pg_basebackup my_backup from node "primary" # Running: pg_basebackup -D /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_primary_data/backup/my_backup -h /tmp/SxybDgAP0Z -p 56473 --checkpoint fast --no-sync # Backup finished # Checking port 56474 # Found port 56474 Name: standby_1 Data directory: /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_standby_1_data/pgdata Backup directory: /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_standby_1_data/backup Archive directory: /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_standby_1_data/archives Connection string: port=56474 host=/tmp/SxybDgAP0Z Log file: /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/log/027_stream_regress_standby_1.log # Initializing node "standby_1" from backup "my_backup" of node "primary" ### Enabling streaming replication for node "standby_1" ### Starting node "standby_1" # Running: pg_ctl -w -D /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_standby_1_data/pgdata -l /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/log/027_stream_regress_standby_1.log -o --cluster-name=standby_1 start waiting for server to start.... done server started # Postmaster PID for node "standby_1" is 66380 # using postmaster on /tmp/SxybDgAP0Z, port 56473 ok 1 - test_setup 333 ms # parallel group (20 tests): pg_lsn char name int2 txid oid int4 text varchar regproc uuid float4 money boolean int8 float8 bit enum numeric rangetypes ok 2 + boolean 239 ms ok 3 + char 128 ms ok 4 + name 129 ms ok 5 + varchar 172 ms ok 6 + text 164 ms ok 7 + int2 150 ms ok 8 + int4 163 ms ok 9 + int8 258 ms ok 10 + oid 155 ms ok 11 + float4 203 ms ok 12 + float8 262 ms ok 13 + bit 295 ms ok 14 + numeric 1101 ms ok 15 + txid 149 ms ok 16 + uuid 198 ms ok 17 + enum 316 ms ok 18 + money 224 ms ok 19 + rangetypes 1153 ms ok 20 + pg_lsn 110 ms ok 21 + regproc 185 ms # parallel group (20 tests): line md5 lseg path time circle timetz point macaddr numerology date macaddr8 inet interval timestamp strings timestamptz multirangetypes polygon box ok 22 + strings 608 ms ok 23 + md5 95 ms ok 24 + numerology 212 ms ok 25 + point 170 ms ok 26 + lseg 97 ms ok 27 + line 82 ms ok 28 + box 1034 ms ok 29 + path 117 ms ok 30 + polygon 1028 ms ok 31 + circle 125 ms ok 32 + date 234 ms ok 33 + time 117 ms ok 34 + timetz 159 ms ok 35 + timestamp 511 ms ok 36 + timestamptz 626 ms ok 37 + interval 418 ms ok 38 + inet 283 ms ok 39 + macaddr 178 ms ok 40 + macaddr8 250 ms ok 41 + multirangetypes 937 ms # parallel group (12 tests): unicode comments misc_sanity xid tstypes expressions mvcc horology geometry type_sanity regex opr_sanity ok 42 + geometry 552 ms ok 43 + horology 524 ms ok 44 + tstypes 334 ms ok 45 + regex 1335 ms ok 46 + type_sanity 679 ms ok 47 + opr_sanity 1656 ms ok 48 + misc_sanity 137 ms ok 49 + comments 105 ms ok 50 + expressions 408 ms ok 51 + unicode 86 ms ok 52 + xid 257 ms ok 53 + mvcc 479 ms # parallel group (5 tests): copydml copyselect copy insert_conflict insert ok 54 + copy 374 ms ok 55 + copyselect 141 ms ok 56 + copydml 66 ms ok 57 + insert 1203 ms ok 58 + insert_conflict 572 ms # parallel group (7 tests): create_function_c create_operator create_type create_schema create_misc create_procedure create_table ok 59 + create_function_c 47 ms ok 60 + create_misc 216 ms ok 61 + create_operator 149 ms ok 62 + create_procedure 215 ms ok 63 + create_table 1216 ms ok 64 + create_type 149 ms ok 65 + create_schema 214 ms # parallel group (5 tests): index_including index_including_gist create_view create_index_spgist create_index not ok 66 + create_index 3645 ms ok 67 + create_index_spgist 2859 ms ok 68 + create_view 2131 ms ok 69 + index_including 747 ms ok 70 + index_including_gist 1664 ms # parallel group (16 tests): errors create_cast roleattributes hash_func create_aggregate drop_if_exists select typed_table create_function_sql create_am infinite_recurse vacuum constraints inherit updatable_views triggers ok 71 + create_aggregate 250 ms ok 72 + create_function_sql 714 ms ok 73 + create_cast 163 ms ok 74 + constraints 2422 ms ok 75 + triggers 9959 ms ok 76 + select 360 ms ok 77 + inherit 4493 ms ok 78 + typed_table 542 ms ok 79 + vacuum 1606 ms ok 80 + drop_if_exists 314 ms ok 81 + updatable_views 4581 ms ok 82 + roleattributes 181 ms ok 83 + create_am 853 ms ok 84 + hash_func 239 ms ok 85 + errors 124 ms ok 86 + infinite_recurse 892 ms ok 87 - sanity_check 405 ms # parallel group (20 tests): select_distinct_on delete select_implicit select_having case namespace select_into prepared_xacts random select_distinct portals transactions arrays union subselect update hash_index join aggregates btree_index ok 88 + select_into 379 ms not ok 89 + select_distinct 684 ms ok 90 + select_distinct_on 128 ms ok 91 + select_implicit 221 ms ok 92 + select_having 282 ms not ok 93 + subselect 1667 ms not ok 94 + union 1432 ms ok 95 + case 301 ms not ok 96 + join 3428 ms not ok 97 + aggregates 3946 ms ok 98 + transactions 832 ms ok 99 + random 526 ms ok 100 + portals 761 ms ok 101 + arrays 1354 ms ok 102 + btree_index 7752 ms ok 103 + hash_index 2276 ms ok 104 + update 2218 ms ok 105 + delete 149 ms ok 106 + namespace 310 ms ok 107 + prepared_xacts 450 ms # parallel group (20 tests): init_privs drop_operator security_label password lock tablesample object_address collate replica_identity groupingsets matview identity spgist generated rowsecurity gin gist brin join_hash privileges ok 108 + brin 5896 ms ok 109 + gin 4248 ms ok 110 + gist 4661 ms ok 111 + spgist 3575 ms ok 112 + privileges 7265 ms ok 113 + init_privs 113 ms ok 114 + security_label 272 ms ok 115 + collate 1146 ms ok 116 + matview 2137 ms ok 117 + lock 510 ms ok 118 + replica_identity 1396 ms ok 119 + rowsecurity 3931 ms ok 120 + object_address 599 ms ok 121 + tablesample 510 ms ok 122 + groupingsets 1497 ms ok 123 + drop_operator 225 ms ok 124 + password 418 ms ok 125 + identity 2509 ms ok 126 + generated 3799 ms ok 127 + join_hash 5956 ms # parallel group (2 tests): brin_bloom brin_multi ok 128 + brin_bloom 379 ms ok 129 + brin_multi 2005 ms # parallel group (18 tests): collate.icu.utf8 async collate.utf8 dbsize tid tsrf tidrangescan alter_operator tidscan create_role misc_functions sysviews misc alter_generic incremental_sort merge create_table_like without_overlaps ok 130 + create_table_like 1342 ms ok 131 + alter_generic 660 ms ok 132 + alter_operator 279 ms ok 133 + misc 483 ms ok 134 + async 76 ms ok 135 + dbsize 101 ms ok 136 + merge 1312 ms ok 137 + misc_functions 406 ms ok 138 + sysviews 470 ms ok 139 + tsrf 218 ms ok 140 + tid 209 ms ok 141 + tidscan 318 ms ok 142 + tidrangescan 225 ms ok 143 + collate.utf8 87 ms ok 144 + collate.icu.utf8 61 ms ok 145 + incremental_sort 747 ms ok 146 + create_role 380 ms ok 147 + without_overlaps 1739 ms # parallel group (7 tests): collate.linux.utf8 collate.windows.win1252 amutils psql_crosstab psql rules stats_ext ok 148 + rules 1817 ms ok 149 + psql 1483 ms ok 150 + psql_crosstab 67 ms ok 151 + amutils 67 ms ok 152 + stats_ext 3543 ms ok 153 + collate.linux.utf8 36 ms ok 154 + collate.windows.win1252 44 ms not ok 155 - select_parallel 2610 ms ok 156 - write_parallel 262 ms ok 157 - vacuum_parallel 212 ms # parallel group (2 tests): subscription publication ok 158 + publication 1291 ms ok 159 + subscription 179 ms # parallel group (17 tests): advisory_lock portals_p2 combocid xmlmap guc functional_deps equivclass tsdicts dependency select_views indirect_toast bitmapops window cluster tsearch foreign_data foreign_key ok 160 + select_views 704 ms ok 161 + portals_p2 213 ms ok 162 + foreign_key 4234 ms ok 163 + cluster 1808 ms ok 164 + dependency 459 ms ok 165 + guc 406 ms ok 166 + bitmapops 1484 ms ok 167 + combocid 270 ms ok 168 + tsearch 1994 ms ok 169 + tsdicts 428 ms ok 170 + foreign_data 3185 ms not ok 171 + window 1565 ms ok 172 + xmlmap 315 ms ok 173 + functional_deps 415 ms ok 174 + advisory_lock 196 ms ok 175 + indirect_toast 1077 ms ok 176 + equivclass 422 ms # parallel group (8 tests): json_encoding jsonpath_encoding jsonpath sqljson sqljson_queryfuncs jsonb_jsonpath json jsonb ok 177 + json 435 ms ok 178 + jsonb 1032 ms ok 179 + json_encoding 50 ms ok 180 + jsonpath 176 ms ok 181 + jsonpath_encoding 62 ms ok 182 + jsonb_jsonpath 367 ms ok 183 + sqljson 192 ms ok 184 + sqljson_queryfuncs 245 ms # parallel group (18 tests): prepare returning plancache limit conversion temp sequence rowtypes copy2 polymorphism largeobject with truncate rangefuncs domain xml plpgsql alter_table ok 185 + plancache 620 ms ok 186 + limit 689 ms ok 187 + plpgsql 4675 ms ok 188 + copy2 1206 ms ok 189 + temp 886 ms ok 190 + domain 1737 ms ok 191 + rangefuncs 1580 ms ok 192 + prepare 263 ms ok 193 + conversion 735 ms ok 194 + truncate 1436 ms ok 195 + alter_table 5747 ms ok 196 + sequence 1074 ms ok 197 + polymorphism 1268 ms not ok 198 + rowtypes 1157 ms ok 199 + returning 455 ms ok 200 + largeobject 1345 ms not ok 201 + with 1426 ms ok 202 + xml 2774 ms # parallel group (13 tests): predicate hash_part reloptions explain partition_info memoize compression stats partition_aggregate tuplesort indexing partition_join partition_prune ok 203 + partition_join 3416 ms ok 204 + partition_prune 3569 ms ok 205 + reloptions 195 ms ok 206 + hash_part 156 ms ok 207 + indexing 3398 ms ok 208 + partition_aggregate 2735 ms ok 209 + partition_info 373 ms ok 210 + tuplesort 3393 ms ok 211 + explain 363 ms ok 212 + compression 793 ms ok 213 + memoize 709 ms ok 214 + stats 1384 ms ok 215 + predicate 97 ms # parallel group (2 tests): oidjoins event_trigger ok 216 + oidjoins 760 ms ok 217 + event_trigger 839 ms ok 218 - event_trigger_login 61 ms ok 219 - fast_default 325 ms ok 220 - tablespace 821 ms 1..220 # 10 of 220 tests failed. # The differences that caused some tests to fail can be viewed in the file "/tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/regression.diffs". # A copy of the test summary that you see above is saved in the file "/tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/regression.out". === dumping /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/regression.diffs === diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/create_index.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/create_index.out --- /tmp/cirrus-ci-build/src/test/regress/expected/create_index.out 2024-04-03 18:05:02.753546749 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/create_index.out 2024-04-03 18:13:34.925159547 +0000 @@ -1916,11 +1916,15 @@ SELECT unique1 FROM tenk1 WHERE unique1 IN (1,42,7) ORDER BY unique1; - QUERY PLAN -------------------------------------------------------- - Index Only Scan using tenk1_unique1 on tenk1 - Index Cond: (unique1 = ANY ('{1,42,7}'::integer[])) -(2 rows) + QUERY PLAN +------------------------------------------------------------------- + Sort + Sort Key: unique1 + -> Bitmap Heap Scan on tenk1 + Recheck Cond: (unique1 = ANY ('{1,42,7}'::integer[])) + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = ANY ('{1,42,7}'::integer[])) +(6 rows) SELECT unique1 FROM tenk1 WHERE unique1 IN (1,42,7) @@ -1936,12 +1940,13 @@ SELECT thousand, tenthous FROM tenk1 WHERE thousand < 2 AND tenthous IN (1001,3000) ORDER BY thousand; - QUERY PLAN -------------------------------------------------------- - Index Only Scan using tenk1_thous_tenthous on tenk1 - Index Cond: (thousand < 2) - Filter: (tenthous = ANY ('{1001,3000}'::integer[])) -(3 rows) + QUERY PLAN +-------------------------------------------------------------------------------------- + Sort + Sort Key: thousand + -> Index Only Scan using tenk1_thous_tenthous on tenk1 + Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[]))) +(4 rows) SELECT thousand, tenthous FROM tenk1 WHERE thousand < 2 AND tenthous IN (1001,3000) diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/select_distinct.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/select_distinct.out --- /tmp/cirrus-ci-build/src/test/regress/expected/select_distinct.out 2024-04-03 18:05:02.817546727 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/select_distinct.out 2024-04-03 18:13:46.289144233 +0000 @@ -262,12 +262,12 @@ -- Ensure we don't do parallel distinct with a parallel unsafe function EXPLAIN (COSTS OFF) SELECT DISTINCT distinct_func(1) FROM tenk1; - QUERY PLAN ----------------------------------------------------------- + QUERY PLAN +-------------------------------------- Unique -> Sort Sort Key: (distinct_func(1)) - -> Index Only Scan using tenk1_hundred on tenk1 + -> Seq Scan on tenk1 (4 rows) -- make the function parallel safe diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/subselect.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/subselect.out --- /tmp/cirrus-ci-build/src/test/regress/expected/subselect.out 2024-04-03 18:05:02.821546726 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/subselect.out 2024-04-03 18:13:47.273142907 +0000 @@ -1029,7 +1029,7 @@ -> Seq Scan on tenk1 t Filter: ((ANY (unique2 = (hashed SubPlan 2).col1)) OR (ten < 0)) SubPlan 2 - -> Index Only Scan using tenk1_unique1 on tenk1 k + -> Seq Scan on tenk1 k (5 rows) select count(*) from tenk1 t @@ -1403,7 +1403,7 @@ -> Seq Scan on public.tenk1 b Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4 SubPlan 1 - -> Index Only Scan using tenk1_unique1 on public.tenk1 a + -> Seq Scan on public.tenk1 a Output: a.unique1 (10 rows) diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/union.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/union.out --- /tmp/cirrus-ci-build/src/test/regress/expected/union.out 2024-04-03 18:05:02.829546723 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/union.out 2024-04-03 18:13:47.037143225 +0000 @@ -350,13 +350,13 @@ explain (costs off) select count(*) from ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------- Aggregate -> HashAggregate Group Key: tenk1.unique1 -> Append - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 -> Seq Scan on tenk1 tenk1_1 (6 rows) @@ -370,8 +370,8 @@ explain (costs off) select count(*) from ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; - QUERY PLAN ------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Aggregate -> Subquery Scan on ss -> HashSetOp Intersect @@ -379,7 +379,7 @@ -> Subquery Scan on "*SELECT* 2" -> Seq Scan on tenk1 -> Subquery Scan on "*SELECT* 1" - -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 + -> Seq Scan on tenk1 tenk1_1 (8 rows) select count(*) from @@ -391,14 +391,14 @@ explain (costs off) select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------- HashSetOp Except -> Append -> Subquery Scan on "*SELECT* 1" - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 -> Subquery Scan on "*SELECT* 2" - -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1 + -> Seq Scan on tenk1 tenk1_1 Filter: (unique2 <> 10) (7 rows) @@ -412,17 +412,19 @@ explain (costs off) select count(*) from ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------- Aggregate -> Unique -> Merge Append Sort Key: tenk1.unique1 - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Sort + Sort Key: tenk1.unique1 + -> Seq Scan on tenk1 -> Sort Sort Key: tenk1_1.fivethous -> Seq Scan on tenk1 tenk1_1 -(8 rows) +(10 rows) select count(*) from ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; @@ -434,8 +436,8 @@ explain (costs off) select count(*) from ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; - QUERY PLAN ------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------- Aggregate -> Subquery Scan on ss -> SetOp Intersect @@ -445,7 +447,7 @@ -> Subquery Scan on "*SELECT* 2" -> Seq Scan on tenk1 -> Subquery Scan on "*SELECT* 1" - -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 + -> Seq Scan on tenk1 tenk1_1 (10 rows) select count(*) from @@ -457,16 +459,16 @@ explain (costs off) select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------- SetOp Except -> Sort Sort Key: "*SELECT* 1".unique1 -> Append -> Subquery Scan on "*SELECT* 1" - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 -> Subquery Scan on "*SELECT* 2" - -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1 + -> Seq Scan on tenk1 tenk1_1 Filter: (unique2 <> 10) (9 rows) diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/join.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/join.out --- /tmp/cirrus-ci-build/src/test/regress/expected/join.out 2024-04-03 18:05:02.781546740 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/join.out 2024-04-03 18:13:49.029140541 +0000 @@ -2780,20 +2780,22 @@ from tenk1 x inner join tenk1 y on x.thousand = y.thousand group by x.thousand, x.twothousand order by x.thousand desc, x.twothousand; - QUERY PLAN ----------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------- GroupAggregate Group Key: x.thousand, x.twothousand -> Incremental Sort Sort Key: x.thousand DESC, x.twothousand Presorted Key: x.thousand -> Merge Join - Merge Cond: (y.thousand = x.thousand) - -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 y + Merge Cond: (x.thousand = y.thousand) -> Sort Sort Key: x.thousand DESC -> Seq Scan on tenk1 x -(11 rows) + -> Sort + Sort Key: y.thousand DESC + -> Seq Scan on tenk1 y +(13 rows) reset enable_hashagg; reset enable_nestloop; @@ -2934,12 +2936,12 @@ explain (costs off) select count(*) from tenk1 a, tenk1 b where a.hundred = b.thousand and (b.fivethous % 10) < 10; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Aggregate -> Hash Join Hash Cond: (a.hundred = b.thousand) - -> Index Only Scan using tenk1_hundred on tenk1 a + -> Seq Scan on tenk1 a -> Hash -> Seq Scan on tenk1 b Filter: ((fivethous % 10) < 10) @@ -3015,13 +3017,13 @@ explain (costs off) select a.* from tenk1 a where unique1 in (select unique2 from tenk1 b); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------- Hash Semi Join Hash Cond: (a.unique1 = b.unique2) -> Seq Scan on tenk1 a -> Hash - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (5 rows) -- sadly, this is not an antijoin @@ -3033,43 +3035,43 @@ Seq Scan on tenk1 a Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1))) SubPlan 1 - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (4 rows) explain (costs off) select a.* from tenk1 a where exists (select 1 from tenk1 b where a.unique1 = b.unique2); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------- Hash Semi Join Hash Cond: (a.unique1 = b.unique2) -> Seq Scan on tenk1 a -> Hash - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (5 rows) explain (costs off) select a.* from tenk1 a where not exists (select 1 from tenk1 b where a.unique1 = b.unique2); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------- Hash Anti Join Hash Cond: (a.unique1 = b.unique2) -> Seq Scan on tenk1 a -> Hash - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (5 rows) explain (costs off) select a.* from tenk1 a left join tenk1 b on a.unique1 = b.unique2 where b.unique2 is null; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------- Hash Anti Join Hash Cond: (a.unique1 = b.unique2) -> Seq Scan on tenk1 a -> Hash - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (5 rows) -- @@ -4029,11 +4031,11 @@ explain (costs off) select unique1, x from tenk1 left join f_immutable_int4(1) x on unique1 = x; - QUERY PLAN ----------------------------------------------------- + QUERY PLAN +------------------------------------ Nested Loop Left Join Join Filter: (tenk1.unique1 = 1) - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 -> Materialize -> Result (5 rows) @@ -4050,15 +4052,14 @@ explain (costs off) select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x; - QUERY PLAN ----------------------------------------------------- - Merge Full Join - Merge Cond: (tenk1.unique1 = (1)) - -> Index Only Scan using tenk1_unique1 on tenk1 - -> Sort - Sort Key: (1) + QUERY PLAN +------------------------------------ + Hash Full Join + Hash Cond: (tenk1.unique1 = (1)) + -> Seq Scan on tenk1 + -> Hash -> Result -(6 rows) +(5 rows) -- check that pullup of a const function allows further const-folding explain (costs off) @@ -4141,15 +4142,15 @@ select (t2.*).unique1, f_field_select(t2) from tenk1 t1 left join onek t2 on t1.unique1 = t2.unique1 left join int8_tbl t3 on true; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join Output: t2.unique1, t2.unique2 -> Hash Left Join Output: t2.unique1, t2.unique2 Hash Cond: (t1.unique1 = t2.unique1) - -> Index Only Scan using tenk1_unique1 on public.tenk1 t1 - Output: t1.unique1 + -> Seq Scan on public.tenk1 t1 + Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4 -> Hash Output: t2.unique1, t2.unique2 -> Seq Scan on public.onek t2 @@ -5288,7 +5289,7 @@ -> Seq Scan on int8_tbl c Filter: (q1 < b.unique1) -> Hash - -> Index Only Scan using onek_unique1 on onek a + -> Seq Scan on onek a (9 rows) select a.unique1, b.unique2 @@ -7412,14 +7413,15 @@ explain (costs off) select count(*) from tenk1 a, tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------------- Aggregate - -> Merge Join - Merge Cond: (a.unique1 = b.unique2) - -> Index Only Scan using tenk1_unique1 on tenk1 a - -> Index Only Scan using tenk1_unique2 on tenk1 b -(5 rows) + -> Hash Join + Hash Cond: (a.unique1 = b.unique2) + -> Seq Scan on tenk1 a + -> Hash + -> Seq Scan on tenk1 b +(6 rows) select count(*) from tenk1 a, tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; @@ -7437,7 +7439,7 @@ Aggregate -> Nested Loop -> Nested Loop - -> Index Only Scan using tenk1_unique1 on tenk1 a + -> Seq Scan on tenk1 a -> Values Scan on "*VALUES*" -> Memoize Cache Key: "*VALUES*".column1 @@ -8907,8 +8909,8 @@ where exists (select 1 from tenk1 t3 where t3.thousand = t1.unique1 and t3.tenthous = t2.hundred) and t1.unique1 < 1; - QUERY PLAN ---------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Output: t1.unique1, t2.hundred -> Hash Join @@ -8917,8 +8919,8 @@ -> HashAggregate Output: t3.thousand, t3.tenthous Group Key: t3.thousand, t3.tenthous - -> Index Only Scan using tenk1_thous_tenthous on public.tenk1 t3 - Output: t3.thousand, t3.tenthous + -> Seq Scan on public.tenk1 t3 + Output: t3.unique1, t3.unique2, t3.two, t3.four, t3.ten, t3.twenty, t3.hundred, t3.thousand, t3.twothousand, t3.fivethous, t3.tenthous, t3.odd, t3.even, t3.stringu1, t3.stringu2, t3.string4 -> Hash Output: t1.unique1 -> Index Only Scan using onek_unique1 on public.onek t1 diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/aggregates.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/aggregates.out --- /tmp/cirrus-ci-build/src/test/regress/expected/aggregates.out 2024-04-03 18:05:02.741546754 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/aggregates.out 2024-04-03 18:13:49.549139839 +0000 @@ -2942,13 +2942,13 @@ SET LOCAL parallel_setup_cost=0; SET LOCAL max_parallel_workers_per_gather=4; EXPLAIN (COSTS OFF) SELECT balk(hundred) FROM tenk1; - QUERY PLAN -------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate - -> Parallel Index Only Scan using tenk1_hundred on tenk1 + -> Parallel Seq Scan on tenk1 (5 rows) SELECT balk(hundred) FROM tenk1; diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/select_parallel.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/select_parallel.out --- /tmp/cirrus-ci-build/src/test/regress/expected/select_parallel.out 2024-04-03 18:05:02.817546727 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/select_parallel.out 2024-04-03 18:14:11.573110160 +0000 @@ -257,13 +257,13 @@ explain (costs off) select sum(sp_parallel_restricted(unique1)) from tenk1 group by(sp_parallel_restricted(unique1)); - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------- HashAggregate Group Key: sp_parallel_restricted(unique1) -> Gather Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_unique1 on tenk1 + -> Parallel Seq Scan on tenk1 (5 rows) -- test prepared statement @@ -608,17 +608,21 @@ set enable_nestloop to off; explain (costs off) select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1; - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate -> Merge Join Merge Cond: (tenk1.unique1 = tenk2.unique1) - -> Parallel Index Only Scan using tenk1_unique1 on tenk1 - -> Index Only Scan using tenk2_unique1 on tenk2 -(8 rows) + -> Sort + Sort Key: tenk1.unique1 + -> Parallel Seq Scan on tenk1 + -> Sort + Sort Key: tenk2.unique1 + -> Seq Scan on tenk2 +(12 rows) select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1; count @@ -847,13 +851,13 @@ -- check parallelized int8 aggregate (bug #14897) explain (costs off) select avg(unique1::int8) from tenk1; - QUERY PLAN -------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate - -> Parallel Index Only Scan using tenk1_unique1 on tenk1 + -> Parallel Seq Scan on tenk1 (5 rows) select avg(unique1::int8) from tenk1; @@ -1051,8 +1055,8 @@ explain (costs off, verbose) select count(*) from tenk1 a where (unique1, two) in (select unique1, row_number() over() from tenk1 b); - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------ Aggregate Output: count(*) -> Hash Semi Join @@ -1069,7 +1073,7 @@ -> Gather Output: b.unique1 Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b + -> Parallel Seq Scan on public.tenk1 b Output: b.unique1 (18 rows) @@ -1204,8 +1208,8 @@ SELECT unnest(ARRAY[]::integer[]) + 1 AS pathkey FROM tenk1 t1 JOIN tenk1 t2 ON TRUE ORDER BY pathkey; - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------------------------- Sort Output: (((unnest('{}'::integer[])) + 1)) Sort Key: (((unnest('{}'::integer[])) + 1)) @@ -1216,11 +1220,11 @@ -> Nested Loop -> Gather Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_hundred on public.tenk1 t1 + -> Parallel Seq Scan on public.tenk1 t1 -> Materialize -> Gather Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_hundred on public.tenk1 t2 + -> Parallel Seq Scan on public.tenk1 t2 (15 rows) -- test passing expanded-value representations to workers @@ -1255,13 +1259,13 @@ EXPLAIN (COSTS OFF) SELECT 1 FROM tenk1_vw_sec WHERE (SELECT sum(f1) FROM int4_tbl WHERE f1 < unique1) < 100; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Subquery Scan on tenk1_vw_sec Filter: ((SubPlan 1) < 100) -> Gather Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_unique1 on tenk1 + -> Parallel Seq Scan on tenk1 SubPlan 1 -> Aggregate -> Seq Scan on int4_tbl diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/window.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/window.out --- /tmp/cirrus-ci-build/src/test/regress/expected/window.out 2024-04-03 18:05:02.833546721 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/window.out 2024-04-03 18:14:14.973105578 +0000 @@ -5285,13 +5285,13 @@ FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous WHERE t2.two = 1 LIMIT 1; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Limit -> WindowAgg -> Hash Join Hash Cond: (t1.unique1 = t2.tenthous) - -> Index Only Scan using tenk1_unique1 on tenk1 t1 + -> Seq Scan on tenk1 t1 -> Hash -> Seq Scan on tenk1 t2 Filter: (two = 1) @@ -5303,17 +5303,18 @@ SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous LIMIT 1; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Limit -> WindowAgg - -> Merge Join - Merge Cond: (t1.unique1 = t2.tenthous) - -> Index Only Scan using tenk1_unique1 on tenk1 t1 - -> Sort - Sort Key: t2.tenthous - -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2 -(8 rows) + -> Sort + Sort Key: t1.unique1 + -> Hash Join + Hash Cond: (t1.unique1 = t2.tenthous) + -> Seq Scan on tenk1 t1 + -> Hash + -> Seq Scan on tenk1 t2 +(9 rows) -- Ensure we get a cheap total plan. This time use 10000 FOLLOWING so we need -- to read all join rows. @@ -5321,17 +5322,18 @@ SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND 10000 FOLLOWING) FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous LIMIT 1; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Limit -> WindowAgg - -> Merge Join - Merge Cond: (t1.unique1 = t2.tenthous) - -> Index Only Scan using tenk1_unique1 on tenk1 t1 - -> Sort - Sort Key: t2.tenthous - -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2 -(8 rows) + -> Sort + Sort Key: t1.unique1 + -> Hash Join + Hash Cond: (t1.unique1 = t2.tenthous) + -> Seq Scan on tenk1 t1 + -> Hash + -> Seq Scan on tenk1 t2 +(9 rows) -- Tests for problems with failure to walk or mutate expressions -- within window frame clauses. diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/rowtypes.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/rowtypes.out --- /tmp/cirrus-ci-build/src/test/regress/expected/rowtypes.out 2024-04-03 18:05:02.813546728 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/rowtypes.out 2024-04-03 18:14:20.297098403 +0000 @@ -302,11 +302,15 @@ select thousand, tenthous from tenk1 where (thousand, tenthous) >= (997, 5000) order by thousand, tenthous; - QUERY PLAN ------------------------------------------------------------ - Index Only Scan using tenk1_thous_tenthous on tenk1 - Index Cond: (ROW(thousand, tenthous) >= ROW(997, 5000)) -(2 rows) + QUERY PLAN +----------------------------------------------------------------------- + Sort + Sort Key: thousand, tenthous + -> Bitmap Heap Scan on tenk1 + Recheck Cond: (ROW(thousand, tenthous) >= ROW(997, 5000)) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (ROW(thousand, tenthous) >= ROW(997, 5000)) +(6 rows) select thousand, tenthous from tenk1 where (thousand, tenthous) >= (997, 5000) @@ -380,11 +384,15 @@ select thousand, tenthous from tenk1 where (998, 5000) < (thousand, tenthous) order by thousand, tenthous; - QUERY PLAN ----------------------------------------------------------- - Index Only Scan using tenk1_thous_tenthous on tenk1 - Index Cond: (ROW(thousand, tenthous) > ROW(998, 5000)) -(2 rows) + QUERY PLAN +---------------------------------------------------------------------- + Sort + Sort Key: thousand, tenthous + -> Bitmap Heap Scan on tenk1 + Filter: (ROW(998, 5000) < ROW(thousand, tenthous)) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (ROW(thousand, tenthous) > ROW(998, 5000)) +(6 rows) select thousand, tenthous from tenk1 where (998, 5000) < (thousand, tenthous) @@ -1132,20 +1140,22 @@ select row_to_json(q) from (select thousand, tenthous from tenk1 where thousand = 42 and tenthous < 2000 offset 0) q; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Subquery Scan on q - -> Index Only Scan using tenk1_thous_tenthous on tenk1 - Index Cond: ((thousand = 42) AND (tenthous < 2000)) -(3 rows) + -> Bitmap Heap Scan on tenk1 + Recheck Cond: ((thousand = 42) AND (tenthous < 2000)) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: ((thousand = 42) AND (tenthous < 2000)) +(5 rows) select row_to_json(q) from (select thousand, tenthous from tenk1 where thousand = 42 and tenthous < 2000 offset 0) q; row_to_json --------------------------------- - {"thousand":42,"tenthous":42} {"thousand":42,"tenthous":1042} + {"thousand":42,"tenthous":42} (2 rows) select row_to_json(q) from @@ -1153,8 +1163,8 @@ where thousand = 42 and tenthous < 2000 offset 0) q; row_to_json ------------------- - {"x":42,"y":42} {"x":42,"y":1042} + {"x":42,"y":42} (2 rows) select row_to_json(q) from @@ -1162,8 +1172,8 @@ where thousand = 42 and tenthous < 2000 offset 0) q(a,b); row_to_json ------------------- - {"a":42,"b":42} {"a":42,"b":1042} + {"a":42,"b":42} (2 rows) create temp table tt1 as select * from int8_tbl limit 2; diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/with.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/with.out --- /tmp/cirrus-ci-build/src/test/regress/expected/with.out 2024-04-03 18:05:02.837546720 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/with.out 2024-04-03 18:14:20.565098042 +0000 @@ -642,14 +642,14 @@ with x as materialized (select unique1 from tenk1 b) select count(*) from tenk1 a where unique1 in (select * from x); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------------- Aggregate CTE x - -> Index Only Scan using tenk1_unique1 on tenk1 b + -> Seq Scan on tenk1 b -> Hash Semi Join Hash Cond: (a.unique1 = x.unique1) - -> Index Only Scan using tenk1_unique1 on tenk1 a + -> Seq Scan on tenk1 a -> Hash -> CTE Scan on x (8 rows) @@ -678,16 +678,19 @@ with x as materialized (select unique1 from tenk1 b order by unique1) select count(*) from tenk1 a where unique1 in (select * from x); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------------- Aggregate CTE x - -> Index Only Scan using tenk1_unique1 on tenk1 b - -> Merge Semi Join - Merge Cond: (a.unique1 = x.unique1) - -> Index Only Scan using tenk1_unique1 on tenk1 a - -> CTE Scan on x -(7 rows) + -> Sort + Sort Key: b.unique1 + -> Seq Scan on tenk1 b + -> Hash Semi Join + Hash Cond: (a.unique1 = x.unique1) + -> Seq Scan on tenk1 a + -> Hash + -> CTE Scan on x +(10 rows) -- SEARCH clause create temp table graph0( f int, t int, label text ); === EOF === [18:14:30.812](68.249s) not ok 2 - regression tests pass [18:14:30.812](0.000s) # Failed test 'regression tests pass' # at /tmp/cirrus-ci-build/src/test/recovery/t/027_stream_regress.pl line 95. [18:14:30.813](0.000s) # got: '256' # expected: '0' 1 1 1 2 1 1 1 9 5 5 5 3 4 3 4 4 1 32 1 1 1 6 104 2 1 1006 4001 1 2 41 -2 5 17 9 1 33 34 1 1 1 1 1 1 -1 1 1 -1 -32768 32767 46 Waiting for replication conn standby_1's replay_lsn to pass 0/13E79C08 on primary done # Running: pg_dumpall -f /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/primary.dump --no-sync -p 56473 --no-unlogged-table-data [18:14:34.583](3.770s) ok 3 - dump primary server # Running: pg_dumpall -f /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/standby.dump --no-sync -p 56474 [18:14:39.731](5.149s) ok 4 - dump standby server # Running: diff /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/primary.dump /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/standby.dump [18:14:39.778](0.047s) ok 5 - compare primary and standby dumps [18:14:40.108](0.329s) ok 6 - check contents of pg_stat_statements on regression database ### Stopping node "standby_1" using mode fast # Running: pg_ctl -D /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_standby_1_data/pgdata -m fast stop waiting for server to shut down.... done server stopped # No postmaster PID for node "standby_1" ### Stopping node "primary" using mode fast # Running: pg_ctl -D /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/t_027_stream_regress_primary_data/pgdata -m fast stop waiting for server to shut down.... done server stopped # No postmaster PID for node "primary" [18:14:40.327](0.219s) 1..6 [18:14:40.329](0.002s) # Looks like you failed 1 test of 6.