Issue with postgres insert on_conflict when using a uniqueConstraint

[nicola...@cubber.com]

Hello everyone,

I encountered a surprising behavior of the postres specific insert when using UniqueConstraint.

A test code :

from sqlalchemy import Column, Integer, String, UniqueConstraint, create_engine
from sqlalchemy.dialects.postgresql import insert as pg_insert
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class TestClass(Base):
    __tablename__ = 'test'
    id_ = Column('id_', Integer, primary_key=True)
    a = Column('a', Integer)
    b = Column('b', String)
    __table_args__ = (UniqueConstraint('a', 'b', name='const'),)

engine = create_engine('mysql+mysqldb://db_user:db_pswd@db_host/db_name')
TestClass.metadata.create_all(engine)
query = pg_insert(TestClass).values(a=1, b='b').on_conflict_do_nothing(constraint='const')
engine.execute(query)
engine.execute(query)

The exception returned :

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in connection(self)
    345         try:
--> 346             return self.__connection
    347         except AttributeError:

AttributeError: 'Connection' object has no attribute '_Connection__connection'

During handling of the above exception, another exception occurred:

ResourceClosedError                       Traceback (most recent call last)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _commit_impl(self, autocommit)
    722         except BaseException as e:
--> 723             self._handle_dbapi_exception(e, None, None, None, None)
    724         finally:

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1396             else:
-> 1397                 util.reraise(*exc_info)
   1398

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    186             raise value.with_traceback(tb)
--> 187         raise value
    188

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _commit_impl(self, autocommit)
    720         try:
--> 721             self.engine.dialect.do_commit(self.connection)
    722         except BaseException as e:

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in connection(self)
    350             except BaseException as e:
--> 351                 self._handle_dbapi_exception(e, None, None, None, None)
    352

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1396             else:
-> 1397                 util.reraise(*exc_info)
   1398

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    186             raise value.with_traceback(tb)
--> 187         raise value
    188

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in connection(self)
    348             try:
--> 349                 return self._revalidate_connection()
    350             except BaseException as e:

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _revalidate_connection(self)
    428             return self.__connection
--> 429         raise exc.ResourceClosedError("This Connection is closed")
    430

ResourceClosedError: This Connection is closed

During handling of the above exception, another exception occurred:

AttributeError                            Traceback (most recent call last)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in connection(self)
    345         try:
--> 346             return self.__connection
    347         except AttributeError:

AttributeError: 'Connection' object has no attribute '_Connection__connection'

During handling of the above exception, another exception occurred:

ResourceClosedError                       Traceback (most recent call last)
<ipython-input-2-02fbc1f7d6d4> in <module>()
     14 TestClass.metadata.create_all(engine)
     15 query = pg_insert(TestClass).values(a=1, b='b').on_conflict_do_nothing(constraint='const1')
---> 16 engine.execute(query)
     17 engine.execute(query)

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   2054
   2055         connection = self.contextual_connect(close_with_result=True)
-> 2056         return connection.execute(statement, *multiparams, **params)
   2057
   2058     def scalar(self, statement, *multiparams, **params):

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    943             raise exc.ObjectNotExecutableError(object)
    944         else:
--> 945             return meth(self, multiparams, params)
    946
    947     def _execute_function(self, func, multiparams, params):

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    261     def _execute_on_connection(self, connection, multiparams, params):
    262         if self.supports_execution:
--> 263             return connection._execute_clauseelement(self, multiparams, params)
    264         else:
    265             raise exc.ObjectNotExecutableError(self)

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1051             compiled_sql,
   1052             distilled_params,
-> 1053             compiled_sql, distilled_params
   1054         )
   1055         if self._has_events or self.engine._has_events:

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1207
   1208         if context.should_autocommit and self._root.__transaction is None:
-> 1209             self._root._commit_impl(autocommit=True)
   1210
   1211         if result._soft_closed and self.should_close_with_result:

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _commit_impl(self, autocommit)
    724         finally:
    725             if not self.__invalid and \
--> 726                     self.connection._reset_agent is self.__transaction:
    727                 self.connection._reset_agent = None
    728             self.__transaction = None

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in connection(self)
    349                 return self._revalidate_connection()
    350             except BaseException as e:
