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

```sql
-- 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:

```sql
-- 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`:

```sql
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