Support for protocol level operations

[adriangb]

Is there any support in psycopg for taking an existing connection and doing protocol-level operations on it? In particular, I need to be able to cancel a different execution (using a pid and magic number you get via the low-level protocol) but it would be nice to have psycopg handle connection pooling and all of that stuff, the alternative being having a psycopg pool I use for some things and janky protocol level communication out of band.

[dvarrazzo]

Are you sure you can cancel a connection from another one using the cancel code? Where is it documented? From another connection you can probably use pg_cancel_backend(pid) instead.

Psycopg can allow you to interface with the libpq; if there is a libpq function to do what you want to do it should be wrapped in psycopg.pq.

We don't currently offer functionalities based on accessing the wire protocol. We can consider introducing some if useful and if we can't do otherwise, but we need a reason and I can't quite figure out the use case you propose.

[adriangb]

I guess I can answer my own question: for this particular use case it makes no sense for psycopg to handle the connection because it must be a new connection to be able to send the CancelRequest message (since it's akin to a startup message, not a regular message flow message).

[dvarrazzo]

The feature you mention is wrapped by conn.pgconn.get_cancel() and PGcancel.cancel(). We miss documentation for that layer but most methods map 1:1 with libpq functions. Those are the functions on which conn.cancel() is based.

You can use that feature, but isn't it easier to run a pg_cancel_backend(), for which you only need to know the pid?

[adriangb]

You can use that feature, but isn't it easier to run a pg_cancel_backend(), for which you only need to know the pid?

That's a good question, I'm curious now why the protocol doesn't recommend that. Maybe it's to avoid the overhead of fully establishing a new connection (auth, etc.)?

[dvarrazzo]

One shortcoming I see is that pg_cancel_backend() requires certain database privileges. In the past only superusers could call it; only sometimes later (9.2-ish) the permission requirements were relaxed (a friend of mine actually pestered the core devs to have this feature...) and now an user can cancel its own backends.

ISTM that, originally, pg_cancel_backend() wasn't really a function available for normal usage, but to database admins only; with relaxed permissions it is now usable at application level too.

[adriangb]

That makes sense, I imagine there are still scenarios where a user doesn't have access to pg_cancel_backend() but should be able to cancel their own query (i.e. ctrl-c in psql).