Re: Strukturproblem mit Hierarchie

Hi,

als MySQL Geschädigter ist man doch von der Komplexität von PostgreSQL
überrascht. Positiv zwar, aber ein paar der Konzepte wie Cursor,
Transactions, Auto-Commit (ist veraltet), etc. kenne ich nur entfernt
vom Hörensagen, bzw. weiß ich grob was sich dahinter verbirgt.

Gibt es eine kurze Übersicht über PostgreSQL, am besten für
MySQL-Umsteiger? Muss nicht in Deutsch verfasst sein.

Viele Grüße,
Dirk

Eine kurze Übersicht kenne ich leider nicht. Da ich aber sowohl MySQL
und PostgreSQL kenne, empfehle ich Dir die PostgreSQL-Dokumentation.

Wenn es schnell gehen soll, gehst Du am besten wie folgt vor:

1. Schau Dir mal die Datentypen an, die es so gibt. (Kapitel II)
2. als nächstes mal die Funktionen und Operatoren "überfliegen" (Kapitel II)
3. Im Referenzteil (Kapitel VI) mal die Kommandos anschauen, die Dir von
MySQL her
unbekannt sind als auch die folgenden Kommandos:
3a. Unter "CREATE TABLE" findest Du eine Menge über
Fremdschlüssel und Constraints und weitere Verweise.
3b. "SELECT" zeigt eine Menge von Abfragen, welche es unter MySQL
nicht gibt
4. Wichtig: Ein Blick in die FAQ
(http://www.postgresql.org/docs/faqs/FAQ.html)

Somit hast Du zumindest einen groben Überblick, was es so gibt und was
anders ist.

Mit bestem Gruß:
Janko Richter

Dirk Olbertz wrote:

>Hi,
>
>als MySQL Geschädigter ist man doch von der Komplexität von PostgreSQL
>überrascht. Positiv zwar, aber ein paar der Konzepte wie Cursor,
>Transactions, Auto-Commit (ist veraltet), etc. kenne ich nur entfernt
>vom Hörensagen, bzw. weiß ich grob was sich dahinter verbirgt.
>
>Gibt es eine kurze Übersicht über PostgreSQL, am besten für
>MySQL-Umsteiger? Muss nicht in Deutsch verfasst sein.
>
>Viele Grüße,
> Dirk
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>

Hi Janko,

vielen Dank schonmal für diese Zusammenstellung. Ich werde mich mal da
reinhängen.

Ich werde die libpq nutzen und bin im Moment vor allem an den beiden
Konzepten
- Transaktion und
- Cursor
interessiert. Was beides grob ist, weiß ich, ich bin mir nur unsicher,
wie ich mit beiden Dingen umzugehen habe. Dazu mal ein paar konkrete
Frage, vielleicht könnt ihr mir ja eure Standpunkte erklären.

1. Wann sollten Transaktionen genutzt werden?
In diesem Zusammenhang habe ich gelesen, dass AUTOCOMMIT veraltet ist.
Heisst das also, dass jedes Statement mit BEGIN und END verschachtelt
werden soll? Die Befehle für COMMIT und Rollback sind mir bisher noch
nicht untergekommen, wie benutzt ihr dieses Feature?

2. Macht eine Transaktion für SELECT-Statements überhaupt Sinn?

3. Wann sollten Cursor genutzt werden?
Den genauen Unterschied zwischen der Verwendung eines
Abfrageergebnisses mit und ohne Cursor habe ich noch nicht ganz
verstanden. Um nochmal zurück zu MySQL zu kommen: hier gab es die
Möglichkeit entweder alle Zeilen eines Ergebnisses sofort im Speicher
zu haben, oder aber das Ergebnis zeilenweise zu erhalten. Gerade bei
der sehr großen Datenmengen bietet sich letzteres an. Ist das
Cursor-Konzept auch für diesen Zweck gedacht? Und wie unterscheidet es
sich dann von der MySQL-Lösung?

Einige Fragen mögen euch trivial erscheinen, wichtig ist mir aber, das
Gesamtkonzept zu verstehen. Deshalb helfen mir die Einzelbeschreibungen
der PostgreSQL-Eigenschaften nicht immer weiter.

Viele Grüße,
Dirk

In epistula a Dirk Olbertz, die horaque Sat, Sep 25, 2004 at 10:28:40AM -0400:
> Hi Janko,
>
> vielen Dank schonmal für diese Zusammenstellung. Ich werde mich mal da
> reinhängen.
>
> Ich werde die libpq nutzen und bin im Moment vor allem an den beiden
> Konzepten
> - Transaktion und
> - Cursor
> interessiert. Was beides grob ist, weiß ich, ich bin mir nur unsicher,
> wie ich mit beiden Dingen umzugehen habe. Dazu mal ein paar konkrete
> Frage, vielleicht könnt ihr mir ja eure Standpunkte erklären.
>

Hört sich für mich nach "Grundlagenvorlesung relationale Datenbanken"
an, die Dinge, die du ansprichst sind eigentlich nicht
PostgreSQL-spezifisch, sondern gehören zum SQL Standard. MySQL ist
allerdings nicht unbedingt dafür bekannt, sich an den Standard zu
halten.

Beispiele: http://sql-info.de/mysql/gotchas.html

(An alle, die mit PostgreSQL arbeiten: Vor dem Lesen obiger
Seite den Kaffee weg und den Stuhl auf sicheren Untergrund
stellen)

<professorenhut>

Transaktionen dienen erst mal zu zwei Sachen

- Garantie von Atomizität
- Isolation

Das Allerweltsbeispiel zu beidem ist wohl
(sinngemäß übernommen aus [1]):

CREATE TABLE konto (
kontonr VARCHAR(30)
CHECK (kontonr LIKE '[0-9]{2,3} [0-9]{3,3} [0-9]{3,3}')
PRIMARY KEY,
kontostand MONEY
);

Transaktion A Transaktion B

wert := SELECT kontostand
FROM konto
WHERE kontonr = '123-123-123';

wert := SELECT kontostand
FROM konto
WHERE kontonr = '123-123-123';

wert := wert + 10;

wert := wert - 100;

UPDATE konto
SET kontostand = wert
WHERE kontonr = '123-123-123';

UPDATE konto
SET kontostand = wert
WHERE kontonr = '123-123-123';

Das Beispiel ist zwar schlecht, weil man die entsprechende
Operation auch gleich ins UPDATE-Statement einbauen könnte,
aber es illustriert das Grundprinzip.

Auf jeden Fall steht nach Ausführung der obigen Befehle der falsche
Wert in der Datenbank. Packt man die beiden Befehle oben in eine
Transaktion ist das natürlich nicht der Fall.

Bei vielen Datenbanken kommt dann noch dazu, daß gehäufte
Aktualisierungen innerhalb einer Transaktion oftmals schneller
ausgeführt werden, wie ohne.

Bei einzelnen SELECTs bringt eine Transaktion natürlich herzlich
wenig, das ist wohl auch der Grund, warum für viele Web-Anwendungen
MySQL bevorzugt wird. Dort braucht man eigentlich die Fähigkeiten
einer "richtigen" Datenbank nicht, sondern es zählt vor allem die
Tatsache, daß die Abfragen schnell ablaufen.

Transaktionen sind auch für Programmierer interessant, den
die erleichtern die Implementierung von unabhängigen Updates:
Führt man mehrere Aktualisierungen hintereinander durch, kann
es sein, daß eine davon fehlschlägt. Ohne Transaktionsunterstützung
muß man sich dann selbst darum kümmern, die vorhergehenden Operationen
rückgängig zu machen (unter Berücksichtigung, daß andere Prozesse
auch auf dieselben Daten zugreifen können). Mit Transkationen reicht
ein einfaces "ROLLBACK;".

Zu Cursors:

Das muß man sich so vorstellen, als würde man eine Abfrage als
"Datenstrom" erstellen. Innerhalb dieses Datenstroms kann
man dann einen Zeiger (CURSOR) plazieren.

Ich weiß nicht, auf welche Funktionalität du bei MySQL da anspielst,
aber wenn ich richtig rate, meinst du den "LIMIT"-Clause für den
SELECT-Befehl (gibt es auch in PostgreSQL).

Der Vorteil von CURSORs ist ziemlich klar: Sie sind schneller.

Für einen Befehl wie

SELECT *
FROM tabelleA JOIN tabelleB
ON FeldA = FeldB
WHERE FeldC = WertA
LIMIT 1 OFFSET 1999

muß die Datenbank den Befehl erneut interpretieren, einen
Ausführungsplan erstellen, die Datensätze holen und innerhalb
der Datensatzmenge bis zum 1999sten Wert vorspringen um den 2000sten
Wert zu holen. Das ist natürlich viel Aufwand, wenn man es 2000 mal
machen muß, um einzeln die Datensätze von 1 bis 2000 zu holen.

Mit CURSORs kann man einfach jedesmal

FETCH NEXT FROM <cursor_name>;

schreiben. Das erspart der Datenbank das Aufstellen des Query-Planes
(Die Abfrage ist ja schon bekannt) und einen Großteil der anderen
Operationen. Die Datenbank muß nur noch wissen, wie sie in einem
bekannten Datensatz"strom" eine Zeile vorwärts kommt.

</professorenhut>

Ich hoffe, das hilft dir ein wenig beim Verständis, ansonsten
empfehle ich das Skript der Datenbankvorlesung einer beliebigen
Fachhochschule/Universität.

Gruß,
Peter

[1] Abraham Silberschatz, Henry F. Korth, S. Sudarshan,
Database System Concepts, 4th Edition, McGraw-Hill,
ISBN 0-07-112268-0

--
Miteinander zu sprechen ist besser als gegeneinander zu schweigen.
-- Ignazio Silone

Hallo,

Am 25.09.2004 um 12:31 schrieb Peter Wullinger:
> Hört sich für mich nach "Grundlagenvorlesung relationale Datenbanken"
> an, die Dinge, die du ansprichst sind eigentlich nicht
> PostgreSQL-spezifisch, sondern gehören zum SQL Standard. MySQL ist
> allerdings nicht unbedingt dafür bekannt, sich an den Standard zu
> halten.

Das ist jetzt zwar peinlich für mich, aber die Vorlesung
"Informationssysteme" habe ich vor knapp 6 Jahren durchaus besucht. Die
Begriffe der Transaktion, Atomizität, etc. sind mir also zumindest
theoretisch bekannt. Ich danke Dir aber trotzdem nochmal für die
Auffrischung und die Bestätigung, dass Transaktionen bei
Select-Statements keinen Sinn machen. Ich habe mir noch zusätzlich die
entsprechenden Kapitel in der Dokumentation durchgelesen.

> Transaktionen sind auch für Programmierer interessant, den
> die erleichtern die Implementierung von unabhängigen Updates:
> Führt man mehrere Aktualisierungen hintereinander durch, kann
> es sein, daß eine davon fehlschlägt. Ohne Transaktionsunterstützung
> muß man sich dann selbst darum kümmern, die vorhergehenden Operationen
> rückgängig zu machen (unter Berücksichtigung, daß andere Prozesse
> auch auf dieselben Daten zugreifen können). Mit Transkationen reicht
> ein einfaces "ROLLBACK;".

Hierzu noch eine Frage: Ist es möglich, Transaktionen über mehrere
Sessions hinweg zu halten? Mit Sessions meine ich Datenbankverbindungen
eines Programmes.

> Zu Cursors:
>
> Das muß man sich so vorstellen, als würde man eine Abfrage als
> "Datenstrom" erstellen. Innerhalb dieses Datenstroms kann
> man dann einen Zeiger (CURSOR) plazieren.
>
> Ich weiß nicht, auf welche Funktionalität du bei MySQL da anspielst,
> aber wenn ich richtig rate, meinst du den "LIMIT"-Clause für den
> SELECT-Befehl (gibt es auch in PostgreSQL).

Ich hatte da eher auf den Unterschied zwischen mysql_fetch_result und
mysql_store_result angespielt. Dabei bin ich mir jetzt unischer, ob das
nur eine Interface-spezifische Eigenschaft ist, oder grundsätzlich bei
MySQL unterschieden wird. Bei der einen Operation wird nämlich sofort
das gesamte Ergebnis des Select-Statements an den Client übertragen und
dort wird dann durch die Ergebnismenge iteriert und bei der anderen
wird immer nur eine einzelne Zeile aus dem Ergebnis übertragen.

> FETCH NEXT FROM <cursor_name>;
>
> schreiben. Das erspart der Datenbank das Aufstellen des Query-Planes
> (Die Abfrage ist ja schon bekannt) und einen Großteil der anderen
> Operationen. Die Datenbank muß nur noch wissen, wie sie in einem
> bekannten Datensatz"strom" eine Zeile vorwärts kommt.

Ich zitiere mal die Dokumentation: " Rather than executing a whole
query at once, it is possible to set up a cursor that encapsulates the
query, and then read the query result a few rows at a time. One reason
for doing this is to avoid memory overrun when the result contains a
large number of rows. "

Also bekomme ich bei einem normalen Aufruf eines Select-Statements die
Daten komplett, mittels der Cursor kann ich aber auch bei sehr großen
Ergebnismengen flexibel sein. Das gefällt mir, weil es nicht auf der
Ebene der Datenbankabstraktion meiner Applikation gemacht werden muss,
sondern dort, wo die Anfrage anfällt und wo dann eher bekannt ist, wie
groß die Rückgabemengen sein können.

Viele Grüße,
Dirk

In epistula a Dirk Olbertz, die horaque Sat, Sep 25, 2004 at 07:25:08PM -0400:
> Hallo,
>
> Am 25.09.2004 um 12:31 schrieb Peter Wullinger:
> >Hört sich für mich nach "Grundlagenvorlesung relationale Datenbanken"
> >an, die Dinge, die du ansprichst sind eigentlich nicht
> >PostgreSQL-spezifisch, sondern gehören zum SQL Standard. MySQL ist
> >allerdings nicht unbedingt dafür bekannt, sich an den Standard zu
> >halten.
>
> Das ist jetzt zwar peinlich für mich, aber die Vorlesung
> "Informationssysteme" habe ich vor knapp 6 Jahren durchaus besucht. Die
> Begriffe der Transaktion, Atomizität, etc. sind mir also zumindest
> theoretisch bekannt. Ich danke Dir aber trotzdem nochmal für die
> Auffrischung und die Bestätigung, dass Transaktionen bei
> Select-Statements keinen Sinn machen. Ich habe mir noch zusätzlich die
> entsprechenden Kapitel in der Dokumentation durchgelesen.
>

Ne, das ist nicht peinlich, das ist normal ;-).

