Postgresql ignoring if condition-CodePudding

I'm trying to understand transactions in plpgsql and i would like some explanations.

I have this code:

CREATE OR REPLACE PROCEDURE MaJ(mode IN INT)
AS
$$
DECLARE
r RECORD;
DECLARE
r RECORD;
BEGIN
    FOR r IN SELECT id, fname, lname, bday FROM usr
        LOOP
            IF r.ID % 2 = 0 THEN
                UPDATE usr SET lname = 'KONAN';
                RAISE NOTICE E'fname : %\n', r.lname;
            END IF;
        END LOOP;
    IF mode = 0 THEN
        COMMIT;
    ELSE IF mode = 1 THEN
        ROLLBACK;
    END IF;
END;

firs of all and after getting and trying all possible solutions, what's the best approach for acheiving the commit/rollback based on the procedure parameter.
second, aside from the comit rollback issue, postgres is ignoring the if IF r.ID % 2 = 0 THEN all together and updating all entries, thank you for any explanation.

I'm calling this procedure from another procedure using CALL MaJ(VAL);

Update: Maybe this code portrays hte problem better: Here's what I'm trying to do:

CREATE OR REPLACE PROCEDURE CRDM(Crtrn INOUT INT)
AS
$CRDM$
DECLARE
R RECORD;
BEGIN
    
        FOR R IN SELECT * FROM usr
            LOOP
                IF R.ID % 2 = 0 THEN
                    UPDATE USR SET lname = 'MAGNI' WHERE USR.ID = R.ID;
                END IF;
            END LOOP;
        IF Crtrn = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
END;
$CRDM$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE AMI() 
AS
$AMI$
DECLARE
rtrn INT:=0;
BEGIN
    BEGIN
        CALL CRDM(rtrn);
    END;
END;
$AMI$ LANGUAGE plpgsql;

DO
$$
BEGIN
    CALL AMI();
END;
$$ LANGUAGE plpgsql;

Any help wih what I'm missing and how I can better think about transactions is welcome.

CodePudding user response：

Your "COMMIT;" and "ROLLBACK;" logic looks OK to me after reading this: https://www.postgresql.org/docs/current/plpgsql-transactions.html

You might need to "BEGIN;" a transaction before you "CALL" your procedure: I'm not sure.

Your 2nd problem is simple: your UPDATE statement has no WHERE clause. Make sure you update only the current loop record by matching on the ID value.

CodePudding user response：

The possibility to use ROLLBACK or COMMIT are only in new versions. Check version that you use if these commands are supported.

Long time the Postgres stored procedures was without these commands and without problems. Using COMMIT, ROLLBACK inside PL/pgSQL is not too much native - mostly it was implemented for more easy migration from Oracle, but because transaction model of Oracle and Postgres is very different, there is lot of limits.

Oracle start implicit transaction after login and waits on explicit commit or rollback. After these commands, Oracle starts new transaction immediately.

In Postgres, every statement is executed under transaction like in Oracle. but if tranaction was started by user, then user is responsible for commit or rollback. If user didn't start transaction, then Postgres starts transaction, and Postgres implicitly runs rollback when operation raises an exception, or runs commit if not. After an exception only rollback is allowed.

Usually in Postgres is not necessary to use commit or rollback. Just raise an exception, and upper layers does all necessary work.

Your code is not nice, please, try to read documentation and some notes about programming style. Don't use Camel notation - in case insensitive language.

CREATE TABLE foo_tab(id int);
CREATE TABLE boo_tab(id int, b text);

INSERT INTO foo_tab VALUES(1);
INSERT INTO foo_tab VALUES(2);

CREATE OR REPLACE PROCEDURE foo(a bool, b text)
AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM foo_tab
  LOOP
    INSERT INTO boo_tab VALUES(r.id, b);
  END LOOP;
  IF a THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE foo2(a bool, b text)
AS $$
BEGIN
  CALL foo(a, b);
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
  CALL foo2(true, 'Ahoj');
  CALL foo2(false, 'Nazdar');
END;
$$; -- LANGUAGE plpgsql is implicit here

postgres=# SELECT * FROM boo_tab;
┌────┬──────┐
│ id │  b   │
╞════╪══════╡
│  1 │ Ahoj │
│  2 │ Ahoj │
└────┴──────┘
(2 rows)

Tested on Postgres 13