![]() Note that while using kill -9 on a backend causes a short down time, kill -9 on the postmaster process itself has even worse effects and should be avoided at any cost. It will then kill all other PostgreSQL processes and undergo crash recovery, which causes an outage of the whole database that can take seconds to minutes. The problem is that the postmaster detects if one of its child processes did not shut down cleanly. That signal cannot be caught and immediately terminates the process. If that has no effect, it is tempting to use kill -9, which will send SIGKILL. That will send the SIGTERM signal, so it is the same as calling pg_terminate_backend() for that backend. It is perfectly fine to use plain kill on a PostgreSQL backend process. Note that the delivery of signals is held off while a process is in kernel space.ĭon’t use kill -9 unless you are desperate That would indicate a problem on the operating system or hardware level. Execution is stuck in a system call that cannot be interrupted.In this case, you should report it as a bug to the author of the function. ![]() Execution is stuck in a third-party C function that was called in an SQL statement.That would be a PostgreSQL bug, and the fix would be to add another call to the macro. ![]() Execution is stuck in a loop that does not contain CHECK_FOR_INTERRUPTS().Reasons why canceling a query may not work This function will then throw the error that cancels the current statement or it will terminate the backend process, depending on which flag was set. This ensures that no process is interrupted at an inconvenient time, when it would for example leave shared memory in an inconsistent state.Ĭalls to the CHECK_FOR_INTERRUPTS() macro, which invoke the ProcessInterrupts() function, are sprinkled all over the PostgreSQL code at safe places. These variables act as flags, and it is the responsibility of the backend process to react to them as soon as is convenient. SIGINT will set Quer圜ancelPending and SIGTERM will set ProcDiePending. This signal handler does not immediately interrupt the backend process, but it sets global variables for the process. Now each PostgreSQL process has a signal handler that processes these signals when they are received. pg_terminate_backend() sends the signal SIGTERM. This is also what the function pg_cancel_backend() does. When the postmaster process receives a CancelRequest, it sends the signal SIGINT to the backend process of the corresponding database session. Inter-process communication in PostgreSQL relies a lot on signals. How the server responds to a cancel request To be able to use these functions, you must either be a superuser or a member of the default role pg_signal_backend (the next section will explain why), or you must have connected to the database as the same database user as the session you want to torpedo (you are allowed to cancel your own statements). ![]() Another option is pg_terminate_backend(), which goes one step further and terminates someone else’s database session. In an interactive psql session, you can simply hit Ctrl+C to send a cancel request, and GUI clients usually have a button for that purpose.īut it is also possible to cancel somebody else’s query by calling the database function pg_cancel_backend(). The C library libpq provides the functions PQgetCancel() and PQcancel() to cancel queries, and other database APIs should have similar provisions. Without that key, everybody could cancel your query, which would be an inacceptable security problem. That secret key was sent by the server during the start of the original connection. This is done by opening a new connection and sending a CancelRequest message with a secret key. The PostgreSQL protocol has provisions for interrupting a running statement. I’ll also show you a trick how to solve the problem (not for the faint of heart!) How to cancel a running query This article discusses what might be the cause. Usually, it is easy to interrupt (cancel) such a statement, but sometimes it can turn into a problem, and a query cannot be canceled. Sometimes a PostgreSQL query takes forever.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |