Wissen wer was wo macht...

Servus,

Beim schmökern in dem Buch von Andreas Scherbaum kam mir die Idee über ein SQL-Statement mir anzeigen zu lassen:
a) Wer eine Verbindung zur Datenbank hat,
b) welches Statement er gerade ausführt (Wenn kein Statement ausgeführt wird, idelt er halt),
c) Wie die ProcessID ist, damit ich ihn abschiessen kann,
d) Welche Tabellen er dabei sperrt.

Diese Infos baruche ich ab und an, wenn ich Änderungen an der Datenbank vornehmen will und trotz Aufrufs sich die Leute nicht von der Datenbank abmelden. Ich könnte mit diesem Statement nun gezielt die IP-Adressen rausfinden die die Tabelle xy sperren und dort anrufen…

Ich beziehe mich dabei auf 2 Beispiele aus dem Buch:


14.3
select … from pg_locks

und 14.4
select … from pg_stat_activity


diese habe ich versucht zu kombinieren und habe folgendes erstellt:

SELECT
	pg_stat_activity.procpid,
	pg_stat_activity.datname,
	pg_stat_activity.client_addr,
	pg_stat_activity.current_query,
	pg_locks.mode,
	pg_class.relname

FROM
	pg_stat_activity 
        LEFT JOIN pg_locks ON (pg_stat_activity.procpid = pg_locks.pid) 
	LEFT JOIN pg_class ON (pg_class.oid = pg_locks.relation)

ORDER BY 
	pg_stat_activity.client_addr, pg_stat_activity.procpid ;

I. Jedoch liefert mir diese Abfrage auch Datensätze, die zwar bei “current_query” ein SQL-Statement, aber bei relname nichts stehen haben.

II. Dies Datensätze aus I. kommen doppelt und dreifach vor.

III. Die Tabelle auf die sich das Statement bezieht, kommt gar nicht vor

IV. Es gibt mehrfache Datensätze, mit “current_query” = und keinem Eintrag in “relname”. Die bräuchte ich wenn nur einzeln.

V. Was sind verbindungen, die nichts machen? Sollte Postgres mir nich zurückgeben?

Wie sind die Themen I bis V zu interpretieren?
Was müsste ich an meinem Statement ändern um die Punkte a bis d zu erfüllen?


Hier mal ein Auszug aus dem Ergebnis meines Statements…

3640	mo_rehmann2	192.168.44.210			
15074	mo_rehmann2	192.168.44.210			
15080	mo_rehmann2	192.168.44.210	AccessShareLock			UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	RowExclusiveLock		UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	AccessShareLock			UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	RowExclusiveLock		UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	ExclusiveLock			UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	RowExclusiveLock		UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	RowExclusiveLock		UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	AccessShareLock			UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	AccessShareLock			UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	AccessShareLock			UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	RowExclusiveLock		UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	AccessShareLock			UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	RowExclusiveLock		UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	RowExclusiveLock		UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15080	mo_rehmann2	192.168.44.210	AccessShareLock			UPDATE T_KVPOSTEILE SET nartikelkeystliste = (SELECT nartikelkey FROM T_Artikel WHERE nliefekey = (SELECT nliefekey FROM T_LIEFE WHERE t_liefe.slieferantident = (SELECT t_kvpos.slieferantident FROM t_kvpos WHERE t_kvpos.simportfeld9 = t_kvposteile.simportfeld2) ) and t_artikel.skatalog = '1' ) WHERE t_kvposteile.simportfeld3 = '999'
15118	mo_mirko	192.168.44.222			
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_class	<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_database	<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_stat_activity <IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_attribute	<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_constraint	<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	ExclusiveLock			<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_namespace	<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_type		<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_class_oid_index <IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	tables		<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_authid	<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_attrdef	<IDLE> in transaction
15123	mo_nsw2		192.168.44.223	AccessShareLock	pg_locks	<IDLE> in transaction

Weiss denn jemand von einem Programm, mit dem ich den genauen Status der DB erfragen kann?
Ziel ist es rauszufinden Wer (IP-Adresse; PID) welche Tabelle sperrt. Bzw. Welches Statement diese Sperrung verursacht.

ich hab jetzt den ads mal wachgetreten, er meldet sich bestimmt auch noch hier …

Andreas

Nun, pg_locks enthält mehr Locks, nicht nur für Tabellen, sondern z. B. auch für TransaktionsIDs. Die müsstest du alle erst mal ausklammern.

Du suchst also alle Locks, die auf einer Relation liegen.



Ach so, danke für das Kaufen des Buchs :wink:

Jau!
Ich suche Userinformationen wer auf welche Tabellen und Views zugreift.

Hintergrund:
Wir entwickeln Software und synchronisieren die Datenbanken (Strukturmäßig) in der Regel morgens, wenn unsere Kunden noch nicht arbeiten.
Manchmals müssen aber auch tagsüber an Tabellen neue Spalten angehängt, Views gedroppt und neu angelegt werden etc…

Dies geht aber manchmals nicht, weil irgendwelche User dann noch einen Lock auf die zu ändernden Tabellen haben.

Wenn so etwas vorkommt habe ich mir bisher damit beholfen, dass ich zuerst bei unserem Kunden anrief und ihn bat, die Mitarbeiter mögen mal kurz vom System gehen.
Besser wäre:
Ich will Änderuneghn an z.b. Tabelle t_adresse vornehmen. Mein Editor meldet mir einen Fehler und ich schaue nach wer noch dadrauf zugreift. Dann kann ich ihn entweder anrufen oder killen und mit meiner Arbeit fortfahren…

PS:
Gerne geschehen…
Gibts auch T-Shirts??? :wink:

Nun, ja, an die Infos kommt man heran. Du musst wie gesagt nur all die Zeilen nehmen, in denen die Relation gesetzt ist. Alles andere sind andere Informationen, die nicht zu deinem aktuellen Problem beitragen.

Du könntest dich auch in einer Transaktion herantasten: starte die Transaktion, setze einen Savepoint, locke die Tabelle mit NOWAIT. Wenn das fehlschlägt, roll zurück zum Savepoint und schau nach, wer den Lock hat und reagiere - ggf. einfach die Anfrage des anderen beenden.


T-Shirts habe ich heute bestellt, wird es auf der FOSDEM und in Chemnitz geben. Gleichfalls Tassen und Elefanten :wink: