Handling SQL constraint exceptions in Odoo ORM

Question

I've got a UNIQUE constraint defined in my model:

class FooBar(models.Model):
    _name = 'my.foobar'

    # ...

    _sql_constraints = [
        ('foo_bar_uniq', 'unique("foo", "bar")', 'You could not step twice into the same foobar!')
    ]

And a controller with code for creating new objects:

class FooBarController(http.Controller):
    @http.route('/foobar/create/', auth='public', website=True)
    def create(self, foo, bar):
        http.request.env['my.foobar'].create({
            'foo': foo,
            'bar': bar,
        })
        return http.request.render('my.thank_you_page')

If the UNIQUE constraint is violated I get an IntegrityError exception. I would like to catch it and display a different message to the user:

from psycopg2 import IntegrityError

class FooBarController(http.Controller):
    @http.route('/foobar/create/', auth='public', website=True)
    def create(self, foo, bar):
        try:
            http.request.env['my.foobar'].create({
                'foo': foo,
                'bar': bar,
            })
            return http.request.render('my.thank_you_page')
        except IntegrityError:
            return http.request.render('my.error_page')

This works... kinda. The IntegrityError is successfully caught, but all subsequent database operations (which, as far as I know, are trigger automatically by the website module) end in InternalError:

InternalError: current transaction is aborted, commands ignored until end of transaction block.

As a consequence, all that the end user sees is the Internal Server Error page.

How can I handle UNIQUE constraint violations correctly?

Answer 1

First thing to do in your catch code, is to commit or close your database cursor in order to release the lock.

Answer 2

Here is an example expanding on Emanuel's answer:

class FooBarController(http.Controller):
    @http.route('/foobar/create/', auth='public', website=True)
    def create(self, foo, bar):
        try:
            http.request.env['my.foobar'].create({
                'foo': foo,
                'bar': bar,
            })
            return http.request.render('my.thank_you_page')
        except IntegrityError:
            # can't use the usual `http.request.env.cr` style,
            # because `env` queries db and everything explodes
            http.request._cr.rollback()
            return http.request.render('my.error_page')