[Sqlalchemy-tickets] Issue #3805: 1.1.0b3 Core PostgreSQL INSERT CTE rollback bug with engine.execute and default reset_on_return (zzzeek/sqlalchemy)

[Christopher W. <iss...@bi...>]

New issue 3805: 1.1.0b3 Core PostgreSQL INSERT CTE rollback bug with engine.execute and default reset_on_return
https://bitbucket.org/zzzeek/sqlalchemy/issues/3805/110b3-core-postgresql-insert-cte-rollback

Christopher Wilson:

I'm using the new insert CTEs to implement a select or insert in one statement using the engine.execute() approach. The default reset_on_return works fine for separate select() followed by an insert() as the insert transaction is COMMITted. However, using an insert CTE, the transaction is ROLLBACKed whenever the connection is returned to the pool.


MWE: using PostgreSQL 9.3.


```
#!python

import sqlalchemy as sa

metadata = sa.MetaData()

exTable = sa.Table('the_table_name',metadata,
        sa.Column('id', sa.Integer, primary_key = True),
        sa.Column('text', sa.TEXT, unique=True),
        )

theText = "some text here"
returningCols = [exTable.c.id]

engine = sa.create_engine(connStr)
lookup = exTable.select().with_only_columns(returningCols).where(exTable.c.text==theText)
lookupCTE = lookup.cte('selq')
insertCTE = exTable.insert().returning(*returningCols).from_select([exTable.c.text],sa.select([sa.literal(theText)]).where(~sa.exists(sa.select([sa.text('*')]).select_from(lookupCTE)))).cte('insq')

selParams = [sa.text('*')]
lookupSel = sa.select(selParams).select_from(lookupCTE)
lookupIns = sa.select(selParams).select_from(insertCTE)
query = lookupSel.union_all(lookupIns)
idres = engine.execute(query)
theId = None
for item in idres:
  print item
  theId = item['id']

idLookup = engine.execute(lookup)
print ("Should find ID: %d..." % (theId))
for item in idLookup:
  print "item found: %d" % item['id']
```
prints:
(1,)
Should find ID: 1...


Setting pool_reset_on_return = 'commit':
(2,)
Should find ID: 2...
item found: 2

This leads to confusing behavior: 
* if the data is present, the correct ID is returned, 
* if the data is not present, then the data is inserted, the corresponding ID returned, and then that ID is invalid as the transaction is rolled back at the end of the execute() call. 

Yes, I can arbitrarily use the pool_reset_on_return='commit' option for the engine, but the expected behavior would be a COMMIT transaction on the use of INSERT, UPDATE, or DELETE CTEs, since that is the behavior of the separate INSERT command with the default *pool_reset_on_return* behavior.