--> 351                 self._handle_dbapi_exception(e, None, None, None, None)
    352
    353     def get_isolation_level(self):

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1395                 )
   1396             else:
-> 1397                 util.reraise(*exc_info)
   1398
   1399         finally:

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    185         if value.__traceback__ is not tb:
    186             raise value.with_traceback(tb)
--> 187         raise value
    188
    189 else:

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in connection(self)
    347         except AttributeError:
    348             try:
--> 349                 return self._revalidate_connection()
    350             except BaseException as e:
    351                 self._handle_dbapi_exception(e, None, None, None, None)

/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _revalidate_connection(self)
    427             self.__invalid = False
    428             return self.__connection
--> 429         raise exc.ResourceClosedError("This Connection is closed")
    430
    431     @property

ResourceClosedError: This Connection is close

I guess I did something wrong, but the error message seems completely unrelated to the operation (A RessourceClosed error ?).
It is to note that it is the second execution of the query (or the first execution on a table with the line from query already present) that raise this exception.

The exception is not raised when we put a value to id_ (for example values(a=1, b='b', id=1) ).

It was done with sqlalchemy 1.1.8 and postgres 9.6.2.

[mike bayer]

noting that your example is erroneously referring to "mysql", the trace
you describe is against Postgresql, issue
https://bitbucket.org/zzzeek/sqlalchemy/issues/3955/connectionless-execution-autocommit-on
is added and fix targeted at 1.1.9 is at
https://gerrit.sqlalchemy.org/#/q/I235a25daf4381b31f523331f810ea04450349722.

For now I would advise to not use connectionless execution for
operations like this.

On 04/03/2017 12:15 PM, nicola...@cubber.com wrote:
> Hello everyone,
>
> I encountered a surprising behavior of the postres specific insert when
> using UniqueConstraint.
>
> A test code :
>
> |
> fromsqlalchemy importColumn,Integer,String,UniqueConstraint,create_engine
> fromsqlalchemy.dialects.postgresql importinsert aspg_insert
> fromsqlalchemy.ext.declarative importdeclarative_base
> Base=declarative_base()
>
> classTestClass(Base):
> __tablename__ ='test'
> id_ =Column('id_',Integer,primary_key=True)
> a =Column('a',Integer)
> b =Column('b',String)
> __table_args__ =(UniqueConstraint('a','b',name='const'),)
>

> engine =create_engine('mysql+mysqldb://db_user:db_pswd@db_host/db_name')
> TestClass.metadata.create_all(engine)
> query
> =pg_insert(TestClass).values(a=1,b='b').on_conflict_do_nothing(constraint='const')

> inconnection(self)
> 348 try:
> -->349 returnself._revalidate_connection()
> 350 exceptBaseExceptionase:
>
> /home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
> in_revalidate_connection(self)
> 428 returnself.__connection
> -->429 raiseexc.ResourceClosedError("This Connection is closed")

> 430
>
> ResourceClosedError:ThisConnectionisclosed
>
> Duringhandling of the above exception,another exception occurred:
>
> AttributeError Traceback(most recent call last)
> /home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
> inconnection(self)
> 345 try:
> -->346 returnself.__connection
> 347 exceptAttributeError:
>
> AttributeError:'Connection'objecthas noattribute '_Connection__connection'
>
> Duringhandling of the above exception,another exception occurred:
>

> ResourceClosedError Traceback(most recent call last)

> <ipython-input-2-02fbc1f7d6d4>in<module>()
> 14TestClass.metadata.create_all(engine)
> 15query

> =pg_insert(TestClass).values(a=1,b='b').on_conflict_do_nothing(constraint='const1')

> -->429 raiseexc.ResourceClosedError("This Connection is closed")

> 430
> 431 @property
>
> ResourceClosedError:ThisConnectionisclose
> |
>
>
>
> I guess I did something wrong, but the error message seems completely
> unrelated to the operation (A RessourceClosed error ?).
> It is to note that it is the second execution of the query (or the first
> execution on a table with the line from query already present) that
> raise this exception.
>
> The exception is not raised when we put a value to id_ (for example
> values(a=1, b='b', id=1) ).
>
> It was done with sqlalchemy 1.1.8 and postgres 9.6.2.
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.