Waiting, Cancelling, Disconnecting

Why this stage exists

A text client of the first stage saw the [LOCK] line when its statement waited. A PostgreSQL client sees nothing at all. It sent a Query, and no answer has come. This is correct: PostgreSQL itself says nothing while a statement waits for a lock. A client cannot tell "waiting" from "slow", and neither can a test, unless the server says so somewhere else. So the server says it on its own stdout:

$droid --server --port 5433 --db shop.db
[SERVER] listening on 127.0.0.1:5433
[SERVER] session 1 connected
[SERVER] session 2 connected
[LOCK] session 2 waits for session 1 on stock:7

This stage is about everything that happens around a statement while clients come and go. One waits, another gives up waiting, one vanishes with a transaction open, too many arrive at once, one is idle for an hour, and finally the whole server stops. Each of these is a way for one client to harm the others, and the server's job is to keep that harm in one place.

Cancelling from the side

A client whose statement waits can't send anything on its connection, because the protocol says it must wait for ReadyForQuery. So cancelling uses a second connection. The client opens a fresh one, and its first and only message is a CancelRequest with the two numbers the greeting gave it in BackendKeyData:

The secret key is why BackendKeyData exists. Session numbers are easy to guess, so without the key any client could cancel any other's statements. With a wrong key, the server does nothing and says nothing: telling the sender the key was wrong would help it guess.

A cancelled statement is undone like any failed statement. If its client had opened a transaction with BEGIN, that transaction stays open but failed (E, section 96), and the client decides what to do. Since droid runs one statement at a time to its end, only a statement that is parked can be cancelled. In PostgreSQL a cancel can also stop a long-running query halfway.

Limits

Every session costs memory, and a server without limits can be knocked over by one buggy client opening connections in a loop. --max-connections 2 caps the sessions. A connection over the cap gets 53300 ("too many connections") as soon as it has sent its StartupMessage, and is closed, without ever becoming a session or using a number.

A session that does nothing still holds its socket and its memory. --idle-timeout-ms 1000 closes sessions idle for a second, with 57P05. Idle is strict: no transaction open, nothing parked, nothing half received, nothing left to send. A session with a transaction open is never closed for being idle, because closing it would roll back work the client meant to commit.

The timeout is the first thing the poll loop has to wake up for without any socket being ready. Pass the time until the earliest idle session runs out as the poll's timeout, and check the clock when it returns.

Output contract for this stage

These are the exact lines and messages the tests check. The full definition is on the output contract page, section 97.

What you should see when done

In psql, a statement that waits, cancelled with Ctrl+C. psql sends the CancelRequest for you:

$psql -h 127.0.0.1 -p 5433 -U droid droid
droid=> begin;
BEGIN
droid=*> update stock set n = 9 where id = 7;
^CCancel request sent
ERROR:  canceling statement due to user request
DETAIL:  droid error 01908
droid=!> rollback;
ROLLBACK

Common bugs in this stage

• Logging the [LOCK] line only to the connection. A PostgreSQL client never shows it; the test reads it from your stdout.
• Running the cancelling connection's message as a query. Its one message is the CancelRequest; close it after reading the 16 bytes, whatever they say.
• Rolling back a cancelled statement's whole transaction. Only the statement goes; the transaction the client began stays, failed.
• Answering a wrong key. Silence, always.
• Closing a session in a transaction for being idle. That would roll back work the client meant to keep.
• A poll with no timeout. Then an idle session is only closed when some other socket happens to wake the loop.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: clock_gettime(CLOCK_MONOTONIC, ...) for "now", never the wall clock, which can jump. poll's last argument is the timeout in milliseconds, -1 for none.
• C++: std::chrono::steady_clock::now(), and a std::chrono::milliseconds until the earliest deadline.
• Rust: std::time::Instant and Duration. mio::Poll::poll takes an Option<Duration>.
• Zig: std.time.Instant.now() and since; std.posix.poll takes the timeout in milliseconds.