2

Could some kindly soul point out where this is statement is wrong, please? I'm trying to run a very simple transaction test, but can't even get the statement to run. I'm trying to run it in the SQL Query window of PG Admin. There are no other connections to the database.

DO $$
BEGIN

START TRANSACTION;

UPDATE IsolationTests SET Col1 = 2;
perform pg_sleep(5.0);

ROLLBACK;

EXCEPTION WHEN division_by_zero THEN
BEGIN
END;

END $$;

I want to play around with the transaction level and start and end a transaction in the code. If I comment out the "START TRANSACTION" & "ROLLBACK" the statment runs fine. Uncomment them and I get

"ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead."

Uh? It's in a BEGIN ... EXCEPTION block, isn't it??

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Adam Benson
  • 7,480
  • 4
  • 22
  • 45
  • 2
    In PL/pgSQL `BEGIN ... END` is **not** about transactions. It's a syntax block. Like e.g `{... }` in Java or C++ a `DO` block is an anonymous function and you can't have transaction control in a function in Postgres (or SQL Server for that matter). Unfortunately Postgres has no stored procedures, so you always need to manage the transaction in the calling code –  Feb 02 '17 at 11:07
  • it doesn't like `START TRANSACTION;` in PlPgSql, not begin in exception block – Vao Tsun Feb 02 '17 at 11:13
  • @ a_horse_with_no_name - right, thanks. To make sure I've got this clear: the only way would be to open a connection using C# libs, issue the "start transaction" statement over that connection and later issue a "rollback" or "commit" before terminating the connection? – Adam Benson Feb 02 '17 at 11:19
  • Essentially yes. The only "transaction control" you can have in a function are savepoints if I'm not mistaken –  Feb 02 '17 at 11:49

1 Answers1

0

Answering your question - pointing out where this is statement is wrong: START TRANSACTION; and not the BEGIN statement in EXCEPTION block...

I'm not sure what you try to do. Here is example of rolled back transaction (table t created, but not committed => not exists), yet exception (/0) caught and processed (raised info):

t=# begin;
BEGIN
Time: 0.124 ms
t=#
t=# DO $$
t$# BEGIN
t$#   begin
t$#     create table t (i int);
t$#   end;
t$#     perform 1/0;
t$# EXCEPTION WHEN division_by_zero THEN
t$#   BEGIN
t$#     raise info '%','exc catched';
t$#   END;
t$#
t$# END;
t$# $$
t-# ;
INFO:  exc catched
DO
Time: 10.288 ms
t=#
t=# rollback;
ROLLBACK
Time: 0.152 ms
t=#
t=# \dt+ t
No matching relations found.
t=# ;
Time: 0.062 ms
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132