Re: BUG #13518: CancelRequest lacks statement identifier

Dear Tom,
thanks for info.

>Niall Ross <niallfr(at)btinternet(dot)com> writes:
>
>
>>Am I misunderstanding something? My experience with using Postgres, and
>>my reading of the documents, is that a new statement, but not a new
>>connection, is needed to cancel a statement on that connection. The
>>cancel is signalled by a fresh statement using the same connection (and
>>specifically that connection's PID and Key).
>>
>>
>
>Uh, no, that's not how it works at all. PQcancel() involves opening a
>fresh connection to the postmaster, which will then send SIGINT to the
>backend process you're originally connected to.
>
>
You are right. Our cancelling code is implemented on _our_ system's
existing connection instance but it opens a new _socket_ connection to
send the cancel request to postgres (we do not reify this temporary
socket connection as a separate connection in our system). I apologise
for my stupid phrasing - I was thinking in my system's terms, forgetting
that you of course were describing it in Postgres' terms.

>Also, looking again at the PQcancel code, I'm unsure that there is any
>actual race condition here. The comments therein quoth
>
> * Wait for the postmaster to close the connection, which indicates that
> * it's processed the request. Without this delay, we might issue another
> * command only to find that our cancel zaps that command instead of the
> * one we thought we were canceling. Note we don't actually expect this
> * read to obtain any data, we are just waiting for EOF to be signaled.
>
>
That's interesting. Currently, we close the socket at our client end
after committing the cancel request, knowing that no message response is
expected, and do not attempt to tell when the socket is closed by the
server.

If the connection's command has completed on the server by the time the
cancel request reaches the server, is the behaviour expected that the
server would close the socket (EOF) immediately?

>which means that by the time the PQcancel call returns, the SIGINT has
>been sent. I did speculate upthread that delivery of that signal might
>be asynchronous, but that was just speculation. Even if there was some
>delay, the backend would ignore the signal unless it arrived later than
>the first byte of the next client request.
>
Should I interpret these remarks to mean that you are qualifying the
quoted comment above them, or would that be a misunderstanding? To ask
the same question another way, are you saying it is safe to wait till
the cancel request's socket is closed by the server, for no less time
but also for no longer, before sending another command on the
connection's main socket?

>So it seems a bit hard to
>credit that you would have a practical problem unless your application
>were to issue a new command while PQcancel was still executing
>
That's exactly the problem; in our desire to maximise performance, we
want to issue a new command after cancelling a command that may or may
not complete before the cancel reaches the server and is processed.

- In an ideal world, if the command is still running when the
cancel is processed, we want it cancelled, otherwise a no-op.

- In a less ideal world, we at least want a response when the cancel
request has been processed rather than waiting for a guessed time.
IIUC, EOF on the cancel socket is this response, both in the case where
the cancel does something and in the case where it arrives after the
query completes.

>... and if
>it does, I maintain that's an application bug not a protocol problem.
>
We have a multi-threaded system which can use relational database
backends, Postgres being one possibility. Threaded presentation of
statements to the same connection is not eliminable in certain important
cases. One solution is to protect our reification of the connection so
it drains its socket of messages for an ongoing query (i.e. buffers
them) before sending another. Such a query can be dismissed while
buffering in various cases. This is the situation in which we want to
end a running query's posting of messages to the socket as soon as
possible, and then present a new query to that socket as soon as
possible. Fire&forget cancellation would be ideal - _if_ the cancel
could only affect the intended query. Waiting for server-closing of
another socket is less ideal, but at least it is deterministic.

I submitted this as a bug, but now I understand there is a way to know
when the server has cancelled, I'd reclassify it as a critique of the
cancel request feature. For now, we are managing without it. Hopefully
the discussion has been of interest to you in thinking about Postgres
evolution.

Yours faithfully
Niall Ross