SQLite temporary table name collision with forkIO for "returning" emulation

[moll]

Hey,

I'm not even sure how well running Beam on SQLite from different lightweight threads (forkIO) should work, but given the temporary table creation mechanism for getting the inserted row (https://github.com/tathougies/beam/blob/d87120b58373df53f075d92ce12037a98ca709ab/beam-sqlite/Database/Beam/Sqlite/Connection.hs#L379) reuses the same id every round, it definitely won't work and ends up throwing a number of table inserted_values_14173 already exists errors.

[moll]

Looking at it again, I suspect the approach of sending separate statements to SQLite has no chance of working in concurrent contexts regardless if the id is unique or not. I imagine it could work if all the trigger creation, insert statements and teardown statements could be sent to SQLite in a single call, it'd work due to its serialization, but separate execute calls on a single connection are bound to be interleaved, unless I'm unaware of some synchronization going on somewhere else in Beam.

I had a similar bug in a JavaScript app just the other day where I was also depending on last_insert_rowid to get the inserted row, but did so leaving a gap for another insert to be made before my SELECT reached SQLite. ^_^

[kmicklas]

We should support concurrent use of an SQLite database as long as connections are not shared.