Hallo,
ich habe einen anonymen Codeblock wie folgt:
DO $$
BEGIN
INSERT INTO my_table (field1, field2) VALUES (1,2);
END $$;
Das funktioniert. Ich nehme jetzt eine Transaktion hinzu:
DO $$
BEGIN
BEGIN;
INSERT INTO my_table (field1, field2) VALUES (1,2);
COMMIT;
END $$;
Hier erhalte ich folgenden Fehler:
SQL-Fehler [42601]: FEHLER: Syntaxfehler bei »;«
Position: 25
Was mache ich falsch?
Vielen Dank schon mal.
Franz
–
PostgreSQL 16.2
DBeaver 24.0.3
Window 10 Prof
Wie im Handbuch beschrieben müsstest Du START TRANSACTION verwenden.
Aber anonyme Code-Blöcke sind eigentlich Funktionen und in denen sind keine Transaktionen erlaubt. Du musst eine Transaktion vor dem Block starten und danach beenden:
begin transaction;
DO $$
BEGIN
INSERT INTO my_table (field1, field2) VALUES (1,2);
END
$$;
commit;
Okay, und wie geht das beim Rollback? Es muss ja reagiert werden, je nachdem, ob das Insert erfolgreich war.
Anders gefragt: Ich habe mehrere Insert-Anweisungen. Wenn eine davon schief läuft soll alles, was bisher eingefügt wurde zurückgenommen werden. Angenommen in my_table dürfen keine negativen Zahlen gespeichert werden. In T-SQL (MS-SQL-Server) sähe das ungefähr wie folgt aus:
BEGIN TRAN
BEGIN TRY
INSERT INTO my_table (field1, field2) VALUES (1, 2);
INSERT INTO my_table (field1, field2) VALUES (3, 4);
INSERT INTO my_table (field1, field2) VALUES (-5, -6);
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Die dritte Insert-Zeile produziert eine Exception. Damit wird COMMIT übergangen, in den CATCH-Block gesprungen und das ROLLBACK ausgeführt. Wie macht man sowas in Postgres?
Hängt ein bisschen davon ab in welcher Umgebung Du das ausführst. Normalerweise ist ja autocommit aktiv, dann reicht ein:
INSERT INTO my_table
(field1, field2)
VALUES
(1, 2),
(3, 4),
(-5, -6);
Und bei einem Fehler wird automatisch das gesamte Statement zurückgerollt.
Bei psql kannst Du z.B. ON_ERROR_ROLLBACK setzen. Dann startest Du eine Transaktion mit BEGIN TRANSACTION und beim ersten Fehler (wenn Du mehr als ein Statement hast), wird ein ROLLBACK ausgeführt. Am besten noch mit ON_ERROR_STOP kombinieren.
Nachtrag: ein ROLLBACK kannst Du natürlich in einem PL/pgSQL Block machen. Die Transaktion wird aber implizit gestartet, ein START TRANSACTION ist also nicht notwendig.
do
$$
begin
insert into my_table (field1, field2) values (1, 2);
insert into my_table (field1, field2) values (3, 4);
insert into my_table (field1, field2) values (-5, -6);
exception
when others then
rollback;
end;
$$
;
In “anonymen Code-Blöcken” (eigentlich: DO-Statements) sind sehr wohl Transaktionen erlaubt.
Nur kann man keine neuen Transaktionen mit BEGIN starten. Man verwendet die automatisch mit dem Statement gestartete Transaktion.
Das ist also legitim:
DO $$BEGIN
INSERT INTO my_table (field1, field2) VALUES (1,2);
COMMIT;
END$$;
Allerdings ist das COMMIT sinnlos, denn das Ende des DO-Statements beendet automatisch die Transaktion.
Achtung: Transaktionsmanagement funktioniert in DO-Statements nur dann, wenn das DO die Transaktion startet, man also nicht schon vorher eine Transaktion begonnen hat.
Der Exception-Teil ist doch hier überflüssig oder?
Verletzt ein Insert (oder irgendein anderes DML Statement) die Regeln, wird automatisch zurück gerollt.
Man muss sich vielleicht klar machen, was man mit einem (anonymen) Statement erreichen will. M.E. würde man einen anonymous block genau für eine logische Transaktion einsetzen. Also mehrere Statements zusammenfassen, die zusammengehören müssen(!), aus Konsistenzgründen.
Geht man darüber hinaus, ist man auf der Ebene von Business Logik.
Ein Commit mitten im PSQL Code wäre m.E. sowieso allermeistens fürchterlich, weil es zu ungewollten Effekten führen kann, wenn man mehrere Code Blöcke aufruft. Das geschieht allerdings naturgemäß bei einem anonymen Block gar nicht.
Setzt man es dagegen in Functions ein, kann es unangenehm werden. Es würde eine vorgelagerte Transaktion frühzeitig (unvollständig) commiten? Damit wäre man dann hier angelangt:
Das finde ich überraschend bzw. tückisch, wenn man glaubt, das Exception Handling / Transaktionsmanagement funktioniert so, wie es im Code steht.
Ja, ein Fehler führt automatisch zu Rollback.
Deshalb ist das auch nicht erlaubt.
Auch das ist nicht erlaubt, eben weil es zu Inkonsistenzen führen würde. Ich kann nicht mitten in einem Statement (das nicht CALL ist) ein COMMIT machen. Oracle hat solche Skrupel übrigens nicht.
Das verstehe ich nicht ganz. Es funktioniert so, wie es im Code steht. Wenn man ein COMMIT an einer Stelle oder in einer Situation verwendet, wo es nicht geht, bekommt man eine hübsche Fehlermeldung, die einem das unmißverständlich näherbringt. Das freut zwar nicht, ist aber sicher nicht tückisch.
Ah, gut, ich hab das nicht nachvollzogen / noch nie so gemacht. Deine Warnung hatte ich so verstanden, dass ~“innere Transaktionen” dann ignoriert werden.
Aber eine Fehlermeldung ist gut, transparent, nicht heimtückisch.
Das Oracle es anders macht, kenne ich so. Fehler, die auf inneren Commits beruhen, sind dann u.U. sehr schwer zu finden. Das war im eigenen Code also verboten. Ich habe allerdings nicht den kompletten Überblick, ob es z.B. zusammen mit autonomen Transaktionen vielleicht doch Konstellationen geben kann, wo das Sinn macht. Naja, anderes Thema.
Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.