Kurz und gut - Episode #08 Bitte zur Seite treten: Sessions einer Tabelle entfernen.

Das Problem

Ein Kunde hatte neulich die folgende Problemstellung mit PostgreSQL: In bestimmten Intervallen musste eine Tabelle komplett mit truncate geleert und im Abschluss wieder neu befüllt werden. Dabei ist es häufig passiert, dass ein „truncate“-Kommando und damit davon abhängige, weitere Arbeitsschritte hängen geblieben sind. Grund dafür war, dass ausgerechnet auf der zu leerenden Tabelle eine Anwendung Abfragen abgesetzt hatte, die in diesem Fall dem Monitoring dienten und noch eine Lese-Sperre hatten. 

Die Lösung

Aufgrund der Sperre darf das „truncate“ nicht einfach am „select“ vorbei eine Tabelle leeren, denn dabei handelt es sich de facto um einen Neuaufbau der Tabelle. Aufgrund der Automatisierung des Verfahrens müssen demnach alle Sessions, die noch auf die Tabelle zugreifen, beendet werden.

In diesem Fall hilft uns ein Skript, welches wir in der Datenbank mit der Sperre ausführen. Tabellen- und Schemanamen müssen bekannt sein und eingesetzt werden. Die Funktion „pg_terminate_backend“ beendet die Session mit der „pid“, die aus der angegebenen Abfrage gegen den Systemkatalog ermittelt wird. Es müssen noch der Tabellen- und Schemaname eingesetzt werden. Die Ausführung erfolgt als „Superuser“ der Datenbank:

select pg_terminate_backend(pid)        -- Funktion zum Beenden von Sessions
from (
        select distinct l.pid from      -- eindeutige pid ermitteln
pg_locks l,
pg_class c,
pg_namespace n,
pg_database d,
pg_stat_activity s 
        where
        l.pid != pg_backend_pid()       -- nicht meine eigene Session killen
        and
        d.datname=current_database()    -- nur Sessions meiner momentanen DB killen
        and
        s.datid=l.database              -- Join zwischen Activity- und Lock-Tabelle
        and
        l.database=d.oid                -- Join zwischen Lock- und Datenbank-Tabelle
        and
        s.backend_type='client backend' -- Nur Usersessions (keine internen) killen
        and
        c.relname='tabname'             -- Tabellen- oder Viewnamen einsetzen
        and
        -- s.state='idle'               -- Benutzen, um nur inaktive Sessions zu killen
        -- and
        n.nspname='schemaname'          -- Schemaname einsetzen
        and
        n.oid=c.relnamespace            -- Join zwischen Class- und Schematabelle
        and
        c.oid=l.relation                -- Join zwischen Class- und Sperrtabelle
      )
as term_session ;
 

Im Anschluss an die Ausführung waren alle Sessions aus der Tabelle entfernt und das „truncate“ lief problemlos durch. Zu beachten ist, dass der Teil mit „state='idle'“ in diesem Fall nicht zum Tragen kam. Im jeweiligen Problemfall kann es nötig sein, den jeweiligen Status ebenfalls zu berücksichtigen. Die ausführende Session selbst sollte ebenfalls keine Abfrage auf die entsprechende Tabelle absetzen, da sie sich nicht selbst beenden soll. 

Fazit

Bei PostgreSQL ist es leicht möglich, problematische Sessions zu entfernen. Über die Systemkatalog-Tabellen können die Verursacher leicht ermittelt werden.

Sie haben Fragen oder Problemen rund um das Thema PostgreSQL? Schreiben Sie Ihr Anliegen in die Kommentare und wir melden uns zeitnah bei Ihnen! 

Seminarempfehlung