Re: Long running query causing XID limit breach

2024-05-26 Thread sud
On Mon, May 27, 2024 at 12:55 AM Torsten Förtsch wrote: > On Sun, May 26, 2024 at 8:46 PM sud wrote: > >> Would you agree that we should have two standby, one with default >> max_standby_streaming_delay (say 10 sec ) which will be mainly used as high >> availability and thus will be having minim

Re: Long running query causing XID limit breach

2024-05-26 Thread Torsten Förtsch
On Sun, May 26, 2024 at 8:46 PM sud wrote: > Would you agree that we should have two standby, one with default > max_standby_streaming_delay (say 10 sec ) which will be mainly used as high > availability and thus will be having minimal lag. and another standby with > max_standby_streaming_delay a

Re: Long running query causing XID limit breach

2024-05-26 Thread sud
On Sun, May 26, 2024 at 11:18 PM Torsten Förtsch wrote: > Each query on the replica has a backend_xmin. You can see that in > pg_stat_activity. From that backend's perspective, tuples marked as deleted > by any transaction greater or equal to backend_xmin are still needed. This > does not depend

Re: Long running query causing XID limit breach

2024-05-26 Thread Torsten Förtsch
On Sun, May 26, 2024 at 11:16 AM sud wrote: > On Sun, May 26, 2024 at 1:43 PM Torsten Förtsch > wrote: > >> On Sat, May 25, 2024 at 11:00 PM sud wrote: >> >>> >>> But i have one question here , does max_standby_streaming_delay = 14 , >>> means the queries on the standby will get cancelled after

Re: Long running query causing XID limit breach

2024-05-26 Thread Adrian Klaver
On 5/25/24 22:56, David HJ wrote: anyone know how to describe from this mailing list? See here: https://lists.postgresql.org/unsubscribe/ -- Adrian Klaver adrian.kla...@aklaver.com

Re: scalar plpgsql functions and their stability flags

2024-05-26 Thread Tom Lane
Victor Dobrovolsky writes: > It is clear that for immutable/stable functions with constant parameters, > query planner could/should calculate value in a parse time and use it > directly in query, or at least once per query. Immutable, yes, stable, no. Awhile back there was a draft patch to cach

scalar plpgsql functions and their stability flags

2024-05-26 Thread Victor Dobrovolsky
Good day experts... Question on scalar plpgsql functions stability flags (immutable, stable) regarding how it works in sql queries. It is clear that for immutable/stable functions with constant parameters, query planner could/should calculate value in a parse time and use it directly in query, o

Re: Long running query causing XID limit breach

2024-05-26 Thread sud
On Sun, May 26, 2024 at 1:43 PM Torsten Förtsch wrote: > On Sat, May 25, 2024 at 11:00 PM sud wrote: > >> >> But i have one question here , does max_standby_streaming_delay = 14 , >> means the queries on the standby will get cancelled after 14 seconds? >> > > No, your query gets cancelled when i

Re: Long running query causing XID limit breach

2024-05-26 Thread Torsten Förtsch
On Sat, May 25, 2024 at 11:00 PM sud wrote: > > But i have one question here , does max_standby_streaming_delay = 14 , > means the queries on the standby will get cancelled after 14 seconds? > No, your query gets cancelled when it stalls replication for >14 sec. If your master is idle and does n