![]() 604326 + 00 | ALTER TABLE auth_user ADD COLUMN age int null ( 1 row ) mydb =# SELECT 'SELECT pg_terminate_backend(' || pid || ') ' FROM pg_stat_activity WHERE pid = ANY ( pg_blocking_pids ( 613 )) ? column ? - SELECT pg_terminate_backend ( 77 ) ( 1 row ) mydb =# SELECT pg_terminate_backend ( 77 ) pg_terminate_backend - t ( 1 row ) mydb =# SELECT pid, state, backend_start, substr ( query, 0, 100 ) q FROM pg_stat_activity WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%' ORDER BY backend_start pid | state | backend_start | q -+-+-+- 613 | idle | 2022 - 06 - 17 20 : 25 : 06. Mydb =# SELECT pid, state, backend_start, substr ( query, 0, 100 ) q FROM pg_stat_activity WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%' ORDER BY backend_start pid | state | backend_start | q -+-+-+- 613 | active | 2022 - 06 - 17 20 : 25 : 06. And it may continue to hold problematic locks. When using pg_cancel_backend, there’s a risk that the application’s error-handling code will rollback the transaction/savepoint, and carry on to run similar queries. Normally, when I need to stop a query, I want to stop the whole application process that started it, roll back all data changes, and release all locks. If you’re interested in diving into the source, start with ProcessInterrupts in src/backend/tcop/postgres.c.) In theory, these sections don’t spend much time executing, but never say never. Small sections of the code also defer handling it. (* Ackshuallly, pg_terminate_backend can also fail to apply immediately, but it’s much less likely. The backend process defers handling the cancellation until all the input has been read, since otherwise the connection could not be left open and functional. Specifically, this can occur when the process is reading input from the client, such as an incoming query. So sometimes, you might run pg_cancel_backend and see nothing happen for a while. You can query the available databases by viewing the expected values for the db configuration option. Second, pg_terminate_backend applies immediately*, whilst pg_cancel_backend can be deferred at certain points in the backend process lifecycle. The server has built-in support for different databases. Thus, if the connection is using savepoints, it can still keep the surrounding transaction open, with pending data changes and locks. The current transaction or savepoint is aborted. In contrast, pg_cancel_backend interrupts only the running query, leaving the connection open. This rolls back any open transaction on the connection, freeing all locks it holds. There are two differences that make pg_terminate_backend “harsher”.įirst, pg_terminate_backend completely stops the process, causing the connection to close.
0 Comments
Leave a Reply. |