Re: Support worker_spi to execute the function dynamically.

On 2023-Jul-28, Michael Paquier wrote:

> So you have faced a race condition where the commit of the transaction
> doing the schema creation for the static workers is delayed long
> enough that the dynamic workers don't see it, and bumped on a catalog
> conflict when they try to create the same schemas.
>
> Having each bgworker on its own schema would be enough to prevent
> conflicts, but I'd like to add a second thing: a check on
> pg_stat_activity.wait_event after starting the workers. I have added
> something like that in the patch I have posted today for the custom
> wait events at [1] and it enforces the startup sequences of the
> workers in a stricter way.

Hmm, I think having all the workers doing their in the same table is
better -- if nothing else, because it gives us the opportunity to show
how to use some other coding technique (but also because we are forced
to write the SQL code in a way that's correct for potentially multiple
concurrent workers, which sounds useful to demonstrate). Can't we
instead solve the race condition by having some shared resource that
blocks the other workers from proceeding until the schema has been
created? Perhaps an LWLock, or a condition variable, or an advisory
lock.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/