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 backUsing 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 | ThirdUsing 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=2Summary
- 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
EXCEPTIONblock to create savepoints
Article copyright statement
