Allow a promotion request to supply the new timeline ID

Hi hackers,

Recently we had several availability-zone power failures. Promotion was
the largest contributor to read-only time for our PostgreSQL clusters.
One recurring stack was:

StartupXLOG
findNewestTimeLine
existsTimeLineHistory
RestoreArchivedFile
system("... wal-fetch 00000024.history ... --timeout 60s")

I would like the availability of the archive not to determine how long
it takes to choose a new timeline. The cluster manager has already
decided which server may promote, typically using a DCS. It could allocate
the timeline ID there too, and pass it to PostgreSQL.

Related discussions include Mats's work on detecting diverging
timelines [0] and Kostja's built-in Raft proposal [1]. Mats also supported
an allocation API or hook. The interface proposed here would be useful
independently of either project.

Attached is a draft of the SQL interface:

SELECT pg_promote(true, 60, new_timeline => 36);

The existing pg_promote() and pg_ctl promote still allocate the ID
automatically. The new overload takes a bigint to cover the unsigned
32-bit timeline range, after the existing wait and wait_seconds arguments.
All three are required in this form, so old calls, including untyped
string literals, keep resolving as before. Its EXECUTE privilege is
separate from the existing overload.

The allocator must issue globally unique, strictly increasing IDs for
this database system, preserving its high-water mark across restarts.
Reserved IDs must not be reused after failed promotion attempts, and
ordinary promotions outside the allocator must also be accounted for.
PostgreSQL can check local WAL and history files and require an ID above
the recovery target, but cannot enforce the global allocation order.
An ordinary restore_command failure followed by a missing local file
cannot distinguish an absent history from an unavailable archive. The
existing probing mechanism is not an atomic allocator either.

There is a second archive access to avoid: writeTimeLineHistory() fetches
the parent's history again. With an explicit ID, the draft uses the local
history already obtained during recovery, and refuses promotion if it
is missing. Timeline 1 needs no history file. The new history is queued
for archiving as usual; promotion does not wait for archive_command.

This does not interrupt a restore_command already running or change the
policy of replaying available WAL before promotion. Those can still
delay promotion. The change removes archive accesses for allocating the
ID and copying the parent history, not every archive dependency.

Some interface questions:

* The draft keeps the existing numeric ordering for latest, along with
the ancestry and replay-position checks. The highest discovered ID need
not identify the timeline the current primary is writing to. I don't
see a better interpretation within the existing interface, though.
Increasing allocation preserves that ordering, but does not solve
discovery: allocating 36 and failing before creating its history can
leave a gap before 37. The draft handles gaps in local histories and
streaming, but archive-only recovery through restore_command needs an
explicit target to cross a gap. Is requiring an explicit
recovery_target_timeline acceptable in this case, or must latest
discover timelines across gaps too?

* The draft keeps requests in shared memory. The first accepted choice
cannot be replaced by a concurrent request; timeout or client disconnect
does not cancel it. A server restart loses an uncompleted request, so
the coordinator must inspect the resulting state before deciding what
to do next. Is a SQL overload sufficient for the first patch, or should
pg_ctl promote accept a timeline ID too?

* The draft accepts the full uint32 range and fixes signed parsing in
streaming clients. Some SQL monitoring functions still expose TLI
fields as integer, though. The bigint argument allows us to start with
a narrower range and extend it later without changing the signature.
Should we widen those monitoring fields before allowing externally
allocated IDs above INT32_MAX, or initially limit the accepted IDs to
INT32_MAX?

Using this API with globally unique IDs for every promotion prevents
the timeline-ID collisions that Mats's UUID check would detect. The UUID
check would still be useful for uncoordinated promotions. This API does
not provide fencing.

Thank you!

Best regards, Andrey Borodin.

[0] https://www.postgresql.org/message-id/5407e386-3d61-4712-99af-9d0b29fab3b2%40gmail.com
[1] https://www.postgresql.org/message-id/Z_1Cq7JvabsFYjQo%40ark