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?

score 5 · Answer 1 · answered Nov 28 '14 at 15:00

5

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

answered Nov 28 '14 at 15:00

Emanuel

155
2
11

Thank you for the response. [This FAQ](http://initd.org/psycopg/docs/faq.html#problems-with-transactions-handling) in `psycopg` documentation suggests that rollback might be better? – Ludwik Trammer Nov 28 '14 at 15:28

score 5 · Answer 2 · answered Nov 28 '14 at 17:19

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')

score 3 · Accepted Answer · answered Feb 08 '18 at 20:07

You can use the cr.savepoint() context manager:

class FooBarController(http.Controller):

    @http.route('/foobar/create/', auth='public', website=True)
    def create(self, foo, bar):
        try:
            with http.request.env.cr.savepoint():
                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')

Any database calls inside the context manager will be run inside a PostgreSQL savepoint. If an exception occurs, the savepoint (not the transaction) will be rollbacked, so you will be able to make subsequent calls inside the current database transaction.

Additionally, if you do not want the IntegrityErrors to be logged, you may temporarily mute the openerp.sql_db logger (or odoo.sql_db, if you are using Odoo 10 or above) using the mute_logger context manager/decorator:

from openerp.tools import mute_logger

# ...

try:
    with mute_logger('openerp.sql_db'), http.request.env.cr.savepoint():
        # ...
except IntegrityError:
    # ...

That's a great answer, thank you! It came a couple of years too late for me to use ;) but I'll gladly accept it. — Ludwik Trammer, Feb 08 '18 at 21:01

Handling SQL constraint exceptions in Odoo ORM

3 Answers3