Re: pg_upgrade and logical replication

On Tue, Jul 23, 2024 at 4:33 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Mon, Jul 22, 2024 at 09:46:29AM -0500, Nathan Bossart wrote:
> > On Mon, Jul 22, 2024 at 03:45:19PM +0530, Amit Kapila wrote:
> >>
> >> Unlike subscriptions, logical slots are database-specific objects. We
> >> have some checks in the code like the one in CreateDecodingContext()
> >> for MyDatabaseId which may or may not create a problem for this case
> >> as we don't consume changes when checking
> >> LogicalReplicationSlotHasPendingWal via
> >> binary_upgrade_logical_slot_has_caught_up() but I think this needs
> >> more analysis than what Nathan has proposed. So, I suggest taking up
> >> this task for PG18 if we want to optimize this code path.
> >
> > I see what you mean.
>
> I am not sure to get the reason why get_old_cluster_logical_slot_infos()
> could not be optimized, TBH.  LogicalReplicationSlotHasPendingWal()
> uses the fast forward mode where no changes are generated, hence there
> should be no need for a dependency to a connection to a specific
> database :)
>
> Combined to a hash table based on the database name and/or OID to know
> to which dbinfo to attach the information of a slot, then it should be
> possible to use one query, making the slot info gathering closer to
> O(N) rather than the current O(N^2).
>

The point is that unlike subscriptions logical slots are not
cluster-level objects. So, this needs more careful design decisions
rather than a fix-up patch for PG-17. One more thing after collecting
slot-level, we also want to consider the creation of slots which again
are created at per-database level.

-- 
With Regards,
Amit Kapila.