Commit, savepoint, rollback to in PostgreSQL?

Question

Can someone please explain to me why does COMMIT in this function returns EXCEPTION ?

DECLARE
  XNar CURSOR (forDATE Varchar) IS 
   SELECT NARUCENO, ISPORUKA_ID FROM XDATA_NARUDZBE 
   WHERE TO_CHAR(XDATA_NARUDZBE.DATUM, 'DD.MM.YYYY') = forDATE;
 LastDate  DATE;
 OutResult INTEGER;
 curNAR    NUMERIC;
 curISP    VARCHAR;
 RXNar     RECORD; 
BEGIN

 OutResult := 1;

 SELECT MAX(DATUM) INTO LastDate FROM XDATA_NARUDZBE;  

 FOR RXNar IN XNar(TO_CHAR(LastDate, 'DD.MM.YYYY')) LOOP

   IF (RXNar.NARUCENO <> 0) AND (RXNar.ISPORUKA_ID = 'R01') THEN
     UPDATE NARUDZBE SET ISPORUCENO = RXNar.NARUCENO 
      WHERE NARUDZBE.PP_ID  = RXNar.PP_ID
        AND NARUDZBE.ART_ID = RXNar.ART_ID
        AND NARUDZBE.ISPORUKA_ID = 'R01';
   END IF;

  END LOOP;

 COMMIT; <--- ????

 RETURN OutResult;

 EXCEPTION
  WHEN OTHERS THEN
   OUTRESULT := 0;
   RAISE;    
   RETURN OutResult;

END;

and why I can not use ROLLBACK TO SavePoint when EXCEPTION block exists in function?

score 13 · Answer 1 · answered Mar 27 '11 at 11:59

13

You can't use COMMIT in a stored procedure, the entire procedure is a transaction of it's own.

answered Mar 27 '11 at 11:59

Frank Heikens

117,544
24
142
135

6

link: last paragraph of [this page](http://www.postgresql.org/docs/current/static/plpgsql-structure.html#PLPGSQL-STRUCTURE) – Mar 27 '11 at 12:09
@JackPDouglas: That's what the OP needs, nested blocks. – Frank Heikens Mar 27 '11 at 12:13
1

in other words he needs to `commit` outside the procedure? My understanding is that he can roll back sub-transactions inside the procedure, but not `commit` at all (or `rollback` the whole transaction explicitly) – Mar 27 '11 at 12:34
I am new to PG... thx for clarifaing that for me! What about next question ? – EmirZ Mar 27 '11 at 13:27

score 9 · Accepted Answer · edited Mar 27 '11 at 17:04

You can't commit in a plpgsql stored function/procedure using plpgsql as Frank Heikens answered. You can however work around this issue by using dblink(http://www.postgresql.org/docs/9.0/interactive/contrib-dblink-connect.html) or another store procedure language such as plperl(untrusted). Check out this link where this talked about.

http://postgresql.1045698.n5.nabble.com/Re-GENERAL-Transactions-within-a-function-body-td1992810.html

The high level is you open a new connection using one of these methods and issue a separate transaction on that connection. Works for most cases not ideal because you are opening a new connection, but may work fine for most use cases.

score 0 · Answer 3 · answered Dec 10 '21 at 05:15

Begin with PostgreSQL11 there is an procedure module. Demo based on Manual.

CREATE OR REPLACE PROCEDURE insert_data_drop(_a integer, _b integer)
LANGUAGE plpgsql
AS $$
begin
   INSERT INTO tbl(a) VALUES (_a);
   INSERT INTO tbl(b) VALUES (_b);
   Rollback;
   INSERT INTO tbl(a) VALUES (_a);
end
$$;

Now CALL insert_data_drop(1, 2); will insert 1 to column a, 2 will not be saved. However, SAVEPOINT seems not working. It will show error like:

ERROR:  unsupported transaction command in PL/pgSQL
CONTEXT:  PL/pgSQL function insert_data_drop(integer,integer) line 5 at SQL stateme

Commit, savepoint, rollback to in PostgreSQL?

3 Answers3

Linked