Sollte mich heute jemand nach Quine-McKluskey (Minimierungsverfahren für
boolsche Ausdrücke) fragen, hätte ich jetzt erst mal größere Probleme.

[snip]

> Hierzu noch eine Frage: Ist es möglich, Transaktionen über mehrere
> Sessions hinweg zu halten? Mit Sessions meine ich Datenbankverbindungen
> eines Programmes.

Das nicht, nein. Das liegt allerdings wohl hauptsächlich daran, daß die
Datenbank keine Möglichkeit außer der Verbindung zum Server hat, einen
Client zu identifizieren. So etwas wie eine benutzer-sichtbare
Transaktionsnummer gibt es also nicht.

[snip]

>
> Ich hatte da eher auf den Unterschied zwischen mysql_fetch_result und
> mysql_store_result angespielt. Dabei bin ich mir jetzt unischer, ob das
> nur eine Interface-spezifische Eigenschaft ist, oder grundsätzlich bei
> MySQL unterschieden wird. Bei der einen Operation wird nämlich sofort
> das gesamte Ergebnis des Select-Statements an den Client übertragen und
> dort wird dann durch die Ergebnismenge iteriert und bei der anderen
> wird immer nur eine einzelne Zeile aus dem Ergebnis übertragen.

Liest sich für mich, als wäre das eine Art "CURSOR-Ersatz" ohne
entsprechende SQL-Unterstützung. Ich kenn' mich jetzt da nicht aus,
aber ich denke, diese Funktionen sind nur über die MySQL-eigenen
Bibliotheken nutzbar und nicht über Datenbank-unabhängige
Schnittstellen wie ODBC.

