I’m always working with customers migrating from Oracle to PostgreSQL. In both DBMS systems, there is I/O impact when using exception handlers such as when handling a PK constraint violation, but the impact in PostgreSQL is different and you should be aware of what is actually going on. For example, when an exception occurs, redo is generated in Oracle (WAL in Postgres) and additional catalog queries are issued in both DBMS systems to get pertinent data about the constraint violation. But what actually happens in Postgres as it relates to MVCC?
Let’s use a simple test to demonstrate. First, create a table with some mocked up “product” data:
/* Create the table: */
CREATE TABLE public.pk_violation_test (
id int PRIMARY KEY,
value numeric,
product_id int,
effective_date timestamp(3)
);
/* Insert some mocked up data */
INSERT INTO public.pk_violation_test VALUES (
generate_series(0,10000),
random()*1000,
random()*100,
current_timestamp(3));
/* Verify that there are no dead tuples: */
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup
FROM
pg_stat_all_tables
WHERE
relname = 'pk_violation_test';
schemaname | relname | n_live_tup | n_dead_tup
------------+-------------------+------------+------------
public | pk_violation_test | 100001 | 0
Now, we will execute a simple insert statement using no directive:
/* Perform a simple insert: */
INSERT INTO pk_violation_test
VALUES (0, 44.33893489873, 46, now());
ERROR: duplicate key value violates unique constraint "pk_violation_test_pkey"
DETAIL: Key (id)=(0) already exists.
Time: 1.292 ms
/* Verify the dead tuple count: */
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup
FROM
pg_stat_all_tables
WHERE
relname = 'pk_violation_test';
schemaname | relname | n_live_tup | n_dead_tup
------------+-------------------+------------+------------
public | pk_violation_test | 100001 | 1
As you can see the error is produced that the insert violated the PK and the tuple which was in violation is now a dead tuple.
Now change the insert to use the “ON CONFLICT” directive and check the dead tuple count:
/* Perform a simple insert using the directive: */
INSERT INTO pk_violation_test
VALUES (0, 44.33893489873, 46, now())
ON CONFLICT
DO NOTHING;
INSERT 0 0
Time: 0.889 ms
/* Verify the unchanged dead tuple count: */
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup
FROM
pg_stat_all_tables
WHERE
relname = 'pk_violation_test';
schemaname | relname | n_live_tup | n_dead_tup
------------+-------------------+------------+------------
public | pk_violation_test | 100001 | 1
As you can see the dead tuple count did not increase, thus reducing the amount of vacuum needed!
Now, most of the code conversion tools might try to do something with that exception block. Maybe it gets converted into something like this:
/* Create a simple function with exception logic: */
CREATE OR REPLACE FUNCTION pk_violation_test_func (p_id int, p_value numeric, p_product_id int)
RETURNS VOID
AS $$
BEGIN
BEGIN
INSERT INTO pk_violation_test (id, value, product_id, effective_date)
VALUES (p_id, p_value, p_product_id, now());
RETURN;
EXCEPTION
WHEN unique_violation THEN
-- try an update
UPDATE
pk_violation_test
SET
value = p_value,
product_id = p_product_id,
effective_date = now()
WHERE
id = p_id;
IF found THEN
RETURN;
END IF;
END;
END;
$$
LANGUAGE plpgsql;
So what happens in this case? Watch how now we get TWO dead tuples. One for the insert and one for the update (if it’s not a HOT Update). I will vacuum the table first so that there is no question around how many dead tuples there are:
/* Vacuum the table: */
vacuum pk_violation_test;
/* Verify the dead tuple count: */
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup
FROM
pg_stat_all_tables
WHERE
relname = 'pk_violation_test';
schemaname | relname | n_live_tup | n_dead_tup
------------+-------------------+------------+------------
public | pk_violation_test | 1000001 | 0
/* Call the sample function */
select * from pk_violation_test_func(0, 44.33893489873, 46);
pk_violation_test_func
------------------------
(1 row)
/* Verify the dead tuple count: */
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup
FROM
pg_stat_all_tables
WHERE
relname = 'pk_violation_test';
schemaname | relname | n_live_tup | n_dead_tup
------------+-------------------+------------+------------
public | pk_violation_test | 1000001 | 2
As you can see the insert attempt using the “ON CONFLICT” directive did not create a dead tuple. In turn this will make your inserts which violate a PK more efficient, faster and not cause unnecessary vacuum. Remember that logging message that was a result of the PK violation not being handled? That log message is gone too. A WIN all around!
Point being that it is very important to understand whatever DBMS you are running it. Things that seem very simple can have pros and cons that need to be dealt with.
Enjoy!
🛩️ Shane Borden's Technology Blog 