Sich selbst Auslagernde Datenbank?

Hallo,

ich habe folgendes vor:
Ich brauche eine Anwendung/ein postgreSQL-Querry, welche/was folgendes können müsste:

-ein “Datenfenster” für die Anzahl Datensätze einer Datenbank “A” festlegen (kann nauch vorher bestimmt/definiert werden)
z.B.: “min = 10000” “max=50000”

-wenn “max” überschritten wird, sollen alte Daten in eine andere Datenbank “B” verschoben werden
sodass Datenbank “A” schlank bleibt, um Zugriffszeiten zu minimieren

-die Zugriffszeiten der Datenbank “B” spielen dabei gar keine Rolle

Diese Anwendung/das Querry wird dann zyklisch aufgerufen.

Ist sowas (ähnliches) möglich?
Hat jemand eine Idee oder einen Denkanstoß für mich?
Oder weiß jemand/ist jemand der Meinung, dass es anders besser/einfacher/effizienter geht?
Habe schon rumgesucht, habe aber immer nur “alle Daten löschen/TRUNCATE/DROP/etc.” finden können.

Freundlichst, Torsten B.

Das klingt eher nach einer Tabellen-Partitionierung. Dabei definiert man (z.B. über TRIGGER), daß Daten z.B. eines Jahres oder eines Monates in je eine separate Tabelle gespeichert werden. Tabellen, die dann ‘alt’ sind, kann man auf billigere (langsammere) Tablespaces auslagern. Abfragen wiederum können (dank Constraint Exclusion) die Daten aller Tabellen zwar sehen, durchsuchen aber nur die in Frage kommenden Tabellen (entsprechend der WHERE-Bedingung)

Klingt das für Dich besser als Deine Idee?

Andreas

Klingt haargenau nach Partitinierung, da hat akretschmer Recht.

ok vielen Dank für den Tipp!

Ich werde mich am WochenEnde ausführlich mit dem Stichwort “Tabellen-Partitionierung” im Zusammenhang mit postgreSQL beschäftigen.
Hoffentlich komme ich meinem Ziel so näher.
Falls ich dann noch fragen habe, … ich weiß ja wo ich euch finden kann :p

Mfg Torsten

Hallo,
ich wollte nur bescheid sagen, dass ich mein Ziel NICHT erreicht habe.
Warum nicht? Weil ich es nicht hinbekomme, dass diese Zeile innerhalb einer Funktion

INSERT INTO SuchTabelle VALUES (NEW.*);

den Tabellennamen aus der Variable SuchTabelle zieht. Es ist nach folgendem Schema aufgebaut:

CREATE OR REPLACE FUNCTION t_value_inserttrigger()
RETURNS TRIGGER AS $$
DECLARE
SuchTabelle text;
aktuellerMonat text;
aktuellesJahr text;
SuchDatum text;
naechsterMonat text;
TimeStampAktuellerMonatAnfang text;
TimeStampNaechsterMonatAnfang text;
BEGIN
aktuellerMonat := to_char(CURRENT_TIMESTAMP,‘MM’);
naechsterMonat := to_char(CURRENT_TIMESTAMP + interval ‘1 month’ ,‘MM’);
aktuellesJahr := to_char(CURRENT_TIMESTAMP,‘YYYY’);
TimeStampAktuellerMonatAnfang := aktuellesJahr || ‘-’ || aktuellerMonat || ‘-01’;
TimeStampNaechsterMonatAnfang := aktuellesJahr || ‘-’ || naechsterMonat || ‘-01’;
SuchDatum := aktuellesJahr || aktuellerMonat;
SuchTabelle := ‘t_value_’ || SuchDatum;

–Prüfen, ob die MonatsTabelle schon existiert
IF EXISTS (SELECT relname FROM pg_class WHERE relname = SuchTabelle)
THEN
INSERT INTO SuchTabelle VALUES (NEW.*);
ELSE
CREATE TABLE SuchTabelle (
CHECK ( Time >= TIMESTAMP TimeStampAktuellerMonatAnfang AND Time < TIMESTAMPTimeStampNaechsterMonatAnfang)
) INHERITS (t_value);
ALTER TABLE SuchTabelle OWNER TO postgres;

INSERT INTO SuchTabelle VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;


CREATE TRIGGER insert_t_value_trigger
BEFORE INSERT ON t_value
FOR EACH ROW EXECUTE PROCEDURE t_value_inserttrigger();


kann mir eventuell jemand sagen, wie ich Funktionen, wie INSER INTO oder CREATE TABLE, Tabellennamen aus Variablen geben kann?
Ich will ja im Endeffekt ‘nur’ eine Datenbank, die jeden Monat in eine neue Tabelle schreibt…

