Hi Justin, Thanks for the clarification. There were no parallel sessions while we deleted those records.
It was a single session and a single transaction only. If the transaction was aborted for any reason, like ERROR or FATAL (I assume the connection will close or reset, starting a new session), then the previous state of the transaction and session will also be rolled back, right? This is the behaviour we regularly see. However, as you see in the reported cases above, this rollback was not occurring. After FATAL, We started a new transaction, It executed the previous transaction's statement (which was aborted due to idle timeout) as a single statement (with autocommit). Is this the correct behaviour? No Right ? Thank you. On Sat, Sep 5, 2026 at 4:41 AM Justin <[email protected]> wrote: > > > On Fri, Sep 4, 2026 at 4:24 PM dinesh kumar <[email protected]> > wrote: > >> During one of our recent database maintenance windows, we found strange >> behaviour. >> >> rupeecom=*> SELECT id FROM owuhhrlb.orders order by order_time desc limit >> 10; >> >> id >> >> ------------ >> >> 2867948821 >> >> 6692427082 <(669)%20242-7082> >> >> 5676641762 <(567)%20664-1762> >> >> 2129769831 <(212)%20976-9831> >> >> 9491447513 >> >> 9208637522 <(920)%20863-7522> >> >> 7331803655 >> >> 1763525242 >> >> 9609866278 >> >> 0442784132 >> >> (10 rows) >> >> >> *rupeecom=*> DELETE FROM owuhhrlb.orders WHERE id IN (SELECT id FROM >> owuhhrlb.orders order by order_time desc limit 10); * >> >> *FATAL: idle transaction timeout* >> >> *rupeecom=> * >> >> *rupeecom=> BEGIN WORK;* >> >> *DELETE 10* >> >> >> >> As you see above, after the transaction terminated, the subsequent BEGIN >> WORK; statement committed the previous transaction. We are using PostgreSQL >> 16.14, also we are using pgCat as connection pooler ... >> >> > Hi Dinesh, > > I do not think this is the complete SQL for the parallel transactions, or > even that these are parallel transactions. There is not enough detail to > say anything concrete. > > This behavior is typically seen when: > > - One transaction sits idle for a period of time, waiting on the > client to send a command or another event that leaves the transaction idle. > - The idle transaction is then terminated by the > idle_in_transaction_session_timeout setting. > - After that, the DELETE becomes unblocked and deletes the 10 records. > > If that guess is correct, this is expected behavior. > > We need all of the SQL from the first transaction and from the transaction > that was blocked. Thank you > -- Regards, Dinesh manojadinesh.blogspot.com
