repozitory integrity error

[Paul Everitt]

Might be a dupe of lp:1035309

IntegrityError: (IntegrityError) duplicate key value violates unique constraint "archived_container_pkey"
DETAIL: Key (container_id)=(1652488497) already exists.
 'INSERT INTO archived_container (container_id, path) VALUES (%(container_id)s, %(path)s)' {'path': u'/communities/justice-initiative-for-all/files/publications/books-monographs/2013/viewed-with-suspicion-the-cost-of-stop-and-search-in-england-and-wales-photo-book', 'container_id': 1652488497}
Exception when processing https://karl.soros.org/communities/justice-initiative-for-all/files/publications/books-monographs/2013/viewed-with-suspicion-the-cost-of-stop-and-search-in-england-and-wales-photo-book/add_file.html
Referer: https://karl.soros.org/communities/justice-initiative-for-all/files/publications/books-monographs/2013/viewed-with-suspicion-the-cost-of-stop-and-search-in-england-and-wales-photo-book/add_file.html

Traceback (most recent call last):
  File "/srv/osfkarl/production/36/eggs/pyramid-1.2.1-py2.6.egg/pyramid/tweens.py", line 17, in excview_tween
    response = handler(request)
  File "/srv/osfkarl/production/36/eggs/pyramid_tm-0.5-py2.6.egg/pyramid_tm/__init__.py", line 107, in tm_tween
    return response
  File "/srv/osfkarl/production/36/eggs/pyramid_tm-0.5-py2.6.egg/pyramid_tm/__init__.py", line 73, in __exit__
    return self._retry_or_raise(*sys.exc_info())
  File "/srv/osfkarl/production/36/eggs/pyramid_tm-0.5-py2.6.egg/pyramid_tm/__init__.py", line 60, in _retry_or_raise
    reraise(t, v, tb) # otherwise reraise the exception
  File "/srv/osfkarl/production/36/eggs/pyramid_tm-0.5-py2.6.egg/pyramid_tm/__init__.py", line 69, in __exit__
    self.manager.commit()
  File "/srv/osfkarl/production/36/eggs/transaction-1.1.1-py2.6.egg/transaction/_manager.py", line 89, in commit
    return self.get().commit()
  File "/srv/osfkarl/production/36/eggs/transaction-1.1.1-py2.6.egg/transaction/_transaction.py", line 329, in commit
    self._commitResources()
  File "/srv/osfkarl/production/36/eggs/transaction-1.1.1-py2.6.egg/transaction/_transaction.py", line 441, in _commitResources
    rm.tpc_begin(self)
  File "/srv/osfkarl/production/36/eggs/zope.sqlalchemy-0.6.1-py2.6.egg/zope/sqlalchemy/datamanager.py", line 87, in tpc_begin
    self.session.flush()
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/orm/session.py", line 1473, in flush
    self._flush(objects)
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/orm/session.py", line 1542, in _flush
    flush_context.execute()
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/orm/unitofwork.py", line 327, in execute
    rec.execute(self)
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/orm/unitofwork.py", line 471, in execute
    uow
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/orm/mapper.py", line 2136, in _save_obj
    execute(statement, multiparams)
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/engine/base.py", line 1358, in execute
    params)
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/engine/base.py", line 1491, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/engine/base.py", line 1599, in _execute_context
    context)
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/engine/base.py", line 1592, in _execute_context
    context)
  File "/srv/osfkarl/production/36/eggs/SQLAlchemy-0.7.1-py2.6-linux-i686.egg/sqlalchemy/engine/default.py", line 325, in do_execute
    cursor.execute(statement, parameters)
IntegrityError: (IntegrityError) duplicate key value violates unique constraint "archived_container_pkey"
DETAIL: Key (container_id)=(1652488497) already exists.
 'INSERT INTO archived_container (container_id, path) VALUES (%(container_id)s, %(path)s)' {'path': u'/communities/justice-initiative-for-all/files/publications/books-monographs/2013/viewed-with-suspicion-the-cost-of-stop-and-search-in-england-and-wales-photo-book', 'container_id': 1652488497}

[Tres Seaver (tseaver)]

> Might be a dupe of lp:1035309

The table whose constraint is being violated is 'archive_container', is managed by the 'repozitory' package, not by 'repoze.pgtextindex'. I updated the title accordingly.

[Tres Seaver (tseaver)]

