Re: Transaction timeout - Mailing list pgsql-hackers

From Nikolay Samokhvalov
Subject Re: Transaction timeout
Date
Msg-id CANNMO+JYA_FdO5SFayD1HxiFeioVdcx-TZSGyyDdQ0x5g6-1mw@mail.gmail.com
Whole thread Raw
In response to Re: Transaction timeout  (Andrey Borodin <amborodin86@gmail.com>)
List pgsql-hackers
On Fri, Jan 13, 2023 at 10:16 AM Andrey Borodin <amborodin86@gmail.com> wrote:
> – it seems we could (should) have one more successful "1s wait, 3s sleep" iteration here, ~727ms somehow wasted in a loop, quite a lot.

I think big chunk from these 727ms were spent between "BEGIN" and
"select now(), clock_timestamp(), pg_sleep(3) \watch 1". 

Not really – there was indeed ~2s delay between BEGIN and the first pg_sleep query, but those ~727ms is something else.
 
here we measure the remainder between the beginning of the transaction measured by "now()' and the the beginning of the last successful pg_sleep() query:

gitpod=# select timestamptz '2023-01-13 15:51:18.179579+00' - '2023-01-13 15:49:22.906924+00';
    ?column?    
-----------------
 00:01:55.272655
(1 row)

It already includes all delays that we had from the beginning of our transaction.

The problem with my question was that I didn't take into attention that '2023-01-13 15:51:18.179579+00' is when the last successful query *started*. So the remainder of our 2-min quota – 00:00:04.727345 – includes the last successful loop (3s of successful query + 1s of waiting), and then we have failed after ~700ms.

In other words, there are no issues here, all good.

> Many thanks for looking into this!

many thanks for implementing it

pgsql-hackers by date:

Previous
From: Jacob Champion
Date:
Subject: Re: [EXTERNAL] Re: Support load balancing in libpq
Next
From: Justin Pryzby
Date:
Subject: Re: properly sizing attnums in pg_get_publication_tables