Using INSERT RETURNING seems to break sqlite3

[ghallberg]

I'm trying to write an INSERT RETURNING query which works on both Postgres and SQLite:

-- :name test_insert :insert
INSERT INTO table (foo) VALUES (:bar) RETURNING id;

This causes a sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) cannot commit transaction - SQL statements in progress error.

It's possible this is related to https://github.com/sqlalchemy/sqlalchemy/issues/6195

[mcfunley]

Looks like the underlying issue isn't fixed yet (haven't had time to really dig here), but in the meantime you should be able to drop the RETURNING id -- for engines that support lastrowid, insert statements already return the ID of the last row inserted.

https://github.com/mcfunley/pugsql/blob/master/pugsql/statement.py#L94

[an-ivanov]

The same bug has not been fixed yet for the UPDATE RETURNING combination :(