Re: LWLock SerializableFinishedList
On Fri, Sep 5, 2025 at 1:02 PM Alec Cozens wrote: > Hi > > > > I’m having trouble with PostgreSQL 16.8 on Windows where for maybe days it > all works perfectly until the number of active connections start > increasing, until over say 10 minutes all 97 connections are active but > seemingly waiting on LWLock on SerializableFinishedList. They will remain > in this locked state for some arbitrary period, up to 1 hour 40 minutes, > after which the connections will all clear apparently simultaneously and > the application continues. > > > > The connections are opened, a few command executed and then closed and > returned to the connection pool. > > > > The application runs on the same server as the postgresql service. > > > > Changing statement_timeout to 1 minute doesn’t seem to cancel these > “active” connections. > > > > Most of the application runs in READ COMMITTED isolation level, but the > particular stored procedure that seems to cause the issue runs in > SERIALIZED. We end up with “active” but hanging connections running this > stored procedure or the associated commands generated by npgqql associated > with opening, closing and returning connections to the pool. > > > > I can’t find any information about SerializeableFinishedList or why all > the standard timeout parameters seem to have no effect on the cancellation > of these hung connections. The npgsql client configuration talks about > clearing idle connections, but these are not idle, they are active but hung. > > > > Any thoughts on the matter, or what might cause a lock on > SerializableFinishedList would be much appreciated. > > > > Regards, > > Alec > > I am betting all the waiting sessions are waiting on a COMMIT from another session. This means all the other sessions are dependent on row(s) locked/updated by the first session. The other sessions have to wait to know if they have a SERIALIZATION conflict. The reason statement_timeout is not working is because all the queries executed and completed. Without reviewing the code and logic based on the description of events I am betting all the sessions got to the COMMIT stage and are waiting on another Session. Would be nice to know what pg_locks showed. Do you have lock_timeout set? Sharing the code of the function and what the other sessions are doing, this way we can attempt to duplicate this behavior . Keep in mind Serializing transactions have quirky behavior like this , the transaction has to validate that no other update or insert is going to cause a problem with the result. Thanks Justin
Re: PostgreSQL include directive in plpgsql language PL/pgSQL
On Fri, Sep 5, 2025 at 7:14 AM PALAYRET Jacques wrote: > Hello, > > In a PL/pgSQL function, there is no command for sharing a common part of > the body of several functions, is there? > > In my case, I would like a function that returns a numeric value; this > value is associated with several other parameters, some numeric and others > textual. > It would not be practical for maintenance to have several functions (one > function for each given parameter) with much of the code being identical. > > Presently, I have a text function with all the parameters in return and a > function for each parameter based on the first one (with all the > parameters), using substr(), position(), etc. > For me, I think it would be easier (and perhaps faster) to use some kind > of #include in the body of all the functions. > Can you provide some examples of what you are trying to accomplish? There may be some workarounds using immutable functions other tricks, but I'm not 100% sure I have my head around your issue. merlin
LWLock SerializableFinishedList
Hi I'm having trouble with PostgreSQL 16.8 on Windows where for maybe days it all works perfectly until the number of active connections start increasing, until over say 10 minutes all 97 connections are active but seemingly waiting on LWLock on SerializableFinishedList. They will remain in this locked state for some arbitrary period, up to 1 hour 40 minutes, after which the connections will all clear apparently simultaneously and the application continues. The connections are opened, a few command executed and then closed and returned to the connection pool. The application runs on the same server as the postgresql service. Changing statement_timeout to 1 minute doesn't seem to cancel these "active" connections. Most of the application runs in READ COMMITTED isolation level, but the particular stored procedure that seems to cause the issue runs in SERIALIZED. We end up with "active" but hanging connections running this stored procedure or the associated commands generated by npgqql associated with opening, closing and returning connections to the pool. I can't find any information about SerializeableFinishedList or why all the standard timeout parameters seem to have no effect on the cancellation of these hung connections. The npgsql client configuration talks about clearing idle connections, but these are not idle, they are active but hung. Any thoughts on the matter, or what might cause a lock on SerializableFinishedList would be much appreciated. Regards, Alec __ This email has been scanned by the Symantec Email Security.cloud service. For more information please visit http://www.symanteccloud.com __
Re: PostgreSQL include directive in plpgsql language PL/pgSQL
"David G. Johnston" writes: > On Friday, September 5, 2025, PALAYRET Jacques > wrote: >> Is there an equivalent of #include (include directive) in functions >> (written in plpgsql language)? > No, there is not. And there won't be one in the future either, as it would give server filesystem access to unprivileged SQL users. (Admittedly just read-only access, but that's still not OK.) Conceivably you could build some such facility into your client-side code that is issuing CREATE FUNCTION commands. regards, tom lane
PostgreSQL include directive in plpgsql language PL/pgSQL
Hello, In a PL/pgSQL function, there is no command for sharing a common part of the body of several functions, is there? In my case, I would like a function that returns a numeric value; this value is associated with several other parameters, some numeric and others textual. It would not be practical for maintenance to have several functions (one function for each given parameter) with much of the code being identical. Presently, I have a text function with all the parameters in return and a function for each parameter based on the first one (with all the parameters), using substr(), position(), etc. For me, I think it would be easier (and perhaps faster) to use some kind of #include in the body of all the functions. Is there an equivalent of #include (include directive) in functions (written in plpgsql language)? Regards - Météo-France - PALAYRET Jacques
Re: PostgreSQL include directive in plpgsql language PL/pgSQL
On Friday, September 5, 2025, PALAYRET Jacques wrote: > > > Is there an equivalent of #include (include directive) in functions > (written in plpgsql language)? > No, there is not. David J.
Re: PostgreSQL include directive in plpgsql language PL/pgSQL
On Fri, Sep 5, 2025 at 9:14 AM PALAYRET Jacques wrote: > Hello, > > In a PL/pgSQL function, there is no command for sharing a common part of > the body of several functions, is there? > > In my case, I would like a function that returns a numeric value; this > value is associated with several other parameters, some numeric and others > textual. > It would not be practical for maintenance to have several functions (one > function for each given parameter) with much of the code being identical. > > Presently, I have a text function with all the parameters in return and a > function for each parameter based on the first one (with all the > parameters), using substr(), position(), etc. > What about something like a "parent" function which a bunch of "stub" functions call? The stub functions would have different parameter types, know what to call the parent function with, and how to return the correct number value. Because of name overloading, they might even have the same name. -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: Latest patches break one of our unit-test, related to RLS
On Fri, Sep 5, 2025 at 1:10 AM Ron Johnson wrote: > Dumb question: did you read the release notes? Well, now I have. Carefully. Nothing stands out. There's RLS mentioned, but for indexing only. So no smoking gun. We'll have to investigate... :( > In our case, 17.6 "pg_dump --schema-only" broke in the sense that the md5sum > of yesterday's schema dump is different from today's schema dump. Puzzled, I > ran "pg_dump --schema-only" twice in a row, but the md5sum was different. > "diff" showed why. Searching the release notes showed me what changed. Thanks for the heads up. We don't depend on pg_dump at all. We have our own COPY BINARY based custom backup/restore to semi-structured SQLite DBs (part relational, part per-row COPY blobs).
