[PG19] Bugs found comparing 19 with 18, list before I open threads

Hi hackers,

I compared 19 with 18 over the last two weeks and have a list of bugs
new in 19. I plan to report them one thread per root cause, with a repro
and a patch each.

I am posting the list first so people can correct my severities or tell me
something is already known.I will open the threads later from High to Low.
If the original author prefers to fix it by yourself, please let me know.

Every item reproduces on REL_19_STABLE at 0b8bff042a9 (19rc1) on both
macOS and Linux, and 18.6 gives the right answer in each case.

High

1. Join removal restarts planning and the aggregate "presorted" flag
from the first pass stays set. A self join on a primary key with several
DISTINCT or ORDER BY aggregates returns wrong counts, 29 where the
answer is 3. Also on the REL_18_STABLE head, 18.6 does not have it. From
2ebf25e7d70. The same restart also leaves a removed rel's TABLESAMPLE
behind, which trips an assert.

2. jsonb_object_agg as a window function returns the value from an
earlier row. With 8 keys over 120 rows about 100 rows are wrong. From
b61aa76e458.

3. Eager aggregation pushes a partial aggregate below a join whose qual
has a volatile function. WHERE random() < 0.5 then keeps or drops whole
groups. 3a08a2a8b4f only covered volatile functions inside the
aggregates.

Medium

4. btree_gin, a timestamp column compared with a timestamptz value. The
index scan misses rows in the repeated hour at the DST fall back.

5. pg_dump --statistics writes extended statistics keyed by attnum.
After a dropped column, or on a child with a different column order,
restore drops them or attaches them to the wrong columns. Hard to change
after release. From c32fb29e979.

6. psql with a non English lc_messages and a client encoding other than
UTF-8. \d, \dt, \l and most backslash commands fail with an encoding
error, because the new query labels are translated. From 41d69e6dcca.

7. JSON_ARRAY and JSON_OBJECT with a ROW argument are treated as
immutable. With a timestamptz in the row an expression index misses rows
after a timezone change and a unique index accepts duplicates. From
ecd9288624a. Robert reported the immutability part in [1], I will reply
there.

8. Standby promotion waits until every process has accepted a
ProcSignalBarrier, even with wal_level=replica and no slots. A backend
blocked sending a result to a client does not accept it. With a client
that stopped reading, promotion hung in 8 of 8 runs on Linux. The one
run I left alone promoted after 949 seconds, when TCP gave up. 18.6
promotes in 0.1 seconds. Terminating that backend gets out of it. From
67c20979ce7.

9. Text search and pg_trgm in databases with the ICU or builtin provider
and LC_CTYPE C. 19 parses non ASCII text differently, for example it
splits a word at an em dash. After pg_upgrade the old GIN and GiST
indexes on to_tsvector() or pg_trgm miss rows that 18.6 found, and a
stored tsvector column *stays wrong after REINDEX.* Nothing warns about
it. From e113f9c102b. My patch makes pg_upgrade list the indexes and
write a reindex script.

Low, titles only

1. ILIKE with a libc UTF-8 collation, an index scan returns fewer rows than
a seq scan for 111 code points on macOS. Same cause as b264391a846, not
fixed by it.

2. CREATE SCHEMA, an index or trigger lands on a temp table of the same
name, still there after e0fdc3f54b4.

3. LISTEN, a listener cancelled while waiting for a safe snapshot keeps its
queue position forever and the queue fills up. From 282b1cde9de.

4. COPY FROM STDIN holds back a complete line of 17 bytes or more until
more data arrives. From e0a3a3fd536, still there after 50ba390a4f8.

This list is only about 19. Bugs that released branches also have will
come in a separate list later. Release note gaps go to Bruce separately.

[1]
https://postgr.es/m/CA+TgmoaiothgQrw9OtgsMzBUCnqJ2jdaGTbS6o3fkjCd+LfzWw@mail.gmail.com

Thanks,
Shihao