PostgreSQL 9.2 / vacuum --> duplicate key ???

Hallo,


arbeite momentan an der Evaluierung ob wir PostgreSQL als System-DB (Alternative) in unserer auf J2EE basierten Umgebung einsetzen können.
Bin dabei auf folgendes Problem gestoßen.
Wir nutzen eine Tabelle zur Festschreibung von internen Zählerständen.
… mir ist bewusst dass man auch Sequenzen, … verwenden könnte.


CREATE TABLE tpermcounters
(
cid character(36) NOT NULL,
cname character varying(255) NOT NULL, – the Counter Name
cvalue numeric(19,0) NOT NULL, – current long value of the counter
cenv character(3) NOT NULL DEFAULT ‘000’::bpchar, – Logical System

CONSTRAINT pk_tpermcounters PRIMARY KEY (cid),
CONSTRAINT ix_tpermcounters UNIQUE (cenv, cname)
)
WITH ( OIDS=FALSE);


Die Applikation schreibt multi-threaded in diese Tabelle.
In diesem Test gab es lediglich 2 Records (2 Counter) in dieser Tabelle, wobei nur auf einem gearbeitet wurde.
via:
“UPDATE tPermCounters SET cValue=cValue+”+step+", … WHERE cName=? AND cEnv=?
wobei: step —> increment
Findet der Update keinen entsprechenden Record, wird via INSERT der Record (Counter) angelegt.
Nun zum Problem:
Während meines Tests wird nur immer ein bereits existierender Counter hochgezählt (immer der gleiche)- und das hat auch einige Stunden gut funktioniert.
Jetzt habe ich allerdings folgenden Fehler beim nicht erwarteten INSERT bekommen:
org.postgresql.util.PSQLException: FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »pk_tpermcounters« …
Der Update ist wohl gescheitert und die Applikation hat daraufhin einen Insert durchgeführt. Dieser hat richtigerweise obige SQLException geworfen aber trotzdem einen Record in der Tabelle angelegt. Jetzt habe ich also zwei Records mit gleichem PK in der Tabelle - Ein Reindex schlägt deshalb auch fehl…
Im PG Log habe ich vor dem ersten Auftreten des Problems, folgendes gefunden:
2012-09-17 14:35:25 CEST ZUSAMMENHANG: automatisches Vacuum der Tabelle »XXX.public.tpermcounters«
2012-09-17 14:37:49 CEST FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »pk_tpermcounters«
2012-09-17 14:37:49 CEST DETAIL: Schlüssel »(cid)=(28db0cd0-00ae-11e2-9834-b53c0a006736)« existiert bereits.
Gibt es einen Zusammenhang zwischen “Auto Vacuum” und einer hohen Update/Insert Last auf einer Tabelle welche letztlich zur Dateninkonsistenz führen kann?
Ist das ein bekannter Bug ?
Ich werde die Datenbank noch auf einer Linux (Centos) Umgebung testen um entsprechende Einflüsse (OS / Hardware) auszuschließen.
Das Hochzählen von Zählerwerten ist bei unserer Applikation ein sehr kleiner und optionaler Teil :slight_smile:
Mir geht es also ums Fehlerbild und nicht um diesen konkreten UseCase.


Umgebung:
“PostgreSQL 9.2.0, compiled by Visual C++ build 1600, 64-bit” running on 64bit WindowsXP
jdbc: postgresql-9.1-902.jdbc4.jar
Vielleicht kann mir hierbei jemand weiterhelfen!?

Danke! Gruß Reinhard

Warum in aller Welt nehmt ihr keine Sequencen? Ich hab schon mal so eine Konstruktion gesehen, also eine Tabelle mit diversen Countern. Das war was, was auf zig DB-Systemen ohne Anpassungen laufen sollte. Da es da wohl zu ähnlichen Problemen kam, hat man noch schön Tabellen-Locks drumherum gewickelt. Das performat dann halt nicht, kann man gleich MyISAM nehmen.


Das Vacuum ist da eher nicht Schuld, ich vermute eher, daß da 2 Threads den selben Record bearbeiten wollten, einer schlug dann fehl und Deine Applikation hat das erneut als Insert versucht. Das fehlerfrei UND performant hinzubekommen wird ganz sicher sehr viel schwerer als es auf Sequencen umzustellen - die sind sicher und performant.

Ach ja: ich bezweifle, daß Du jetzt 2 gleiche cid in der Tabelle hast.

Andreas

