PostgREST
Send a `499 Client Closed Request` status when the proxy times out a request
Yeah, this can be reproduced without a proxy too:
-- Append on test/spec/fixtures/schema.sql
CREATE FUNCTION test.sleep() RETURNS void AS $$
SELECT pg_sleep(5);
INSERT INTO test.projects VALUES (111, 'A canceled request');
$$ LANGUAGE sql;
# In one terminal
PGRST_DB_ANON_ROLE='postgrest_test_anonymous' postgrest-with-postgresql-14 postgrest-run
## On another terminal
http POST localhost:3000/rpc/sleep
## cancel the request immediately
## Then after 5 seconds
http localhost:3000/projects
...
{
"client_id": null,
"id": 111,
"name": "A canceled request"
}
]
Related to https://github.com/PostgREST/postgrest/issues/699
On #699, it was proposed:
I think we could use setOnClose to detect this situation and roll back the current transaction.
But a rollback wouldn't cancel a query immediately, we need to cancel the query using libpq or by calling pg_cancel_backend(not sure if possible/easy).
Edit: using pg_cancel_backend is not possible because it requires superuser.
Cancelling the query should be possible because on psql:
select pg_sleep(300);
-- Pressing Ctrl + C
Cancel request sent
ERROR: 57014: canceling statement due to user request
LOCATION: ProcessInterrupts, postgres.c:3329
Cancel support in other libraries:
- psycopg2: https://stackoverflow.com/questions/2041978/cancel-query-execution-in-pyscopg2 (doesn't seem possible)
- java jdbc: https://stackoverflow.com/questions/1526211/how-to-cancel-a-postgres-query-in-java-jdbc (possible?)
- How to do it with libpq in C: https://stackoverflow.com/questions/18015203/postgresql-cancel-query-from-c-c-program (seems complicated)
- Deno: https://deno.land/x/[email protected]#canceling-queries-in-progress (unreliable)
Not sure if this is entirely worth it. A statement_timeout shorter than the proxy timeout would solve it.
I think it's worth it, because the client can also cancel the connection on purpose. And in this case, the query would still be ongoing, right?
But that could be solved here, too, if understood correctly?
