Re: Cancelling Requests Frontend/Backend Protocol TCP/IP

On 02/11/2009, at 10:29, Craig Ringer wrote:

> On 2/11/2009 5:15 PM, Raimon Fernandez wrote:
>
>> For what I've read in the manuals, this operation is only valid
>> before
>> PostgreSQL has finished processing the SELECT statement ?
>>
>> If for example I send a SELECT * from myTable, it has 20000 rows,
>> and
>> postgre starts sending the rows, how I can cancel this operation ?
>
> Assuming you're asking "is there any way to cancel a running query
> using
> the postgresql tcp/ip protocol" then, as you've noted, you can cancel
> the request until you start getting data.

yes,

> After that, you can still cancel the query by establishing another
> connection to the server and calling pg_cancel_backend( ) at the SQL
> level. This does, unfortunately, involve the overhead of setting up
> and
> tearing down a connection and the associated backend.

I assume the PID is the same as the process_id that I get from
BackendKeyData ?

BackendKeyData (B)
Byte1(’K’) Identifies the message as cancellation key data. The
frontend must save these values if it wishes to be able to issue
CancelRequest messages later.
Int32(12) Length of message contents in bytes, including self.
Int32 The process ID of this backend.
Int32 The secret key of this backend.

process_id= 22403

I can send a SELECT, and while it's running, I open a new connection
with psql and send a SELECT pg_cancel_backend(22403) and postgresql
returns t (true), but I'm still receiving rows in the first process ...

thanks,

regards,

r.