Aber da meine Kenntnisse von MySQL sich auf "wie starte ich den
Datenbank-Server" begrenzen, ist das keine zuverlässige Aussage.

[snip]

> Also bekomme ich bei einem normalen Aufruf eines Select-Statements die
> Daten komplett, mittels der Cursor kann ich aber auch bei sehr großen
> Ergebnismengen flexibel sein. Das gefällt mir, weil es nicht auf der
> Ebene der Datenbankabstraktion meiner Applikation gemacht werden muss,
> sondern dort, wo die Anfrage anfällt und wo dann eher bekannt ist, wie
> groß die Rückgabemengen sein können.

Richtig. Für häufig benutzte Queries hat PostgreSQL inzwischen
auch noch einen weiteren Leckerbissen:

http://www.postgresql.org/docs/7.4/static/sql-prepare.html
http://www.postgresql.org/docs/7.4/static/sql-execute.html

Pre-Compiled Queries ...

Gruß,
Peter
--
Jetzt sind die guten alten Zeiten, nach denen wir uns
in zehn Jahren zurücksehnen.
-- Sir Peter Ustinov

hallo,

ich habe hier ein Strukturproblem.

Die folgende Hierarchie soll in der Datenbank abgebildet werden:

- Projekte
- Kunde A
- Projekt B
- Project C

