Re: Exposing the lock manager's WaitForLockers() to SQL

Hi Laurenz, thanks for taking a look!

On Sat, Jan 6, 2024 at 4:00 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> While your original use case is valid, I cannot think of
> any other use case.  So it is a special-purpose statement that is only
> useful for certain processing of append-only tables.

It is definitely somewhat niche. :-) But as I mentioned in my
longwinded original message, the scheme is easily extended (with some
tradeoffs) to process updates, if they set a non-primary-key column
using a sequence. As for deletions though, our applications handle
them separately.

> Is it worth creating a new SQL statement for that, which could lead to
> a conflict with future editions of the SQL standard?  Couldn't we follow
> the PostgreSQL idiosyncrasy of providing a function with side effects
> instead?

I would be happy to add a pg_foo() function instead. Here are a few
things to figure out:

* To support waiting for lockers in a specified mode vs. conflicting
with a specified mode, should there be two functions, or one function
with a boolean argument like I used in C?

* Presumably the function(s) would take a regclass[] argument?

* Presumably the lock mode would be specified using strings like
'ShareLock'? There's no code to parse these AFAICT, but we could add
it.

* Maybe we could omit LOCK's handling of descendant tables for
simplicity? I will have to see how much other code needs to be
duplicated or shared.

I'll look further into it later this week.