Raise error without rollback in plpgsql/postgresql

Question

I have two stored functions. delete_item which deletes one item, logging it's success or failure in an actionlog table, it returns a 1 on errors and 0 when running successfully.

Secondly I have another function remove_expired that finds what to delete and loops through it calling delete_item.

All this is intended to be called using a simple bash script (hard requirement from operations, so calling like this is not up for discussion), and it have to give an error code when things doesn't work for their reporting tools.

We want all the deletions possible to succeed (we don't expect errors, but humans are still humans, and errors does happen), so if we want to delete 10 items and 1 fails, we still want the other 9 to be deleted.

Secondly we would really like the logs to be in the table actionlog both in the success and error case. I.e., we want that log to be complete.

Since plpgsql functions doesn't allow manual transaction management that seems to not be an option (unless I missed a way to circumvent this?).

The only way I've found so far to achieve this is to wrap scripts around it outside plpgsql, but we would very much like this to be possible in pure plpgsql so we can just give operations a pssql -C ... command and then they shouldn't be concerned with anything else.

SQL to reproduce the problem:

DROP FUNCTION IF EXISTS remove_expired(timestamp with time zone);
DROP FUNCTION IF EXISTS delete_item(integer);

DROP TABLE IF EXISTS actionlog;
DROP TABLE IF EXISTS evil;
DROP TABLE IF EXISTS test;

CREATE TABLE test (
    id         serial primary key       not null,
    t          timestamp with time zone not null
);

CREATE TABLE evil (
    test_id integer not null references test(id)
);

CREATE TABLE actionlog (
    eventTime timestamp with time zone not null default now(),
    message   text                     not null
);


INSERT INTO test (actualTime, t)
VALUES ('2020-04-01T10:00:00+0200'),
       ('2020-04-01T10:15:00+0200'), -- Will not be deleable due to foreign key
       ('2020-04-01T10:30:00+0200')
;

INSERT INTO evil (test_id) SELECT id FROM test WHERE id = 2;


CREATE OR REPLACE FUNCTION remove_expired(timestamp with time zone)
    RETURNS void
AS
$$
DECLARE
    test_id int;
    failure_count int = 0;
BEGIN
    FOR test_id IN
        SELECT id FROM test WHERE t < $1
    LOOP
        failure_count := delete_item(test_id) + failure_count;
    END LOOP;

    IF failure_count > 0 THEN
        -- I want this to cause 'psql ... -c "SELECT * FROM remove_expred...' to exit with exit code != 0
        RAISE 'There was one or more errors deleting. See the log for details';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION delete_item(integer)
    RETURNS integer
AS
$$
BEGIN
    DELETE FROM test WHERE id = $1;
    INSERT INTO actionlog (message)
        VALUES ('Deleted with ID: ' || $1);
    RETURN 0;
EXCEPTION WHEN OTHERS THEN
    INSERT INTO actionlog (message)
        VALUES ('Error deleting ID: ' || $1 || '. The error was: ' || SQLERRM);
    RETURN 1;
END
$$ LANGUAGE plpgsql;

Thanks in advance for any useful input

Answer 1

You can have something close to what your expect in PostgreSQL 11 or PostgreSQL 12 but only with procedures because as already said functions will always roll everything back in case of errors.

With:

DROP PROCEDURE IF EXISTS remove_expired(timestamp with time zone);
DROP PROCEDURE IF EXISTS delete_item(integer);
DROP FUNCTION  IF EXISTS f_removed_expired;
DROP SEQUENCE  IF EXISTS failure_count_seq;

DROP TABLE IF EXISTS actionlog;
DROP TABLE IF EXISTS evil;
DROP TABLE IF EXISTS test;

CREATE TABLE test (
    id         serial primary key       not null,
    t          timestamp with time zone not null
);

CREATE TABLE evil (
    test_id integer not null references test(id)
);

CREATE TABLE actionlog (
    eventTime timestamp with time zone not null default now(),
    message   text                     not null
);


INSERT INTO test (t)
VALUES ('2020-04-01T10:00:00+0200'),
       ('2020-04-01T10:15:00+0200'), -- Will not be removed due to foreign key
       ('2020-04-01T10:30:00+0200')
;

select * from test where t < current_timestamp;

INSERT INTO evil (test_id) SELECT id FROM test WHERE id = 2;

CREATE SEQUENCE failure_count_seq MINVALUE 0;
SELECT SETVAL('failure_count_seq', 0, true);

CREATE OR REPLACE PROCEDURE remove_expired(timestamp with time zone)
AS
$$
DECLARE
    test_id int;
    failure_count int = 0;
    return_code int;
BEGIN
    FOR test_id IN
        SELECT id FROM test WHERE t < $1
    LOOP
        call delete_item(test_id);
        COMMIT;
    END LOOP;

    SELECT currval('failure_count_seq') INTO failure_count; 
    IF failure_count > 0 THEN
        -- I want this to cause 'psql ... -c "SELECT * FROM remove_expred...' to exit with exit code != 0
        RAISE 'There was one or more errors deleting. See the log for details';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE delete_item(in integer)
AS
$$
DECLARE 
 forget_value int;
BEGIN
    DELETE FROM test WHERE id = $1;
    INSERT INTO actionlog (message)
        VALUES ('Deleted with ID: ' || $1);
EXCEPTION WHEN OTHERS THEN
    INSERT INTO actionlog (message)
        VALUES ('Error deleting ID: ' || $1 || '. The error was: ' || SQLERRM);
    COMMIT;
    SELECT NEXTVAL('failure_count_seq') INTO forget_value;
END
$$ LANGUAGE plpgsql;
--

I get:

select * from test;
 id |           t            
----+------------------------
  1 | 2020-04-01 10:00:00+02
  2 | 2020-04-01 10:15:00+02
  3 | 2020-04-01 10:30:00+02
(3 rows)

select current_timestamp;
       current_timestamp       
-------------------------------
 2020-04-01 16:52:26.171975+02
(1 row)

call remove_expired(current_timestamp);
psql:test.sql:80: ERROR:  There was one or more errors deleting. See the log for details
CONTEXT:  PL/pgSQL function remove_expired(timestamp with time zone) line 17 at RAISE
select currval('failure_count_seq');
 currval 
---------
       1
(1 row)

select * from test;
 id |           t            
----+------------------------
  2 | 2020-04-01 10:15:00+02
(1 row)

select * from actionlog;
           eventtime           |                                                                  message                                                                  
-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------
 2020-04-01 16:52:26.172173+02 | Deleted with ID: 1
 2020-04-01 16:52:26.179794+02 | Error deleting ID: 2. The error was: update or delete on table "test" violates foreign key constraint "evil_test_id_fkey" on table "evil"
 2020-04-01 16:52:26.196503+02 | Deleted with ID: 3
(3 rows)

I use a sequence to record the number of failures: you can use this sequence to test for failures and return the right return code.