Summary:
Port PG partition tests. There weren't many YB changes from the previous version, mainly just commented out queries. Many of those queries can be uncommented, because their plan matches the Postgres plan. `partition_prune.sql` had some BNL cases added. Those are left in.
This diff is easiest to review by patching it and diffing the `*.sql` files:
```
diff src/postgres/src/test/regress/sql/partition_aggregate.sql src/postgres/src/test/regress/sql/yb.port.partition_aggregate.sql
diff src/postgres/src/test/regress/sql/partition_info.sql src/postgres/src/test/regress/sql/yb.port.partition_info.sql
diff src/postgres/src/test/regress/sql/partition_join.sql src/postgres/src/test/regress/sql/yb.port.partition_join.sql
diff src/postgres/src/test/regress/sql/partition_prune.sql src/postgres/src/test/regress/sql/yb.port.partition_prune.sql
```
and diffing the `*.out` files:
```
diff src/postgres/src/test/regress/expected/partition_aggregate.out src/postgres/src/test/regress/expected/yb.port.partition_aggregate.out
diff src/postgres/src/test/regress/expected/partition_info.out src/postgres/src/test/regress/expected/yb.port.partition_info.out
diff src/postgres/src/test/regress/expected/partition_join.out src/postgres/src/test/regress/expected/yb.port.partition_join.out
diff src/postgres/src/test/regress/expected/partition_prune.out src/postgres/src/test/regress/expected/yb.port.partition_prune.out
```
Explanations of the changes in the `*.out` files are in the test plan.
This diff also modifies the linter to allow `\set VERBOSITY` new lines in yb.port.* tests, because `--` comments are not allowed after a `\set` command. These lines are required because `DROP TABLE CASCADE` can log the objects it cascades to in a non-stable order.
Jira: DB-15773
Test Plan:
Jenkins: test regex: .*PgRegressPartitions.*
```
./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressPartitions' -n 20
```
-------
=== Comparing PG's output to YB's output ===
For some of the diffs, it's easier to apply the following transformations to both sides:
```lang=sh
sed -i '' 's/Storage Filter/Filter/' <filename>
sed -i '' -E 's/^-----+$/-----------/' <filename> # keep the number of -'s in the explain header consistent
```
This will remove basic common differences and make it easier to find the "real" differences.
-------
==== `partition_aggregate` ==
```
diff src/postgres/src/test/regress/expected/partition_aggregate.out src/postgres/src/test/regress/expected/yb.port.partition_aggregate.out
```
Shows differences:
* HashAggregate changed to GroupAggregate on sorted input. This doesn't significantly impact the plan, so it's not a big deal.
* Small changes like `(t2_2.y = t1_2.x)` -> ` (t1_2.x = t2_2.y)` parameters in several explain plans. These seem stable and will likely continue, as CBO is off in these tests and RBO is generally not changing.
* `Filter` replaced by `Storage Filter`
* Parallel plans commented out, because they're harder to force without more changes.
* New output lines `NOTICE: storage parameters are currently ignored in YugabyteDB` after commands like `ALTER TABLE pagg_tab_para_p2 SET (parallel_workers = 0);`.
-------
==== `partition_info` ==
```
diff src/postgres/src/test/regress/expected/partition_info.out src/postgres/src/test/regress/expected/yb.port.partition_info.out
```
Shows differences:
* `CREATE TABLE INHERITS` is not supported in Yugabyte, so the table creation fails. Subsequent uses of that table are commented out.
-------
==== `partition_join` ==
```
diff src/postgres/src/test/regress/expected/partition_join.out src/postgres/src/test/regress/expected/yb.port.partition_join.out
```
shows differences:
* `Filter` replaced by `Storage Filter`
* Some explain plans commented out because they don't show what PG shows.
* `set enable_indexscan = off; -- YB: force bitmap scan` to encourage YB to use bitmap scans too.
-------
==== `partition_prune` ==
```
diff src/postgres/src/test/regress/expected/partition_prune.out src/postgres/src/test/regress/expected/yb.port.partition_prune.out
```
Shows differences:
* `Filter` replaced by `Storage Filter`
* `Aggregate` replaced by `Finalize Aggregate`
* In YB, we create `part_hashint4_noop` and `part_hashtext_length` because the alternative is to run `yb.port.test_setup.sql` first, which includes a lot of tables that aren't necessary. This was included before this diff as well.
* FETCH BACKWARD is not supported, so `FETCH BACKWARD` followed by another fetch is commented out.
* Parallel plans are skipped, because they're harder to force without more changes. Rather than adjusting every parallel plan, `explain_parallel_append` is modified to skip the output.
* `Bitmap Heap Scan` replaced by `YB Bitmap Table scan` (and some surrounding SET flags that encouraged bitmap scans).
* Merge Append replaced by Sorting.
* Change index on `ma_test (b)` and `rangep (a)` to `ASC` to allow tests to use the indexes.
* `CREATE TABLE INHERITS` is not supported in Yugabyte, so the table creation fails. Subsequent uses of that table are commented out.
* Wrapped `DROP TABLE CASCADE` with `\set VERBOSITY terse` to avoid printing unordered table names
* There's a small difference in behaviour in not executing a plan node. Tracked by #26410 and the issue is added as a comment.
Reviewers: patnaik.balivada
Reviewed By: patnaik.balivada
Subscribers: patnaik.balivada, jason, yql
Differential Revision: https://phorge.dev.yugabyte.com/D42527
