transaction_timeout is not enforced during ClientWrite
Hi,
On one of the PostgreSQL instances we manage, we saw a transaction
remain open for more than 15min with transaction_timeout set to 50s.
The backend was waiting on ClientWrite. We couldn't tell whether the
client or the network caused the stall, but I think the server should
have closed the connection when the transaction timeout expired.
ProcessClientWriteInterrupt() only handles ProcDiePending while the
socket write is blocked. I agree with the reasoning in the earlier
discussion [1]/messages/by-id/CA+TgmoZiZQF5YFLTM_S7TWOy6UKO0639sxDWYOQMYgvHfZ_rMA@mail.gmail.com that canceling a query mid-write and then keeping the
connection open can leave the protocol out of sync. Since we decided
that transaction_timeout should terminate the session with FATAL [2]/messages/by-id/ACC3312D-8218-4F30-8350-E8634BF1C5DC@yandex-team.ru,
is there any design reason to defer handling it while a client write
is blocked?
The attached patch lets ProcessClientWriteInterrupt() use the existing
termination path for a pending transaction timeout. In
ProcessInterrupts(), it moves transaction timeout handling immediately
after ProcDiePending so a pending query cancel cannot raise a
recoverable error first.
Thoughts?
---
Regards,
Jingtang
[1]: /messages/by-id/CA+TgmoZiZQF5YFLTM_S7TWOy6UKO0639sxDWYOQMYgvHfZ_rMA@mail.gmail.com
[2]: /messages/by-id/ACC3312D-8218-4F30-8350-E8634BF1C5DC@yandex-team.ru
Hello
This seems to be a real issue, but isn't the patch incomplete?
It works if the timer fires while the backend is already blocked in the write, but if the client has already stopped reading and the timer fires while the backend is still doing something else, ProcessInterrupts() clears TransactionTimeoutPending and raises the FATAL, then pq_flush() blocks. At that point nothing is pending for ProcessClientWriteInterrupt() to act on, so we wait forever, and the transaction is only aborted later in proc_exit().
Repro is simple, a client that sends this as a simple query and never reads anything:
psql -X -At -v FETCH_COUNT=1 -c "BEGIN; SET LOCAL transaction_timeout='2s'; SELECT CASE WHEN g <= 25 THEN repeat('x',8000) ELSE pg_sleep(6)::text END FROM generate_series(1,26) g;" | sleep 25
(The row count has to fill the socket buffer almost, but not completely)
With a LOCK TABLE in the transaction, the lock is also still held after the FATAL and blocks other sessions.
Hi Jingtang,
There is a similar case on the read side. In psql:
CREATE TABLE tt_copy_wait (v text);
BEGIN;
SET LOCAL transaction_timeout = '2s';
COPY tt_copy_wait FROM STDIN;
Leave COPY waiting for input for more than 2s. In my run,
pg_stat_activity still showed active ClientRead.
Entering \. then produced:
FATAL: terminating connection due to transaction timeout
During COPY input, DoingCommandRead is false. The read-wait path in
ProcessClientReadInterrupt() then handles ProcDiePending, but not
TransactionTimeoutPending. Could the fix cover this path too?
I've posted draft fixes for other transaction_timeout issues [0]/messages/by-id/87676E9A-DCA9-46AE-9214-5C374D5832F3@yandex-team.ru, but
this ClientRead case is independent. Just linking these threads together.
Thank you!
Best regards, Andrey Borodin.
[0]: /messages/by-id/87676E9A-DCA9-46AE-9214-5C374D5832F3@yandex-team.ru
Hi,
Thanks for the feedback.
During COPY input, DoingCommandRead is false. The read-wait path in
ProcessClientReadInterrupt() then handles ProcDiePending, but not
TransactionTimeoutPending. Could the fix cover this path too?
Thanks, Andrey.
Yes. I initially thought the DoingCommandRead branch already covered
ClientRead. I reproduced the issue by leaving COPY FROM STDIN waiting
for input past the transaction timeout. It looks like the same approach
as for ClientWrite should work in ProcessClientReadInterrupt().
I've attached v2 with that change.
It works if the timer fires while the backend is already blocked in
the write, but if the client has already stopped reading and the timer
fires while the backend is still doing something else,
ProcessInterrupts() clears TransactionTimeoutPending and raises the
FATAL, then pq_flush() blocks. At that point nothing is pending for
ProcessClientWriteInterrupt() to act on, so we wait forever, and the
transaction is only aborted later in proc_exit().
Thanks, Zsolt.
Oh, yes, I see what you mean. But I think this goes a bit beyond
transaction_timeout. The same thing can happen with SIGTERM when
ProcessInterrupts() handles ProcDiePending. So I plan to address
this in a separate patch in v2.
Once the backend has decided to terminate the connection, sending
the error to the client should be best-effort. The patch changes
send_message_to_frontend() to send FATAL reports to client sockets
without waiting for the socket to become writable. That lets the
backend exit after either a transaction timeout or SIGTERM, even
if the client has stopped reading.
Does that approach sound reasonable?
I've attached v2 as two separate patches.
---
Regards,
Jingtang
Attachments:
t259189_4v2-0001-Honor-transaction-timeout-during-blocked-client-reads-and-writes.patchapplication/octet-stream; name=v2-0001-Honor-transaction-timeout-during-blocked-client-reads-and-writes.patchDownload+82-17
v2-0002-Avoid-blocking-on-client-output-when-reporting-FATAL-errors.patchapplication/octet-stream; name=v2-0002-Avoid-blocking-on-client-output-when-reporting-FATAL-errors.patchDownload+61-1
Hi Jingtang,
Thank you for the patches. I tested v2 (0001 and 0002). They apply
cleanly to master 5ffc82103773 and build without warnings.
I built with --enable-cassert --enable-tap-tests
--enable-injection-points and used a small raw-protocol client that
sends a query and never reads the reply. I set transaction_timeout
to 2s.
On master, without the patches:
* A 300 MB result: the backend stays in ClientWrite long after 2s.
* Andrey's case, COPY FROM STDIN left idle: the backend stays in
ClientRead.
* Zsolt's case: with 25 rows of 8000 bytes and then pg_sleep(6),
the FATAL is logged but the backend stays in ClientWrite. A
LOCK TABLE taken in that transaction stays held; another session
gets "canceling statement due to lock timeout". With 20, 22, 23
or 24 rows the backend exits. With 26 to 32 rows it blocks in
ClientWrite before the FATAL is logged, like the 300 MB case. So
on my machine only 25 rows shows the FATAL logged and then the
hang.
* The same hang happens if pg_terminate_backend() is used instead
of the timeout (at 25 rows).
With 0001 only, the first two cases are fixed and the client gets
the FATAL for COPY. The 25-row case, the SIGTERM variant and the
held lock are not fixed, which matches your explanation.
With 0001 and 0002, all of these exit, and the other session gets
the lock. This also fits your plan to treat SIGTERM separately.
Normal paths looked unchanged, on master and with the patches:
* transaction_timeout with a reading client still delivers the
FATAL message.
* statement_timeout still gives an ERROR and the connection lives.
* pg_terminate_backend() still delivers "terminating connection due
to administrator command".
* FATAL at startup (unknown database, unknown role) still reaches
psql.
make check passes (243 tests) on master and with both patches.
test_misc passes with the patches (15 files, 257 tests), including
the new tests in 005_timeouts.pl (5 tests, 2 on master). I saw no
TRAP or PANIC in the server logs.
From these tests, the approach in 0002 looks reasonable to me. Two
small questions from reading the code:
1. The comment in send_message_to_frontend() says parallel workers
are excluded, but the check is only MyProcPort != NULL. Is that
enough, or should the comment be reworded?
2. With the PqCommBusy check, socket_putmessage_noblock() drops the
message silently. I think that is intended, since a nested
message during a write was already suppressed. Is a short
comment worth adding there?
What I did not test:
* The literal psql command from Zsolt's message (FETCH_COUNT=1
piped to sleep). On my master the backend exited, and I do not
know why, so I used the raw client instead.
* idle_in_transaction_session_timeout and an idle transaction with
transaction_timeout. My client did not exercise them properly, so
I only rely on the existing TAP tests.
* 0002 without 0001, the extended protocol, COPY OUT, SSL or GSS,
authentication, the libpq TAP tests, walsender, parallel workers,
Windows, and backpatching.
Thanks again for working on this.
Best regards,
Joao Rito
Hi,
Attached is v3 with a few comment changes in v2.
---
Regards,
Jingtang
Attachments:
t259189_6v3-0001-Honor-transaction-timeout-during-blocked-client-reads-and-writes.patchapplication/octet-stream; name=v3-0001-Honor-transaction-timeout-during-blocked-client-reads-and-writes.patchDownload+82-17
v3-0002-Avoid-blocking-on-client-output-when-reporting-FATAL-errors.patchapplication/octet-stream; name=v3-0002-Avoid-blocking-on-client-output-when-reporting-FATAL-errors.patchDownload+60-1
Hi,
Attached is v3 with a few comment changes in v2.
Attached is v4. It disables parallel query in the pg_sleep() test
to fix the macOS TAP failure reported by CommitFest CI.
---
Regards,
Jingtang