SQL-Abfragen mit Priorisierung

Frohes Neues!

Wir haben folgendes Problem:
In einer Multiuser-Umgebung gibt es Kassensysteme, die laufend auf eine PostgreSQL 8.4 zugreifen. Des weiteren gibt es auch die Möglichkeit des Chefs sich die Statistiken anzusehen. Dazu muss er die Statistiken erneuern. Prinzipiel wird dabei folgendes gemacht:

EXECUTE 'DELETE FROM ' || matview;
EXECUTE 'INSERT INTO ' || matview || ' SELECT * FROM ' || entry.v_name;

Dieser Vorgang kann bis zu 3 minuten dauern!
Während dieser Zeit ist jedoch an den Kassen kein Verkauf möglich…

Meine Vermutung ist: Die Kassenverkäufe sind Bestandteil der Statistik. Obiges Statement lockt dann gewisse Datensätze, woraufhin die Kasse warten muß.

Was wäre ein Ansatz um obige Vermutung zu überprüfen?

Gibt es die Möglichkeit ein SQL-Statement mit einer geringeren Priorität auszuführen, so dass der Statistikaufbau auf die Kasse wartet statt umgekehrt?

Guggen, was die Kassen machen. Meine Glaskugel ist aber defekt, und ich arbeite nicht mehr in der Glasbranche …

Gibt es die Möglichkeit ein SQL-Statement mit einer geringeren Priorität auszuführen, so dass der Statistikaufbau auf die Kasse wartet statt umgekehrt?

Nein.

Du könntest schauen, ob es Locks gibt. Dazu gibt es passende Systemtabellen.


Andreas

DELETE FROM ist deutlich langsamer als ein TRUNCATE. Falls ein Großteil der 3 Minuten für das DELETE verwendet werden, könnte ein Ansatz sein, das durch ein TRUNCATE zu ersetzen.

Ihr müsstet halt mal testen welches der beiden Statements so lange läuft.

@akretschmer

Mit folgendem Statement sehe ich welche Prozesse eine Table sperren:

SELECT
	pg_class.relname,
	pg_stat_activity.procpid,
	pg_stat_activity.datname,
	pg_stat_activity.client_addr,	
	pg_locks.mode,
	pg_stat_activity.current_query
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)
WHERE
  	pg_locks.relation IS NOT NULL 
	AND pg_class.relname = 'TABELLENNAME' 

ORDER BY 
	pg_class.relname, pg_stat_activity.client_addr, pg_stat_activity.procpid ;

Wie wäre aber die Vorgehensweise um festzustellen, auf welche Tabellen ein Arbeitsplatz zugreift?

Erschwernisse:
Das Programm läuft auf einem APP-Server und alle verwenden den gleichen ODBC-Eintrag. Ich kann nur unterschiedliche ProcPID’s ausmachen.
Frage: Erzeugt ein Client nur eine ProcPID beim Anmelden, die bis zum Abmelden immer gleich ist?

@castorp
Danke für den Hinweis, aber das Delete geht recht schnell. Zudem ist das Truncate wegen Abhängigkeiten nicht gut geeignet…

Yiha!

Wir konnten den Fehler festmachen:
Die Performance geht nicht in den Keller, wenn man obige Funktion einmalig ausführt.
Sie führt nur dann zu Problemen, wenn unsere Kunden mit mehreren Rechnern die Funktion parrallel ausführen.

Dies möchte ich nun so abändern:

Wenn die Funktion aufgerufen wird, wird in einer Orga-Tabelle eine Spalte nach einem Wert abgefragt.
Wenn dieser ‘1’ ist, wird die Funktion verlassen.
Wenn er != ‘1’ ist, wird er mit ‘1’ belegt und die Funktion wird ausgeführt. Am Ende wird sie dann wieder auf ‘0’ gesetzt.

Dabei muss das abändern auf ‘1’ sofort geschehen (commit), damit andere Abfragen die Änderung direkt wahrnehmen.

Soweit bin ich jetzt:

CREATE OR REPLACE FUNCTION "public"."mv_refresh" (
  "matview" varchar
)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
matview ALIAS FOR $1;
entry t_matviews%ROWTYPE;
dtnow TIMESTAMP;
dtlater TIMESTAMP;
scalculating varchar(1);
BEGIN

SELECT timeofday() INTO dtnow;

SELECT * INTO entry FROM t_matviews WHERE mv_name = matview;
IF NOT FOUND THEN
	RAISE EXCEPTION 'Materialized view % does not exist.', matview;
END IF;


-- Hier die Abfrage, ob nicht bereits die Statistik aktualisiert wird.
SELECT scalculating from t_matviews WHERE mv_name = matview INTO scalculating;
IF scalculating = '1' THEN 
	RETURN; 
--ELSE 
--    UPDATE t_matviews SET scalculating='1' WHERE mv_name=matview ;   
END IF ;


EXECUTE 'DELETE FROM ' || matview;
EXECUTE 'INSERT INTO ' || matview
|| ' SELECT * FROM ' || entry.v_name;

SELECT timeofday() INTO dtlater;

UPDATE t_matviews SET last_refresh=dtlater, idauer=(dtlater - dtnow) WHERE mv_name=matview;

RETURN;
END
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER;

1.: Sobald ich die Zeile ELSE / UPDATE t_matviews SET scalculating=‘1’… einkommentiere, erhalte ich folgenden Fehler:

FEHLER:  Fehler »Syntaxfehler« bei »$1«
LINE 1: UPDATE t_matviews SET  $1 ='1' WHERE mv_name= $2 
                               ^
QUERY:  UPDATE t_matviews SET  $1 ='1' WHERE mv_name= $2 
CONTEXT:  SQL statement in PL/PgSQL function "mv_refresh" near line 22

2.: Damit ich bei parallelen Abfragen sofort den Status erkenne, müsste die Zeile
“UPDATE t_matviews SET scalculating=‘1’ WHERE mv_name=matview ;”
mit einem Beginn; eingeleitet und mit einem comitt; abgeschlossen werden?

Ich habe einen Fehler gehabt, dadurch dass ich eine Variable fälschlicherweise genauso benannt habe, wie die Spalte einer Tabelle…

Nun bekomme ich folgendes:
PostgreSQL:7FEHLER: SPI_execute_plan failed executing query “COMMIT”: SPI_ERROR_TRANSACTION; Error while executing the query

Dies kommt aufgrund der folgenden Funktion:

CREATE OR REPLACE FUNCTION "public"."mv_refresh" (
  "matview" varchar
)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
matview ALIAS FOR $1;
entry t_matviews%ROWTYPE;
dtnow TIMESTAMP;
dtlater TIMESTAMP;
sbinbeschaeftigt VARCHAR(1);

BEGIN

SELECT timeofday() INTO dtnow;

SELECT * INTO entry FROM t_matviews WHERE mv_name = matview;
IF NOT FOUND THEN
	RAISE EXCEPTION 'Materialized view % does not exist.', matview;
END IF;


-- Hier die Abfrage, ob nicht bereits die Statistik aktualisiert wird.
SELECT scalculating from t_matviews WHERE mv_name = matview INTO sbinbeschaeftigt;
IF sbinbeschaeftigt = '1' THEN 
	RETURN; 
ELSE
	--BEGIN 
    	UPDATE t_matviews SET scalculating='1' WHERE mv_name=matview ;   
    COMMIT;
END IF ;


EXECUTE 'DELETE FROM ' || matview;
EXECUTE 'INSERT INTO ' || matview
|| ' SELECT * FROM ' || entry.v_name;

SELECT timeofday() INTO dtlater;

UPDATE t_matviews SET last_refresh=dtlater, idauer=(dtlater - dtnow), scalculating='0' WHERE mv_name=matview;

RETURN;
END
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
COST 100;

Ja. COMMIT innerhalb einer Funktion geht halt nicht.

Andreas

was wäre denn die Vorgehensweise für mein Vorhaben?

Hier die Antwort:
Ich mache es innerhalb meines Programms! Unabhängig von der DB-Funktion.