MfG Torsten

Mittels EXECUTE, dem man ein String gibt. Guggst Du Handbuch, ausführen dyn. Anweisungen.

Andreas

…nochmals VIELEN DANK für diesen Hinweis, das ist genau das, was ich gesucht habe!
soweit sogut.

Ich habe nun sehr viele Möglichkeiten durchprobiert, um die INSERTS irgendwie hinzukriegen…:
EXECUTE ‘‘INSERT INTO ‘’ || SuchTabelle || ‘’ VALUES (NEW.)‘’;
EXECUTE ‘‘INSERT INTO ‘’ || SuchTabelle || ‘’ VALUES (’’ || NEW || ‘’.
)’’;
EXECUTE ‘‘INSERT INTO ‘’ || SuchTabelle || ‘’(’’||Time||‘’) VALUES (‘’ || NEW.Time || ‘’)‘’;
EXECUTE ‘‘INSERT INTO ‘’ || SuchTabelle || ‘’(Time) VALUES (’’ || NEW.Time || ‘’)‘’;
EXECUTE ‘‘INSERT INTO ‘’ || SuchTabelle || ‘’(Time) VALUES (’’ || NEW.Time || ‘’)‘’;
EXECUTE ‘‘INSERT INTO ‘’ || SuchTabelle || ‘’ (Time) VALUES (’’ || NEW.Time || ‘’)‘’;
EXECUTE ‘‘INSERT INTO ‘’ || SuchTabelle || ‘’ VALUES (’’ || NEW || ‘’.*)‘’;

bzw.
EXECUTE ‘INSERT INTO ’ || SuchTabelle ||’(Id, Key, Time, BatchId, CharacteristicId, Value) VALUES (’ || NEW.Id || ‘,’ || NEW.Key || ‘,’ || NEW.Time || ‘,’ || NEW.BatchId || ‘,’ || NEW.CharacteristicId || ‘,’ || NEW.Value || ‘)’;
doch alles ohne Erfolg…


ERROR: NEW used in query that is not in a rule
SQL Status:42601
Kontext:SQL statement “INSERT INTO t_value_201003 VALUES (NEW.*)”
bzw.
ERROR: syntax error at or near “00”
SQL Status:42601


könnte ich noch einen weiteren Denkanstoß/Hinweis von euch bekommen?


MfG Torsten

Wie rufst Du das denn auf, in einem TRIGGER? Schau Dir doch einfach mal die Doku an, PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Partitioning enthält ein passendes Beispiel mit Triggerfunktion und allem drum und dran.

Andreas

Danke für eure Tipps!

Eigentlich hatte ich den richtigen Weg schon, nämlich über die Zeilen:
EXECUTE ‘‘INSERT INTO ‘’ || MonatsTabelle || ‘’ VALUES (’’ || New.id || ‘’)’’;

Mein Problem, warum es vorher nicht Funktioniert hatte, war das, das die SPALTE “Time” heißt.
Die Zeile:
EXECUTE ‘‘INSERT INTO ‘’ || MonatsTabelle || ‘’ VALUES (’’ || New.Time || ‘’)’’;

bringt den Fehler:
ERROR: syntax error at or near “00”
SQL Status:42601

Man darf das “Schlüsselwort” “Time” nicht verwenden, dann gehts !

DANKE AN ALLE !!!

Das stimmt nicht. Ich weiß aber auch nicht woran es sonst liegt.
Ich fasse den Fehler nochmal zusammen:

CREATE TABLE t_value (
Id INTEGER , /* Sequentially generated index /
Key DOUBLE PRECISION , /
Key of entry (optional) /
zzTime TIMESTAMP , /
Timestamp of value /
BatchId INTEGER , /
Referenced batch /
CharacteristicId INTEGER , /
Referenced characteristic /
Value DOUBLE PRECISION , /
Measure value */
UNIQUE ( Id )
);
ALTER TABLE t_value OWNER TO postgres;
–FUNCTION
CREATE OR REPLACE FUNCTION t_value_inserttrigger() RETURNS TRIGGER AS ’
DECLARE
MonatsTabelle text;
aktuellerMonat double precision;
aktuellesJahr double precision;
MonatsDatum text;
naechsterMonat text;
TimeStampAktuellerMonatAnfang timestamp;
TimeStampNaechsterMonatAnfang timestamp;
MonatsTabelleExistiertBereits boolean;
BEGIN
aktuellerMonat := date_part(‘‘month’’, CURRENT_TIMESTAMP);
naechsterMonat := to_char(CURRENT_TIMESTAMP + interval ‘‘1 month’’ ,‘‘MM’’);
aktuellesJahr := date_part(‘‘year’’, CURRENT_TIMESTAMP);
TimeStampAktuellerMonatAnfang := date_trunc(‘‘month’’, CURRENT_TIMESTAMP);
TimeStampNaechsterMonatAnfang := date_trunc(‘‘month’’, CURRENT_TIMESTAMP + interval ‘‘1 month’’);
MonatsDatum := aktuellesJahr || aktuellerMonat;
MonatsTabelle := ‘‘t_value_’’ || MonatsDatum;

