Neue Tabelle (Struktur) erstellen als Kopie der Referenztabelle

Hallo,

in meinem Projekt habe habe ich hunderte von Tabellen, die dynamisch erstellt werden.
So weit so gut.

Jetzt läuft ein Cronjob, der Pl/PgSQL Funktion startet, die bei bestimmten Bedingungen eine Kopie (nur exact gleiche Struktur ohne Daten) von allen vorhandenen Tabellen erstellen soll.

Z.B. es gibt Tabellen: x01, x02, x03, …
Es sollen automatsch: y01, y02, y03, …

erstellt werden. Falls beim nächsten Durchlauf des Cronjobs schon bestimmte Tabellen vorhanden sind, sollen die natürlich nicht überschrieben werden (z.B. falls y02 schon existiert, soll die übersprungen werden.)

Wie stelle ich das am geschicktlichsten an ?

Also meine Vorgehensweise ist bis jetzt folgende:

a) in der “public” Schema information_schema.tables nachschauen, welche Tabellen es schon gibt
b) nur die x* tabellen in der Schleife durchlaufen
c) in der Schleife: falls y* mit der Nummer nicht existiert, ausführen:
“CREATE TABLE tablename_y ( LIKE tablename_x INCLUDING DEFAULTS )”


[font=monospace]
[/font]DECLARE

    referrer_key RECORD;
    tablename_x varchar(256);
    tablename_y varchar(256);
    rows_num integer;

BEGIN

    FOR referrer_key IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' LOOP

        -- alle andere tabellen ausser die mit 'x' anfangen überspringen
        CONTINUE WHEN position('x' in referrer_key.table_name <> 1;

        tablename_x := referrer_key.table_name;

        tablename_y := 'y' || substring(tablename_x from 2);

        SELECT count(table_name) INTO rows_num FROM information_schema.tables WHERE table_schema = 'public' AND table_name = tablename_y LIMIT 1;
        IF (rows_num = 0) THEN
            EXECUTE 'CREATE TABLE "' || tablename_y || '" ( LIKE "' || tablename_x || '" INCLUDING DEFAULTS )';
        END IF;
    END LOOP;

END;

Probleme:

  1. muss man prüfen, ob die Tabelle y* existiert oder besser so was ähnliches,
    wie CREATE TABLE IF NOT EXISTS … (gibt es das ?), dann ersparre ich mir ein IF (Geschwindigkeit?)

  2. bei dem
    “CREATE TABLE tablename_y ( LIKE tablename_x INCLUDING DEFAULTS )”
    wird zwar eine neue Tabelle mit der gleichen Struktur erstellt,
    aber die Sequenze wird von beiden Tabellen eine und die selbe benutzt.
    Das heisst, wenn ich einen Datensatz in die x/y Tabelle einfüge wird bei dem Gegenstück y/x der Counter auch erhöht.
    Wie kann ich den CREATE TABLE Statement richtig aufbauen, damit die neue Tabelle eine eigene Sequenz bekommt ?


    PS: ich arbeite mit PostgreSQL 8.1.23

Warum braucht man hunderte von Tabellen, und dann auch noch davon Kopien? Dies würde ich erst gern einmal wissen wollen.

Ach ja: 8.1 ist schon etwas alt …


Andreas

Hmm, ja, PG 8.1 ist schon so weit veraltet, das es fast nicht mehr supportet ist.

Um die Methode mit dem Prüfen wirst du nicht drumherum kommen - außer du willst die entsprechende Exception abfangen.
Das ist aber noch aufwendiger, im Sinne der Performance.

Die Frage von akretschmer ist berechtigt: wozu brauchst du Hunderte gleiche Tabellen? Das hört sich nach einem extrem schlechten Datenbankdesign an.

@akretschmer,

das war leider nicht die Antwort auf meine Frage.


@akretschmer, ads

aber die Frage zu beantworten, damit es wieder keine “schlechtes DB-Design”-Anschuldigungen kommen :slight_smile:

Alle diese x1, x2, x3, … Tabellen werden dynamsich erstellt.
Auch wenn die jetzt hier x1, x2, x3, … heissen sind die Tabellen nicht gleich, die alle haben verschiedene Struktur (verschiedene Anzahl an Spalten und verschiedene Spaltentypen), die alle sind in dem Sinne unique was Struktur angeht.
Und nein, die kann man nicht normieren oder vereinheitlichen.
Ich hoffe es reicht als Erklärung, wenn nicht, kann ich nochmal genauer erläutern.

Die Kopien y1, y2, y3, … von den allen Tabellen, brauche ich, um da die optimierte verkleinerte Menge von Daten von den x1, x2, x3 zu speichern.
Und nein, es werden dann keine doppeltgemoppelte Daten gespeichert :slight_smile:

Jede von den Tabellen besitzt auf jeden Fall eine Spalte id und eine Spalte timestamp, und noch undefinierte je nach Tabelle verschiedene Anzahl von real, varchar, … Spalten.
In die x* Tabellen kommen unregelmässig INSERTs (ca. 100 je 5 Minuten pro Tabelle).
Die y* Tabellen werden bei den Real-Spalten den Average Wert ausrechnen (1 pro Stunde) und bestimmte Operationen auf andere Spalten durchführen. Danach wird die optimierte Menge aus den x* Tabellen entfernt. Die x*-Tabellen dienen sogesehen als “temp” Tabellen, nur dass die nicht gelöscht werden.

Und ich benutzt PostgreSQL 8.1. weil es leider anders nicht geht. Aufgrund internen Architektur und der Firmenpolitik muss ich dabei bleiben.

PS: ich hoffe ich bekomme noch Antworten auf meine Fragen.

So weit ich selber jetzt rausgefunden habe, muss ich bei den
CREATE TABLE … ( LIKE … INCLUDE INDEXES )
anstatt INCLUDE DEFAULTS machen, damit die y* Tabellen auch eigene Sequenze bekommen. Das geht aber erst ab PostgreSQL Version 8.3.
Deswegen suche ich noch andere Möglichkeit das zu lösen.

schöne Grüße,
Sergius

Du entwickelst gerade ein Produkt auf einer Datenbank, deren EOL bereits letztes Jahr war.

PostgreSQL Release Support Policy - PostgreSQL wiki

An der Stelle habe ich eigentlich wenig Mitleid für deine Probleme, sorry.

“Und ich benutzt PostgreSQL 8.1. weil es leider anders nicht geht. Aufgrund internen Architektur und der Firmenpolitik muss ich dabei bleiben.”

Ich habe auch kein Mitleid erwartet, aber gut, danke wenigstens für die erliche Antwort.

Du solltest ev. anfangen, die Probleme innerhalb deiner Firma zu lösen.
Welcher Verantwortliche kann bei der Entwicklung auf eine Software setzen, die schon nicht mehr supported ist? Firmenpolitik hin oder her.

Hmm, ok, ich habe deine Meinung zur Kenntnis genommen.
Das ist hier aber nicht das Forum, um Disskussionen über die Firmenpolitik zu führen.
Deswegen würde ich an der Stelle dann versuchen mein Problem weiter selber zu lösen.

Du kannst einer Sequence IIRC nicht ansehen, ob diese impliziet oder expliziet erstellt wurde. Daher geht das auch nicht in aktuelleren Versionen. Das ist aber kein Freibrief dafür, bereits klinisch tote Versionen zu verwenden.

Andreas