PostgreSQL Tips

PostgreSQL legt mit jedem SERIAL Type ein „SEQUENCE“ genanntes Datenbankobjekt an:

psql=> CREATE TABLE "customer" ("id" SERIAL);

NOTICE: CREATE TABLE will create implicit sequence 'customer_id_seq' for SERIAL column 'customer.id'

NOTICE: CREATE TABLE / UNIQUE will create implicit index 'customer_id_key' for table 'customer'

Eine mit Hilfe von SERIAL erzeugte SEQUENCE trägt immer den Namen tabelle_spalte_seq. Eine SEQUENCE liefert Werte vom Data Type INTEGER, die für die entsprechende Spalte verwendet werden.

Inhalt

Der Zugriff auf eine SEQUENCE erfolgt in der Regel über (in PostgreSQL vorhandene) Funktionen, hauptsächlich nextval() und currval(). Zur Manipulation von „SEQUENCE“ Werten wird setval() verwendet.

nextval('sequence-name') erhöht den aktuellen Wert der SEQUENCE und liefert das Ergebnis zurück:

psql=> INSERT INTO customer VALUES (nextval('customer_id_seq'));

INSERT 32523 1


psql=> SELECT * FROM customer;


 id
----
  1
(1 row)

currval('sequence-name') liefert den aktuellen Wert der SEQUENCE zurück. Wichtig: currval('sequence-name') kann nur erfolgreich ausgeführt werden, wenn in der selben Session mindestens einmal nextval('sequence-name') ausgeführt wurde.

Um beispielsweise einen Eintrag hinzuzufügen und ihn anschließend darzustellen, werden folgende Befehle benutzt:

psql=> INSERT INTO customer VALUES (nextval('customer_id_seq'));

INSERT 32524 1


psql=> SELECT * FROM customer WHERE id = currval('customer_id_seq');


 id
----
  2
(1 row)

Die Funktion nextval() wird übrigens von PostgreSQL implizit verwendet, wenn als Data Type SERIAL angegeben ist:

psql=> \d customer
                            Table "customer"
 Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------
 id     | integer | not null default nextval('"customer_id_seq"'::text)

Wichtig: Werte des Data Types SERIAL sollten bei einem INSERT entweder gar nicht (also per DEFAULT) oder explizit per nextval() gesetzt werden, niemals explizit literal, wie das folgende Beispiel zeigt:

psql=> CREATE TABLE "buggy" ("id" SERIAL);

NOTICE: CREATE TABLE will create implicit sequence buggy_id_seq' for SERIAL column buggy.id'

NOTICE: CREATE TABLE / UNIQUE will create implicit index 'buggy_id_key' for table 'buggy'


psql=> INSERT INTO buggy VALUES (10);

INSERT 33524 1


psql=> SELECT nextval('buggy_id_seq') AS id;

 id
----
  1
(1 row)

Inhalt

Die Definition eines Data Type SERIAL führt zum automatischen Anlegen einer SEQUENCE. Eine SEQUENCE kann aber auch explizit mit Hilfe des Befehls CREATE SEQUENCE sequence-name oder CREATE TEMPORARY SEQUENCE sequence-name angelegt werden. Letzterer Befehl legt die SEQUENCE nur für die aktuelle Session an.

CREATE SEQUENCE kann mit optionalen Argumenten aufgerufen werden, die weitere Eigenschaften der SEQUENCE festlegen (siehe Eigenschaften einer „SEQUENCE“).

DROP SEQUENCE sequence-name löscht eine vorhandene SEQUENCE. Wichtig: Ein DROP TABLE löscht nicht evtl. aufgrund eines SERIAL Types vorhandene SEQUENCES. Diese müssen explizit gelöscht werden:

psql=> DROP TABLE customer;

DROP


psql=> SELECT nextval('customer_id_seq') AS id;


 id
----
  3
(1 row)


psql=> DROP SEQUENCE customer_id_seq;

DROP


psql=> SELECT nextval('customer_id_seq') AS id;

ERROR: pg_aclcheck: class "customer_id_seq" not found

Inhalt

Der psql Client listet mit \d sowohl TABLES als auch SEQUENCES auf. \ds listet nur SEQUENCES (ein sequence-name kann optional angegeben werden).

Die Eigenschaften einer SEQUENCE lassen sich ganz normal mit Hilfe eines SELECT ermitteln:

psql=> CREATE TABLE "customer" ("id" SERIAL);

NOTICE: CREATE TABLE will create implicit sequence 'customer_id_seq' for SERIAL column 'customer.id'
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'customer_id_key' for table 'customer'


psql=> \x

Expanded display is on.


psql=> SELECT * FROM customer_id_seq;

-[ RECORD 1 ]-+--------------------
sequence_name | customer_id_seq
last_value    | 1
increment_by  | 1
max_value     | 9223372036854775807
min_value     | 1
cache_value   | 1
log_cnt       | 1
is_cycled     | f
is_called     | f

Der Output zeigt die Default-Werte, die beim impliziten Anlegen der SEQUENCE durch die Definition eines Data Types SERIAL, aber auch durch eine einfaches CREATE SEQUENCE sequence-name verwendet werden.