–Prüfen, ob die MonatsTabelle schon existiert
IF EXISTS (SELECT relname FROM pg_class WHERE relname = MonatsTabelle)

–hier “”“”“”““einfach nur””“”“”“” alles einfügen falls es mal klappt…
THEN

ELSE
EXECUTE ''CREATE TABLE ‘’ || MonatsTabelle || ‘’ (CHECK ( zzTime >= current_time AND zzTime < current_time ) ) INHERITS (t_value) ‘’;
EXECUTE ''ALTER TABLE ‘’ || MonatsTabelle || ‘’ OWNER TO postgres ‘’;

EXECUTE ‘‘INSERT INTO ‘’ || MonatsTabelle || ‘’ (Id, Key) VALUES (’’ || New.Id || ‘’,‘’ || New.Key || ‘’)‘’; --Diese Zeile führt er aus
EXECUTE ‘‘INSERT INTO ‘’ || MonatsTabelle || ‘’ (zzTime) VALUES (’’ || New.zzTime || ‘’)‘’; --Hier bringt er den Fehler
END IF;

RETURN NULL;
END; ’ LANGUAGE plpgsql;
ALTER FUNCTION t_value_inserttrigger() OWNER TO postgres;

–TRIGGER erstellen und ihm sagen, was er wann machen soll
CREATE TRIGGER insert_t_value_trigger
BEFORE INSERT ON t_value
FOR EACH ROW EXECUTE PROCEDURE t_value_inserttrigger();

Der besagt Fehler:
ERROR: syntax error at or near “13”
SQL Status:42601

kommt bei diesem Test-INSERT:
INSERT INTO t_value (Id, Key, zzTime, BatchId, CharacteristicId, Value)
VALUES (1,257,‘2010-04-05 13:39:34.312’,4,1,0);

Ich bin ziemlich sicher, dass er mit der Uhrzeit in der Variable zzTime nicht klar kommt. Aber warum ???
Es ist doch alles was damit zu tun hat TIMESTAMP… ?

Weiß jemand Rat?

Deine doppelten ‘’ in der Funktion lassen mich vermuten, daß Dir Dollar-Quoting noch nicht bekannt ist. Daran solltest Du mal arbeiten.
PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Tips for Developing in PL/pgSQL

Um zu schauen, was Dein SQL-String wirklich enthält, ist es sinnvoll, sich eine TEXT-Variable zu definieren und in dieser das auszuführende SQL abzulegen. Dann kann man mit RAISE NOTICE ‘%’, variable; sich das mal anzeigen lassen. Vereinfacht die Fehlersuche drastisch.

Andreas

Danke Andreas, ich konnte erst nicht glauben, dass es an den Hochkommas liegt…
doch du hattest natürlich Recht.
So muss die Zeile aussehen:

EXECUTE ‘‘INSERT INTO ‘’ || MonatsTabelle || ‘’ (Id, Key, zzTime) VALUES (’’ || New.Id || ‘’,‘’ || New.Key || ‘’,‘’‘’‘’ || New.zzTime || ‘’‘’‘’)‘’;

man muss Variablenwerte, die hochkommas haben müssen, in ein 6-faches Hochkomma packen o.O )
‘’‘’‘’ || New.zTime || ‘’‘’‘’ wird so zu ‘2010-04-01 13:33:37.123’

Ich bin der Meinung, dass DU das sofort erkannt hast und mir hättste sagen können. Dann hätte ichs mir aber nicht selbst erarbeitet…
DANKE ,frohes Ostereiersuchen & schööööönen Urlaub wünsch ich euch allen und besonders den beiden Andreas’s !!!

Um ein String-Literal als TIMESTAMP kenntlich zu machen musst Du vor den String das Schlüsselwort TIMESTAMP stellen:

INSERT INTO t_value (Id, Key, zzTime, BatchId, CharacteristicId, Value)
VALUES (1,257, TIMESTAMP '2010-04-05 13:39:34.312', 4,1,0);