Re: LWLock SerializableFinishedList

On Mon, Sep 8, 2025 at 12:04 PM Alec Cozens <acozens(at)pixelpower(dot)com> wrote:

> Looking at the postgresql code, the LW lock SerializableFinishedList
> appears to be acquired and then released, usually in the duration of a call
> to a procedure. Base on that (admittedly maybe faulty) view of the code, I
> am surprised to see the lock held open for over and hour and a half.
>
>
>
>
>
Hi Alec,

Clarification Lock_timeout and Statement_timeout work the same way, they
just differ in what triggers them. Lock timeout is triggered when a lock
can not be acquired in the specified time throwing an error.
Statement_Timeout is triggered when any specific command/query can not be
executed in the specific time.

the error message

could not truncate directory "pg_serial": apparent wraparound

This is concerning: do you have any replicas?? There is a known issue
when hot_standby_feedback = on and replica has long running transaction
running, SLRU will overflow causing PostgreSQL to go to a crawl. Only after
the replica releases whatever locks it has does performance return to
normal. This has been mitigated in PostgreSQL 17 with these configurable
settings
https://www.postgresql.org/docs/devel/runtime-config-resource.html#GUC-MULTIXACT-MEMBER-BUFFERS

I have no idea if SLRU directly affects serialized transactions, I would
suspect that it does as SLRU affects all transactions and if it overflows
bad things start happening. The next time this happen look at the replica
to see if there are any long running transactions

All the conditions I can think of that can cause this is a transaction
sitting out there holding onto an XID causing the serialized transactions
to wait

Thank you
Justin