Das glaube ich nicht. Wenn der Fehler geworfen wird gibt es mit Sicherheit nur einen Datensatz mit diesem Schlüssel danach.

Ein Reindex schlägt deshalb auch fehl…

Kannst Du uns mal das Statement und die exakte Fehlermeldung posten? Evtl. vorher auf Englisch umschalten (SET lc_messages=english)? Ich finde die Englischen Meldungen irgendwie aussagekräftiger und Sonderzeichen gehen beim Posten auch nicht kaputt, weil keine drin sind.


Im PG Log habe ich vor dem ersten Auftreten des Problems, folgendes gefunden:

  2012-09-17 14:35:25 CEST ZUSAMMENHANG:  automatisches Vacuum der Tabelle »XXX.public.tpermcounters«
  2012-09-17 14:37:49 CEST FEHLER:  doppelter SchlÜsselwert verletzt Unique-Constraint »pk_tpermcounters«
  2012-09-17 14:37:49 CEST DETAIL:  Schlüssel »(cid)=(28db0cd0-00ae-11e2-9834-b53c0a006736)« existiert bereits.
>

Zwischen der Meldung, dass ein vacuum ausgeführt wird und dem Unique-Constraint Fehler liegen über 2 Minuten. Für mich sieht das nicht so aus, als würden die zusammengehören.

> Gibt es einen Zusammenhang zwischen "Auto Vacuum" und einer hohen Update/Insert Last auf einer Tabelle welche letztlich zur Dateninkonsistenz führen kann?

Nein, das kann ich mir nicht vorstellen. Wie gesagt: so wie ich die Logmeldungen interpretiere, gehören die beiden Meldungen nicht zusammen.

Und bitte verwende [noparse]

