Jak anulować długie zapytanie bez ubijania sesji (pg_cancel_backend)

PostgreSQL

Jak anulować długie zapytanie bez ubijania sesji (pg_cancel_backend)

W skrócie

Nie każde "zabij to zapytanie" musi kończyć się rozłączeniem sesji. Pokażemy Ci, jak przerwać pojedyncze, za długie polecenie tak, by klient został połączony, i czym pg_cancel_backend różni się od twardszego pg_terminate_backend.

Jak to wygląda w praktyce

Ktoś odpalił SELECT bez sensownego WHERE na dużej tabeli, raport liczy się od kilkunastu minut, a obciążenie dysku i procesora rośnie. Albo aplikacja wpadła w nieoptymalny plan i to samo zapytanie, które zwykle trwa sekundę, tym razem miele bez końca. W obu przypadkach chcesz zatrzymać tę jedną operację, ale nie chcesz zrywać połączenia - bo sesja może być w środku dłuższej pracy, a nagłe rozłączenie oznaczałoby błąd po stronie aplikacji i cofnięcie całej transakcji. Właśnie do tego służy anulowanie zamiast terminowania.

Dlaczego tak się dzieje

Każde połączenie w PostgreSQL obsługuje osobny proces backendu, który w danej chwili wykonuje najwyżej jedno polecenie. Anulowanie działa na to konkretne polecenie: pg_cancel_backend wysyła do procesu sygnał SIGINT, a backend przy najbliższym bezpiecznym punkcie przerywa zapytanie i zgłasza błąd "canceling statement due to user request". Sesja żyje dalej, transakcja pozostaje otwarta (choć w stanie błędu, jeśli anulowaliśmy polecenie w jej trakcie - trzeba ją zamknąć przez ROLLBACK). To zupełnie inne zachowanie niż SIGTERM z pg_terminate_backend, który kończy cały proces i całą sesję. Ważne: anulowanie nie jest natychmiastowe co do milisekundy - backend zareaguje, gdy dojdzie do miejsca, w którym sprawdza przerwania.

Jak to rozwiązać krok po kroku

1. Znajdź PID zapytania, które chcesz przerwać: SELECT pid, usename, now() - query_start AS trwa, left(query, 80) AS zapytanie FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start;. Interesują Cię aktywne polecenia z największym trwa.
2. Zanim naciśniesz spust, upewnij się, że to naprawdę ten proces - porównaj query, usename i application_name, żeby przypadkiem nie ubić ważnego, zaplanowanego raportu.
3. Anuluj bieżące polecenie: SELECT pg_cancel_backend(<pid>);. To najłagodniejszy sposób - przerywa zapytanie, ale zostawia sesję.
4. Jeśli anulowanie kilka razy nie zadziała (backend utknął tak głęboko, że nie sprawdza przerwań), dopiero wtedy rozważ twardsze SELECT pg_terminate_backend(<pid>);, świadomie godząc się na rozłączenie sesji.
5. Sprawdź uprawnienia. Własne zapytanie anulujesz zawsze; cudze - jako superużytkownik lub rola z członkostwem w pg_signal_backend. Aplikacyjnemu adminowi warto nadać właśnie tę rolę zamiast praw superusera.
6. Zapobiegaj na przyszłość. Ustaw statement_timeout dla ról aplikacyjnych (np. '30s'), żeby za długie polecenia były anulowane automatycznie, bez Twojej ręcznej interwencji.

Jak sprawdzić, że zadziałało

Funkcja zwraca true, gdy sygnał został wysłany. Klient, który wykonywał zapytanie, zobaczy komunikat o anulowaniu polecenia na żądanie użytkownika - to potwierdza, że przerwanie doszło do backendu. Wykonaj ponownie zapytanie do pg_stat_activity: dany pid powinien nadal być na liście (sesja żyje), ale jego state zmieni się z active na idle lub idle in transaction, a query nie będzie już tym ciężkim poleceniem. Jeśli po kilku sekundach zapytanie wciąż jest aktywne, powtórz anulowanie - a gdy to nie skutkuje, sięgnij po terminowanie sesji.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5