How can I tell PostgreSQL not to abort the whole transaction when a single constraint has failed?

Question

Postgres automatically aborts transactions whenever any SQL statement terminates with an error, which includes any constraint violation. For example:

glyph=# create table foo (bar integer, constraint blug check(bar > 5));
CREATE TABLE
glyph=# begin;
BEGIN
glyph=# insert into foo values (10);
INSERT 0 1
glyph=# insert into foo values (1);
ERROR:  new row for relation "foo" violates check constraint "blug"
STATEMENT:  insert into foo values (1);
ERROR:  new row for relation "foo" violates check constraint "blug"

No message has yet been issued to that effect, but the transaction is rolled back. My personal favorite line of this session is the following:

glyph=# commit;
ROLLBACK

... since "ROLLBACK" seems like an odd success-message for COMMIT. But, indeed, it's been rolled back, and there are no rows in the table:

glyph=# select * from foo;
 bar 
-----
(0 rows)

I know that I can create a ton of SAVEPOINTs and handle errors in SQL that way, but that involves more traffic to the database, more latency (I might have to handle an error from the SAVEPOINT after all), for relatively little benefit. I really just want to handle the error in my application language anyway (Python) with a try/except, so the only behavior I want out of the SQL is for errors not to trigger automatic rollbacks. What can I do?

Answer 1

I'm extremely new to PostgreSQL, but one of the examples in the PostgreSQL documentation for triggers / server-side programming looks like it does exactly what you're looking for.

See: http://www.postgresql.org/docs/9.2/static/trigger-example.html

Snippet from the page: "So the trigger acts as a not-null constraint but doesn't abort the transaction."

Answer 2

I know this is a very old ticket but (as of 2017) PostgreSQL still have this same behavior of auto-rolling back itself when something goes wrong in the commit. I'd like to share some thoughts here.

I don't know if we can change this behavior, and I don't need this, maybe for the best of delegating PostgreSQL to manage the rollback for us (he knows what he is doing, right ?). Rolling back means changing the data back to its original state before the failed transaction, that means altered or inserted data from triggers will also be discarded. In an ACID logic, this is what we want. Let say you are managing the rollback on the back-end yourself, if something goes wrong during your custom rollback or if the database is changed at the same time from external transactions during your rollback, the data becomes inconsistent and your whole structure most likely to collapse.

So knowing that PostgreSQL will manage its own rollback strategy, the question to ask is "how can I extend the rollback strategy ?". The thing you first should think of is "what caused the transaction to fail ?". In your try/catch structure, try to handle all the possible exceptions and run the transaction again or send feedback to the front-end application with some appropriate "don't do" messages. For me, this is the best way of handling things, it is less code, less overhead, more control, more user-friendly and your database will thank you.

A last point I want to shed light on, SQL standard is having a sqlstate code that can be use to communicate with back-end modules. The failing operation during a transaction will return a sqlstate code, you can then use these codes to make appropriate drawbacks. You can make your own sqlstate codes, as long as it doesn't mess with the reserved ones (https://www.postgresql.org/message-id/20185.1359219138%40sss.pgh.pa.us). For instance in a plpgsql function

...
$$
begin
...do something...if it goes wrong
raise exception 'custom exception message' using errcode='12345';
end
$$
...

This is a example using PDO in PHP (using the error code above) :

...
$pdo->beginTransaction();
try {
  $s = $pdo->prepare('...');
  $s->execute([$value]);

  /**
   * Simulate a null violation exception
   * If it fails, PDO will not wait the commit
   * and will throw the exception, the code below
   * is not executed.
   */
  $s->execute([null]);

  /**
   * If nothing wrong happened, we commit to change
   * the database state.
   */
  $pdo->commit();
}
catch (PDOException $e) {
  /**
   * It is important to also have the commit here.
   * It will trigger PostgreSQL rollback.
   * And make the pdo Object back to auto-commit mode.
   */
  $pdo->commit();

  if ($e->getCode() === '12345') {
    send_feedback_to_client('please do not hack us.');
  }
}
...

Answer 3

I would strongly suggest SqlAlchemy and use subtransactions. You can code like:

#some code 
Session.begin(subtransactions=True)
#more stuff including sql activity then:
with Session.begin(nested=True):
    # get the security
    try:
       foo = MyCodeToMakeFOO(args)
       Session.add(foo)
       Session.flush()
    except:
       log.error("Database hated your foo(%s) but I'll do the rest"%foo)

Most useful when the subtransaction is in a loop where you want to process the good records and log the bad ones.