Postgresql ignoring if condition

Question

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.

Best is a matter of opinion. But why not use a boolean for something that is boolean? — jjanes
– jjanes, Commented Sep 26, 2021 at 15:54
@jjanes I'm sorry this language is causing me enough headache could you please elaborate on your point, thank you — Majd
– Majd, Commented Sep 26, 2021 at 16:16

Pavel Stehule · Accepted Answer · 2021-09-27 04:50:37Z

1

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

answered Sep 27, 2021 at 4:50

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Majd Over a year ago

I'm sorry, but it didn' work, It's throwing he error

ERROR:  invalid transaction termination CONTEXT:  PL/pgSQL function foo(boolean,text) line 9 at COMMIT SQL statement "CALL foo(a, b)" PL/pgSQL function foo2(boolean,text) line 3 at CALL SQL statement "CALL foo2(true, 'Ahoj')" PL/pgSQL function inline_code_block line 3 at CALL SQL state: 2D000

I'm trying to understand what that has to do with autocommit on and off ,which I tried both cases and still getting the same error

Majd Over a year ago

about the camel case, I'm sorry this is Oracle names which i haven't changed for now since I'm stuck with this issue prototyping this functionality

Pavel Stehule Over a year ago

@Majd - maybe you (or your client) started transaction already. You can use COMMIT, ROLLBACK only when CALL statement is called outside transaction or subtransaction. Myabe you have disabled autocommit in psql - that means so psql started transaction on background and in this case you cannot to use subtransaction. If you use psql, then you need to enable autocommit in psql.

Majd Over a year ago

It works now, i was executing everything aat once, after executing each procedure before executing the anonymous block it worked, only with Autocommit on which is normal, any advice on how to handle the situation if i was asked to turn off autocommit?

Pavel Stehule Over a year ago

@Majd - disabled autocommit is just psql feature - it is not server side, so this is not problem for usual application. There is not any possibility how it can work with disabled autocommit - this case is just not supported. COMMIT or ROLLBACK can be used only when transaction is started implicitly - this is implementation limit, and you have to accepted if you like it or not.

bluebird · Accepted Answer · 2021-09-26 14:27:48Z

0

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.

edited Sep 26, 2021 at 14:27

answered Sep 26, 2021 at 14:22

bluebird

1411 silver badge5 bronze badges

3 Comments

Majd Over a year ago

the second problem is solved now, thnak you, however the first problem is still there and it's frustrating. thank you

jjanes Over a year ago

@majd What is the first problem? You didn't describe a problem, you just asked for opinions.

Majd Over a year ago

@jjanes the commit rollback is not working and the BEGIN; isn't working either

Collectives™ on Stack Overflow

Postgresql ignoring if condition

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related