This error is basically a conflict error: two separate requests are
both adding files to a new folder, and the repozitory code is trying to
insert a record into the 'archive_container' table for it from each side.

I can't see an obvious no-risk way to fix the issue. It might be possible
to try to flush the SQLAlchemy session sooner (instead of during the
'transaction.commit()' issued by 'pyramid_tm', and re-load the object
created by the thread which one the race.

[Tres Seaver (tseaver)]

Hmm, there is a server-side example in the PostgreSQL docs:

  http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

I'm not sure if we want to go there.

[Paul Everitt (paul-agendaless)]

How about hiding the exception as an INFO log?

--Paul

On Feb 27, 2014, at 4:03 PM, Tres Seaver <email address hidden> wrote:

> This error is basically a conflict error: two separate requests are
> both adding files to a new folder, and the repozitory code is trying to
> insert a record into the 'archive_container' table for it from each side.
>
> I can't see an obvious no-risk way to fix the issue. It might be possible
> to try to flush the SQLAlchemy session sooner (instead of during the
> 'transaction.commit()' issued by 'pyramid_tm', and re-load the object
> created by the thread which one the race.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1285823
>
> Title:
> repozitory integrity error
>
> Status in KARL3:
> In Progress
>
> Bug description:
> Might be a dupe of lp:1035309
>
> IntegrityError: (IntegrityError) duplicate key value violates unique constraint "archived_container_pkey"
> DETAIL: Key (container_id)=(1652488497) already exists.
> 'INSERT INTO archived_container (container_id, path) VALUES (%(container_id)s, %(path)s)' {'path': u'/communities/justice-initiative-for-all/files/publications/books-monographs/2013/viewed-with-suspicion-the-cost-of-stop-and-search-in-england-and-wales-photo-book', 'container_id': 1652488497}
> Exception when processing https://karl.soros.org/communities/justice-initiative-for-all/files/publications/books-monographs/2013/viewed-with-suspicion-the-cost-of-stop-and-search-in-england-and-wales-photo-book/add_file.html
> Referer: https://karl.soros.org/communities/justice-initiative-for-all/files/publications/books-monographs/2013/viewed-with-suspicion-the-cost-of-stop-and-search-in-england-and-wales-photo-book/add_file.html
>
> Traceback (most recent call last):
> File "/srv/osfkarl/production/36/eggs/pyramid-1.2.1-py2.6.egg/pyramid/tweens.py", line 17, in excview_tween
> response = handler(request)
> File "/srv/osfkarl/production/36/eggs/pyramid_tm-0.5-py2.6.egg/pyramid_tm/__init__.py", line 107, in tm_tween
> return response
> File "/srv/osfkarl/production/36/eggs/pyramid_tm-0.5-py2.6.egg/pyramid_tm/__init__.py", line 73, in __exit__
> return self._retry_or_raise(*sys.exc_info())
> File "/srv/osfkarl/production/36/eggs/pyramid_tm-0.5-py2.6.egg/pyramid_tm/__init__.py", line 60, in _retry_or_raise
> reraise(t, v, tb) # otherwise reraise the exception
> File "/srv/osfkarl/production/36/eggs/pyramid_tm-0.5-py2.6.egg/pyramid_tm/__init__.py", line 69, in __exit__
> self.manager.commit()
> File "/srv/osfkarl/production/36/eggs/transaction-1.1.1-py2.6.egg/transaction/_manager.py", line 89, in commit
> return self.get().commit()
> File "/srv/osfkarl/production/36/eggs/transaction-1.1.1-py2.6.egg/transaction/_transaction.py", line 329, in commit
> self._commitResources()
> File "/srv/osfkarl/production/36/eggs/transaction-1.1.1-py2.6.egg/transaction/_transaction.py", line 441, in _commitResources
> rm.tpc_begin(self)
> File "/srv/osfkarl/production/36/eggs/zope.sqlalchemy-0.6.1-py2.6.egg/zope/sqlalchemy/datamanager.py", line 87, in tpc_begi...

[Tres Seaver (tseaver)]

Maybe 'session.merge()'?

  http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html?highlight=merge#unitofwork-merging

I'm not sure how it would deal with the conflict, but at least we move the problem around. :)

[Paul Everitt (paul-agendaless)]

Moving to April.

[Paul Everitt (paul-agendaless)]

I think we are just going to live with this one.