Блог

Я пытаюсь понять транзакции в plpgsql, и мне хотелось бы получить некоторые объяснения.

У меня есть этот код:

 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;
 

• во-первых, и после получения и опробования всех возможных решений, каков наилучший подход для достижения фиксации/отката на основе параметра процедуры.
• во — вторых, помимо проблемы отката comit, postgres игнорирует if IF r.ID % 2 = 0 THEN все вместе и обновляет все записи, спасибо за любые объяснения.

Я вызываю эту процедуру из другой процедуры, используя CALL MaJ(VAL);

Обновление: Возможно, этот код лучше описывает проблему hte: Вот что я пытаюсь сделать:

 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;
 

Любая помощь в том, чего мне не хватает и как я могу лучше думать о транзакциях, приветствуется.

Возможность использования ROLLBACK или COMMIT только в новых версиях. Проверьте версию, которую вы используете, если поддерживаются эти команды.

Долгое время хранимые процедуры Postgres были без этих команд и без проблем. Использование COMMIT ROLLBACK внутри PL/pgSQL не слишком родное — в основном оно было реализовано для более легкой миграции из Oracle , но поскольку модели транзакций Oracle и Postgres сильно отличаются, существует множество ограничений.

Oracle запускает неявную транзакцию после входа в систему и ожидает явного commit или rollback . После выполнения этих команд Oracle немедленно запускает новую транзакцию.

В Postgres каждый оператор выполняется в рамках транзакции, как в Oracle. но если транзакция была запущена пользователем, то пользователь несет ответственность за commit или rollback . Если пользователь не запускал транзакцию, то Postgres запускает транзакцию, и Postgres неявно запускается rollback , когда операция вызывает исключение, или запускается commit , если нет. После того, как допускается только исключение rollback .

Обычно в Postgres нет необходимости использовать commit или rollback . Просто создайте исключение, и верхние слои выполнят всю необходимую работу.

Ваш код не очень хорош, пожалуйста, попробуйте прочитать документацию и некоторые примечания о стиле программирования. Не используйте верблюжью нотацию — в случае нечувствительного языка.

 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)
 

Протестировано на Postgres 13

Ваша логика «ФИКСАЦИИ» и «ОТКАТА» выглядит для меня нормально после прочтения этого: https://www.postgresql.org/docs/current/plpgsql-transactions.html

Возможно, вам потребуется «НАЧАТЬ» транзакцию, прежде чем вы «ВЫЗОВЕТЕ» свою процедуру: я не уверен.

Ваша 2-я проблема проста: в вашем заявлении об ОБНОВЛЕНИИ нет предложения WHERE. Убедитесь, что вы обновили только текущую запись цикла, сопоставив значение идентификатора.