Dazu habe ich mir die folgende Tabelle angelegt:

id parent_id title
1 null Projekte
2 1 Kunde A
3 2 Projekt B
4 2 Projekt C

Die Ausgabe der Daten soll später wie folgt erscheinen:

Projekte / Kunde A / Projekt B

Ich würde das jetzt über eine Stored Procedure lösen (n-Queries abfragen).
Ihr auch?

Kann ein SQL-Query rekursiv über Datensätze gehen? Eher nicht, oder?

Geht es vielleicht noch eleganter?

Danke fürs reinschauen ,o

Bis dann,
Martin

In epistula a Martin Fleck, die horaque Sun, Sep 26, 2004 at 09:10:09PM +0200:
>
> hallo,
>
> ich habe hier ein Strukturproblem.
>
>
> Die folgende Hierarchie soll in der Datenbank abgebildet werden:
>
> - Projekte
> - Kunde A
> - Projekt B
> - Project C
>
>
> Dazu habe ich mir die folgende Tabelle angelegt:
>
> id parent_id title
> 1 null Projekte
> 2 1 Kunde A
> 3 2 Projekt B
> 4 2 Projekt C
>
>
> Die Ausgabe der Daten soll später wie folgt erscheinen:
>
> Projekte / Kunde A / Projekt B
>
>
> Ich würde das jetzt über eine Stored Procedure lösen (n-Queries abfragen).
> Ihr auch?