```text
[/noparse] Tags um SQL und Logmeldungen lesbarer zu machen: [url=http://www.pg-forum.de/misc.php?do=bbcode#code]BB-Code Liste - PG-Forum.de[/url]

Ach ja: die Frage warum ihr keine sequence für die Generierung von PK Werten verwendet ist natürlich mehr als berechtigt.

Hallo,
Danke für die Antworten!
Wir verwenden keine Sequenzen, weil wir unterschiedliche DB Systeme unterstützen wollen und weitestgehend auf DB interne Funktionen (Trigger, StoredProcedures, …) verzichten wollen. Ein weiterer Grund ist sicherlich, dass wir hier über ein System reden welches es schon in diversen Evolutionsstufen seit einigen Jahren gibt - in dieser komplexen Umgebung gibt es sicherlich noch Dinge die man besser machen kann. Werde auch die Verwendung von Sequenzen in Betracht ziehen …
Ich habe versucht deutlich zu machen, dass es mir hierbei nicht um den UseCase geht. Es geht mir um das Fehlerbild. Unsere Applikationen läuft 24*7 - muss hoch verfügbar sein. Der Datenbestand bei unseren größeren Kunden geht in den zweistelligen TB Bereich, wobei in der Regel mehrere GB neue Daten pro Tag anfallen. “Alte” Daten werden zyklisch reorganisiert. Ich werde dann halt schon nervös wenn ich eine Tabelle vor mir habe, die zwei identische Records trägt und in diesem Fall den PK und eine zusätzliche UniqueKey constraint verletzt. Ich habe nichts gewonnen wenn die Sequenzen funktionieren und evtl. viel wichtigere Transaktionsdaten in anderen hoch frequentierten Tabellen in Mitleidenschaft gezogen werden.


Der Reindex den ich über pgAdmin III auf die beiden Indizes (PK -->pk_tpermcounters / UniqueKey → ix_tpermcounters) gestern versucht habe ist gescheitert.
Hier ist der entsprechende Auszug im LOG:

2012-09-17 14:58:17 CEST FEHLER:  konnte Unique Index »pk_tpermcounters« nicht erstellen
2012-09-17 14:58:17 CEST DETAIL:  Schlüssel (cid)=(28db0cd0-00ae-11e2-9834-b53c0a006736) ist doppelt vorhanden.
2012-09-17 14:58:17 CEST ANWEISUNG:  REINDEX INDEX pk_tpermcounters
2012-09-17 15:22:47 CEST FEHLER:  konnte Unique Index »ix_tpermcounters« nicht erstellen
2012-09-17 15:22:47 CEST DETAIL:  Schlüssel (cenv, cname)=(000, T-40005-TRACKID) ist doppelt vorhanden.
2012-09-17 15:22:47 CEST ANWEISUNG:  REINDEX INDEX ix_tpermcounters

Ich habe daraufhin die Tabelle via DELETE geleert.


Wir haben ein großes Interesse daran auch PostgreSQL zu unterstützen. Ich habe bereits verschiedene andere nicht kommerzielle DB Systeme angeschaut muss aber klar sagen, dass keine Andere ähnlich vielsprechend ist was Stabilität, Performance und Enterprise Features betrifft.
Ich hatte einen ähnlichen Test vor einiger Zeit auch schon auf einer PostgreSQL 9.1.3 (oder 4) ohne Probleme gefahren.
Werde den Test mit der 9.2.0 wiederholen und dann gegebenenfalls die 9.1.5 testen.

Reinhard

Zu Deutsch: Eure Anwendung läuft auf allen Systemen gleich langsam. Tom Kyte (einer der Oracle Gurus) sagt zu dem Thema PKs “selber generieren”: “You can either get it correct, or fast - but never both

Sequences werde mittlerweile von der Mehrheit der Datenbanken unterstützt (Oracle, PostgreSQL, DB2, SQL Server 2012, Firebird, Informix, Ingres, Cubrid, Vertica, Derby, H2, HSQLDB, …).

Ich würde in so einem Fall in der Anwendung Code haben der “den nächsten PK” von der Datenbank holt und zurückliefert. Für Datenbank die Sequences haben, wird halt ein Select auf die Sequence gemacht, für die wenigen anderen eben mit Table-Locks und ähnlichem gearbeitet.

Ich hatte einen ähnlichen Test vor einiger Zeit auch schon auf einer PostgreSQL 9.1.3 (oder 4) ohne Probleme gefahren.
Werde den Test mit der 9.2.0 wiederholen und dann gegebenenfalls die 9.1.5 testen.

9.2.0 ist ja gerade erst rausgekommen und hat gerade im Bereich Concurrency einige neue Optimierungen drin. Wenn Du ein Script hast, welches den Fehler konsistent reproduzieren kann, dann solltest Du das unbedingt auf der mailing liste posten. Die Postgres Entwickler sind auf jeden Fall stark daran interessiert. Dein Problem dort nochmal zu erklären kann auf keinen Fall schaden. Evtl. hat dort noch jemand eine Idee wie man der Ursache auf den Grund kommen könnte.

… der war nicht schlecht :slight_smile:
Würde auch zutreffen wenn diese Zähler Geschichte zwingend mit dem Durchsatz des Systems zu tun hätte.


Ich habe heute Morgen nach 10 Minuten Testlauf das gleiche Problem wieder.

2012-09-18 06:57:46 CEST LOG:  Datenbanksystem wurde unterbrochen]

.... sehe hier auch permission denied.
Ich habe die Postgres auf einer Windows Maschine laufen und dort ist auch ein Viren-Scanner aktiv (eigentlich worst case scenario, kommt aber auch bei Kunden vor). Der könnte natürlich auch Probleme machen .... Vielleicht funkt der manchmal dazwischen wenn die DB datei - bezogene  Wartungsarbeiten durchführt.
Kann ihn nicht deaktivieren aber habe die PostGres vom realtime scan ausgeschlossen .....
... weitere Tests laufen.

Reinhard

Also ich würde bei dem “Permission denied” auf den Viren-Scanner tippen. Hast Du den so eingestellt, dass das Datenverzeichnis (und alle Unterverzeichnisse) vom Scan ausgeschlossen ist?

Um das auszuschliessen, check doch mal die Zugriffsrechte auf dem Datenverzeichnis. Evtl. sind die so angelegt, dass die Rechte für neue Verzeichnisse nicht vererbt werden.

Wenn Dich die kaputten Umlaute im Logfile stören, dann stell einfach in postgresql.conf “lc_messages = Englisch” ein.

Was ich auch komisch finde, ist die Tatsache, dass dein Postgres beim Starten ein Recovery gemacht hat. Wurde das sauber runtergefahren?

Ach ja: es heisst entweder Postgres, PostgreSQL oder schlicht PG, niemals PostGres :wink:

Yep - habe alle PG :rolleyes: related Verzeichnisse vom Scan ausgeschlossen und auch brav lc_messages auf Englisch gestellt (jetzt heult auch das Server-Status tool nicht mehr wg. Sonderzeichen im PG log.
Der Windows Hobel wird einfach runtergefahren - dies sollte ja auch den PG Dienst sauber stoppen.

Hab mittlerweile schon mehrere Testläufe erfolgreich absolviert.
Das Teil läuft jetzt stabil.

Danke!