PostgreSQL functions and rollback behavior with primary key conflicts

Here’s the explanation about PostgreSQL functions and rollback behavior with primary key conflicts:

Default Behavior Demonstration

-- Create test table
CREATE TABLE test_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Create function: intentionally cause a primary key conflict on the second insert
CREATE OR REPLACE FUNCTION test_insert_with_conflict()
RETURNS VOID AS $$
BEGIN
    -- First insert (should succeed)
    INSERT INTO test_table (id, name) VALUES (1, 'First');

    -- Second insert (attempts to insert existing id=1, will cause conflict)
    INSERT INTO test_table (id, name) VALUES (1, 'Second');

    -- Third insert (will not be reached)
    INSERT INTO test_table (id, name) VALUES (2, 'Third');
END;
$$ LANGUAGE plpgsql;

-- Execute function (will error)
SELECT test_insert_with_conflict();

-- Query result: no data was inserted
SELECT * FROM test_table;
-- Result is empty, all inserts were rolled back

Using Subtransactions (SAVEPOINT) for Partial Rollback

If you want to catch the conflict and allow other inserts to succeed, you can use subtransactions:

-- Create function: isolate conflicting insert using subtransaction
CREATE OR REPLACE FUNCTION test_insert_with_savepoint()
RETURNS VOID AS $$
BEGIN
    -- First insert
    INSERT INTO test_table (id, name) VALUES (1, 'First');

    -- Use subtransaction to handle potential conflict
    BEGIN
        INSERT INTO test_table (id, name) VALUES (1, 'Second');
    EXCEPTION 
        WHEN unique_violation THEN
            -- Only roll back the subtransaction on conflict, main transaction unaffected
            RAISE NOTICE 'Primary key conflict, skipping duplicate insert';
    END;

    -- Third insert (will succeed)
    INSERT INTO test_table (id, name) VALUES (2, 'Third');
END;
$$ LANGUAGE plpgsql;

-- Truncate table and re-execute
TRUNCATE test_table;
SELECT test_insert_with_savepoint();

-- Query result
SELECT * FROM test_table;

Result:

id | name
---|------
1  | First
2  | Third

Using ON CONFLICT for Graceful Handling

The recommended approach is to use ON CONFLICT:

CREATE OR REPLACE FUNCTION test_insert_on_conflict()
RETURNS VOID AS $$
BEGIN
    INSERT INTO test_table (id, name) VALUES (1, 'First')
        ON CONFLICT (id) DO NOTHING;

    INSERT INTO test_table (id, name) VALUES (1, 'Second')
        ON CONFLICT (id) DO NOTHING;

    INSERT INTO test_table (id, name) VALUES (2, 'Third')
        ON CONFLICT (id) DO NOTHING;
END;
$$ LANGUAGE plpgsql;

TRUNCATE test_table;
SELECT test_insert_on_conflict();
SELECT * FROM test_table;  -- Result: one row each for id=1 and id=2

Summary

• Default behavior: Any error (including primary key conflicts) will roll back all operations in the entire function
• Using subtransactions (BEGIN…EXCEPTION): Only the conflicting statement is rolled back, other statements commit normally
• Using ON CONFLICT: The most elegant approach, avoids throwing exceptions
• PostgreSQL functions execute within a single transaction by default, unless you explicitly use an EXCEPTION block to create savepoints