last_value den höchsten, für das derzeitige Backend reservierten Wert der SEQUENCE (siehe auch cache_value). Dies ist unabhängig davon, ob schon ein nextval() durchgeführt wurde, weshalb der Wert in diesem Beispiel 1 beträgt und nicht – wie man hätte erwarten können – 0.

increment_by zeigt die Intervalle an, in denen der SEQUENCE Wert durch Aufruf von nextval() verändert wird. Dieser Wert kann selbstverständlich auch negativ sein („rückwärts zählen“).

max_value und min_value stellen den maximalen resp. minimalen Wert der SEQUENCE dar.

cache_value repräsentiert die Anzahl an SEQUENCE-Werten, die für ein Backend reserviert sind. Der Wert 1 bedeutet, daß immer nur ein Wert reserviert ist, also kein Caching stattfindet. Höhere Werte führen zwar evtl. zu Geschwindigkeitsgewinnen, hinterlassen aber Lücken in der SEQUENCE, wenn nicht alle Werte genutzt werden. Beispiel: cache-value sei 5, die SEQUENCE sei neu erzeugt. Backend A beginnt eine Session und reserviert die Werte 1-5. Backend B beginnt eine Session und reserviert die Werte 6-10. Nutzt Backend A drei SEQUENCE Werte und Backend B mindestens einen, dann sind in SEQUENCE die Werte 1-3 und 6 benutzt, 4 und 5 jedoch nicht.

log_cnt ist ein Wert ohne Relevanz für den Nutzer und wird von PostgreSQL intern verwendet.

is_cycled ist ein BOOLEAN Wert, der eine Rolle spielt, wenn max_value (oder auch min_value) erreicht ist. Ist is_cycled FALSE dann liefert PostgreSQL einen Fehler, wenn max_value überschritten (oder min_value unterschritten) wird. Bei is_cycled TRUE folgt in der SEQUENCE auf max_value min_value (resp. auf min_value max_value).

is_called ist ein BOOLEAN Wert, der anzeigt, ob auf die SEQUENCE in der aktuellen Session bereits zugegriffen wurde. In unserem Falle ist er FALSE. Nach einem Aufruf von nextval() wird der Wert TRUE.

Einige der Attribute einer SEQUENCE lassen sich mit CREATE SEQUENCE explizit setzen:

psql=> CREATE TEMPORARY SEQUENCE test_seq
psql->        INCREMENT 10
psql->        MINVALUE  100
psql->        MAXVALUE  1000
psql->        START     500
psql->        CACHE     1
psql->        CYCLE;

CREATE


psql=> SELECT * FROM test_seq;


-[ RECORD 1 ]-+--------------------
sequence_name | test_id_seq
last_value    | 500
increment_by  | 10
max_value     | 1000
min_value     | 100
cache_value   | 1
log_cnt       | 1
is_cycled     | t
is_called     | f

Inhalt

SEQUENCE-Werte können explizit mit setval('sequence-name',value) gesetzt werden. Der nächste Aufruf von nextval() liefert dann value + 1 zurück. Ein setval('sequence-name',value,false) hingegen liefert value zurück:

psql=> \x

Expanded display is off.


psql=> SELECT setval('customer_id_seq',100) AS id;


id
-----
100
(1 row)


psql=> SELECT nextval('customer_id_seq') AS id;


id
-----
101
(1 row)


psql=> SELECT setval('customer_id_seq',200,false) AS id;


id
-----
200
(1 row)


psql=> SELECT nextval('customer_id_seq') AS id;


id
-----
200
(1 row)

Wichtig: Wird setval() verwendet, ist nicht mehr sichergestellt, daß nextval() einen eindeutigen Wert zurückliefert. Dies kann zu Fehlern führen, wenn Spalten mit dem Attribut „UNIQUE“ von einer SEQUENCE gefüttert werden.

Inhalt

Transactions sind immer dann das Mittel der Wahl, wenn mehrere Datenbank-Manipulationen nur einen Sinn ergeben, wenn sie alle ausgeführt werden (oder keine). Um ein klassisches Beispiel zu bemühen: Von einem Konto soll ein bestimmter Betrag auf ein anderes Konto transferiert werden:

psql=> CREATE TABLE "account" ("name" VARCHAR, "saldo" FLOAT);

CREATE


psql=> INSERT INTO account VALUES ('Ulf',+100);

INSERT 32556 1


psql=> INSERT INTO account VALUES ('Alex',+500);

INSERT 32557 1

psql=> SELECT * FROM account;

name | saldo
------+-------
Ulf  |   100
Alex |   500
(2 rows)


psql=> UPDATE account SET saldo = saldo-50 WHERE name='Ulf';

UPDATE 1


psql=> UPDATE account SET saldo = saldo+50 WHERE name='Alex';

UPDATE 1


psql=> SELECT * FROM account;


name | saldo
------+-------
Ulf  |    50
Alex |   550
(2 rows)

Probleme treten hier auf, wenn zwischen dem Abzug des Betrags (1. UPDATE Statement) und dem Zuschlag des Betrags (2. UPDATE Statement) ein Fehler auftritt, etwa die Verbindung zur Datenbank abreißt oder Backend oder Frontend abstürzen.

