Friday, January 9, 2009

PostgreSQL transactions and error handling

I'm taking our web app that runs on MySQL and seeing what it would take to get it running on PostgreSQL. I just discovered one rather glaring difference between PostgreSQL and most other DBMSs.

Here is an example to demonstrate:

I have a table: my_table, with the following row:


----------------
| id  |  val   |
----------------
| 3   | row 3  |
----------------


Let's say I want to make sure I have rows for ids 1 through 5 in the table. This is a case where I would use MySQL's 'INSERT IGNORE' statement, which doesn't exist in PostgreSQL. I have at least two options: I can create a stored procedure to do it, or I can do it in code.

Let's say I create a procedure:


CREATE OR REPLACE FUNCTION my_proc() RETURNS void AS
$$
BEGIN
  FOR i IN 1..5 LOOP
  BEGIN
    INSERT INTO my_table(id, val) VALUES (i, 'row ' || i);
  EXCEPTION WHEN unique_violation THEN
  -- do nothing
  END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;


All is well, this type of exception handling is standard in stored procedures. Here's the entire test:


postgres=# create database my_test;
CREATE DATABASE
postgres=# \c my_test;
You are now connected to database "my_test".
my_test=# create table my_table (id INTEGER PRIMARY KEY, val VARCHAR(64));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "my_table_pkey" for table "my_table"
CREATE TABLE
my_test=# CREATE OR REPLACE FUNCTION my_proc() RETURNS void AS
my_test-# $$
my_test$# BEGIN
my_test$#   FOR i IN 1..5 LOOP
my_test$#   BEGIN
my_test$#     INSERT INTO my_table(id, val) VALUES (i, 'row ' || i);
my_test$#   EXCEPTION WHEN unique_violation THEN
my_test$#   -- do nothing
my_test$#   END;
my_test$#   END LOOP;
my_test$# END;
my_test$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
my_test=# insert into my_table values (3,'row 3');
INSERT 0 1
my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
(1 row)

my_test=# select my_proc();
 my_proc 
---------
 
(1 row)

my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
  1 | row 1
  2 | row 2
  4 | row 4
  5 | row 5
(5 rows)

my_test=# 


Now here's the twist - you can't (really) do the same thing outside of a stored procedure (without using savepoints, as I'll get to later). Here's what happens:


my_test=# \set AUTOCOMMIT OFF
my_test=# delete from my_table where id <> 3;
DELETE 4
my_test=# commit;
COMMIT
my_test=# insert into my_table values (1, 'row 1');
INSERT 0 1
my_test=# insert into my_table values (2, 'row 2');
INSERT 0 1
my_test=# insert into my_table values (3, 'row 3');
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
my_test=# insert into my_table values (4, 'row 4');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
my_test=# insert into my_table values (5, 'row 5');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
my_test=# commit;
ROLLBACK
my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
(1 row)


PostgreSQL forces you to rollback a transaction that hits any error. There is no exception handling! Granted, you can get around this by using savepoints:


my_test=# insert into my_table values (1, 'row 1');
INSERT 0 1
my_test=# insert into my_table values (2, 'row 2');
INSERT 0 1
my_test=# SAVEPOINT my_hack;
SAVEPOINT
my_test=# insert into my_table values (3, 'row 3');
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
my_test=# ROLLBACK TO SAVEPOINT my_hack;
ROLLBACK
my_test=# insert into my_table values (4, 'row 4');
INSERT 0 1
my_test=# insert into my_table values (5, 'row 5');
INSERT 0 1
my_test=# commit;
COMMIT
my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
  1 | row 1
  2 | row 2
  4 | row 4
  5 | row 5
(5 rows)

my_test=# 


But I see this as more of a hack than a real solution.

I can see arguments for both sides of this issue. I really like this conversation about the issue: http://www.nabble.com/25P02,-current-transaction-is-aborted,-commands-ignored-until-end-of-transaction-block-td3710080.html . But what bugs me is that stored procedures can do exception handling, but no one else can. Especially in my case, where I'm using Java and JDBC, which throws exceptions for any errors that come back. I would rather do my own exception handling, or at least have the option of doing my own.

So here's my vote for changing PostgreSQL to behave more like MySQL in this case.

1 comment:

  1. sqlite has a neato construct to peform actions on contraint violations:

    CREATE TABLE foo (i int contraint x unique on conflict ignore);

    sqlite> insert into foo values(1);
    sqlite> insert into foo values(1);
    sqlite> insert into foo values(1);
    sqlite> insert into foo values(1);
    sqlite> select * from foo;
    1

    http://www.sqlite.org/lang_createtable.html

    ReplyDelete