Ich nenn die Tabelle jetzt mal "operation", weil mir nichts
besseres einfällt und ich keine Ahnung habe, worum es genau geht.

Wenn du weißt, daß sich deine nicht über drei Ebenen erstreckt kannst
du das alles in eine Abfrage packen, wobei du die Tabelle jeweils mit
sich selbst joinst:

psql: ~# CREATE TABLE operation (
id BIGSERIAL PRIMARY KEY,
parent BIGINT REFERENCES operation (id) DEFAULT NULL,
title VARCHAR NOT NULL
);

psql: ~# INSERT INTO operation (parent, title) VALUES (NULL, 'Projekte');
psql: ~# INSERT INTO operation (parent, title) VALUES (1, 'Kunde A');
psql: ~# INSERT INTO operation (parent, title) VALUES (2, 'Projekt B');
psql: ~# INSERT INTO operation (parent, title) VALUES (2, 'Projekt C');
psql: ~# INSERT INTO operation (parent, title) VALUES (1, 'Kunde B');

id | parent | title
----+--------+-----------
1 | | Projekte
2 | 1 | Kunde A
3 | 2 | Projekt B
4 | 2 | Projekt C
5 | 1 | Kunde A
(4 rows)

psql: ~# SELECT op1.title AS title1, op2.title AS title2, op3.title AS title3
FROM (operation op1 LEFT JOIN operation op2
ON op2.parent = op1.id)
LEFT JOIN operation op3
ON op3.parent = op2.id
WHERE op1.parent IS NULL;

title1 | title2 | title3
----------+---------+-----------
Projekte | Kunde A | Projekt B
Projekte | Kunde A | Projekt C
Projekte | Kunde B |
(3 rows)

>
> Kann ein SQL-Query rekursiv über Datensätze gehen? Eher nicht, oder?
>

Salopp formuliert kann man in einem einzigen SELECT-Query keine
Abfragen machen, bei der die Spalten des Abfrageergebnisses von
den Datensätzen abhängen. Wenn ich also oben eine vierte Spalte
haben will, muß ich das vorher wissen oder anders implementieren.

Wenn ich acht Spalten haben will, hat wenigstens der GeQO was zu tun ;-).

Gruß,
Peter

--
Live is what happens, while we are busy making other plans
-- John Lennon