Noch gravierender wird das Problem, wenn viele Tupel betroffen sind. Wäre die obige Tabelle tatsächlich in Gebrauch (hätte sie also mehr als zwei Tupel), dann wäre die Datenkonsistenz bei einem Absturz währen eines UPDATE account SET saldo = saldo+50 WHERE saldo < 1000; praktisch nicht mehr herzustellen.

Inhalt

Eine Transaction wird mit BEGIN WORK eingeleitet (WORK kann auch weggelassen oder durch TRANSACTION ersetzt werden, das spielt keine Rolle.). Abgeschlossen wird die Transaction mit COMMIT WORK (WORK ist wiederum optional und kann weggelassen oder durch TRANSACTION ersetzt werden.). Alle Änderungen nach einem BEGIN WORK werden erst geschrieben (und sind erst von außen sichtbar), wenn COMMIT WORK abgesetzt wurde.

psql=> BEGIN WORK;


psql=> UPDATE account SET saldo = saldo-50 WHERE name='Ulf';

UPDATE 1


psql=> UPDATE account SET saldo = saldo+50 WHERE name='Alex';

UPDATE 1


psql=> COMMIT WORK;


psql=> SELECT * FROM account;


name | saldo
------+-------
Ulf  |     0
Alex |   600
(2 rows)

Um eine Transaction abzubrechen, verwendet man ROLLBACK WORK (WORK = optional). Keine der Änderungen nach BEGIN WORK wird nun in die Datenbank geschrieben.

psql=> SELECT * FROM account;

name | saldo
------+-------
Ulf  |     0
Alex |   600
(2 rows)

psql=> BEGIN WORK;


psql=> UPDATE account SET saldo = saldo-50 WHERE name='Ulf';

UPDATE 1


psql=> UPDATE account SET saldo = saldo+50 WHERE name='Alex';

UPDATE 1


psql=> ROLLBACK WORK;


psql=> SELECT * FROM account;


name | saldo
------+-------
Ulf  |     0
Alex |   600
(2 rows)

Inhalt

Das folgende Beispiel zeigt eine Implementierung von Transaction in PHP, läßt sich aber sinngemäß auch auf andere Sprachen übertragen.

function transaction($statement) {

  $this->connect();
  $this->queryID = pg_exec($this->linkID, $statement);
  $this->row = 0;
  $this->errormessage = pg_errormessage();

  if (!$this->queryID):
    $msg = "<strong>Invalid SQL:</strong> ". $statement."|";
    $msg .= ereg_replace("([^:]*:)(.*)", "<strong>\\1</strong>\\2", $this->errormessage)."|";
    $this->queryID = pg_exec($this->linkID, "ROLLBACK WORK");
    if ($this->queryID):
      $this->queryID = "|r|".$msg;
    else:
      $this->queryID = "|n|".$msg;
    endif;
  endif;

  return $this->queryID

}


function failed_transaction($msg) {

  list($tmp,$result,$error,$pgerror) = list('\|',$msg);

  if ($result == "r"):   // transaction failed, rollback successful

    $return = "Transaction failed!";
    // other stuff to do on transaction failure
  else:   // transaction failed, rollback failed
    $return = "Transaction and rollback failed!";
    // other stuff to do on rollback failure

  endif;

  echo $return;
  echo "</td></tr></table></body></html>"
  die();
}


$statement = "BEGIN WORK";
$tmp = $conn->transaction($statement);

if(substr($tmp,0,1) == "|"): failed_transaction($tmp); endif;

// other queries

$statement = "COMMIT WORK";
$tmp = $conn->transaction($statement);
if(substr($tmp,0,1) == "|"): failed_transaction($tmp); endif;

Die Funktion transaction() nimmt ein Statement entgegen und versucht eine Datenbankverbindung aufzubauen (über die Funktion connect(), die hier nicht dargestellt ist). Über diese Verbindung wird versucht das Statement abzusetzen (pg_exec), was bei Erfolg zu einem result index ($this->queryID) führt.

Existiert ein result index wird dieser zurückgegeben und kann normal weiterverarbeitet werden. Gibt es keinen result index, wird – wiederum vermittels pg_exec() – ein ROLLBACK versucht. Nach der Prüfung ob dieser erfolgreich war, wird eine mit Pipes (|) getrennte Fehlermeldung zurückgegeben.

Diese Fehlermeldungen werden von failed_transaction() verarbeitet. $result gibt an, ob der ROLLBACK gelang, $error enthält das verursachende SQL-Statement und $pgerror die Fehlermeldung von PostgreSQL. Nach evtl. Ausgabe, Verständigung des Administrators etc. wird das Script beendet (die()).

Bei der Anwendung wird zunächst ein Statement an transaction() übergeben und anschließend geprüft, ob der Rückgabewerte mit einer Pipe (|) beginnt, sprich die Aktion nicht erfolgreich war. Gegebenenfalls wird der Rückgabewert (die Fehlermeldung) an failed_transaction() übergeben.

Inhalt