Re: REPACK (CONCURRENTLY): do not block the table while waiting for the final lock

shihao zhong <zhong950419(at)gmail(dot)com> wrote:

> Subject: REPACK (CONCURRENTLY): do not block the table while waiting for the final lock

[ I assume this is not meant for v19, is it? ]

>
> REPACK (CONCURRENTLY) requests AccessExclusiveLock at the end to swap
> the files. While it is queued for that lock behind a long running
> transaction, every new query on the table queues behind it. On master I
> held AccessShareLock in one session for 15 seconds, and a single row
> SELECT that arrived while REPACK was waiting took 14.7 seconds. The
> usual defense, lock_timeout, makes it worse here. With lock_timeout = 3s
> the REPACK fails after all the copying is done.
>
> The attached patch makes REPACK stop queueing for that lock. It checks
> whether the lock is available. While it is not, it applies the changes
> that arrived meanwhile and checks again every 50 ms, the way
> lazy_truncate_heap() does. With the patch the same SELECT took 0.9
> seconds, and REPACK finished once the long transaction ended.
> lock_timeout limits how long it keeps trying, so its meaning does not
> change.
>
> The cost is on the REPACK side. A conditional request only succeeds when
> nobody holds a lock at that moment, so on a busy table it can take a
> while. With 16 pgbench clients doing single row SELECTs on the table
> (about 135k tps), REPACK needed 1 to 10 seconds to get the lock, against
> 0.5 seconds when queueing. After hours of copying I think that is fine.
>
> There is a variant with a lock manager change that queues for
> deadlock_timeout at a time and then leaves the queue.

Waiting for a limited time would make more sense, but why exactly
deadlock_timeout should control that?

Attached is my proposal to restrict the wait time, w/o hacking the lock
manager. Note that it deliberately does not teach REPACK to give up. Unlike
(lazy) VACUUM, all the work is rolled back if REPACK ends with ERROR.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com