Repository navigation
Single-statement bulk insert for SQL backends #365
Description
I noticed earlier today that insertions of several rows with the Postgres backend are done invididually. I browsed throught he source and noticed that it's indeed called out explicitly in there:
-- | Create multiple records in the database.
--
-- If you don't need the inserted @Key@s, use 'insertMany_'
--
-- SQL backends currently use the slow default implementation of
-- @mapM insert@
insertMany :: (MonadIO m, backend ~ PersistEntityBackend val, PersistEntity val)
=> [val] -> ReaderT backend m [Key val]
insertMany = mapM insert
It sounds like we might want to flip things around and have the SqlBackend function connInsertSql be one that supports multiple rows at once, and have a single-row insert use that function instead, passing a [[PersistValue]] to it internally.
We would have to rewrite insertSql' to build multiple rows in its logic for Postgres, MySQL and SQLite (which does support multirow insertion starting with v. 3.7.1). Basically this function would have to be upgraded:
-- | SQL code to be executed when inserting an entity.
insertSql' :: EntityDef -> [PersistValue] -> InsertSqlResult
insertSql' ent vals =
let sql = pack $ concat
[ "INSERT INTO "
, escapeDBName $ entityDB ent
, "("
, intercalate "," $ map (escapeDBName . fieldDB) $ entityFields ent
, ") VALUES("
, intercalate "," (map (const "?") $ entityFields ent)
, ")"
]
in case entityPrimary ent of
Just _ -> ISRManyKeys sql vals
Nothing -> ISRInsertGet sql "SELECT LAST_INSERT_ID()"
Thoughts on this? You guys want me to take a stab? Not sure this will break some form of backwards compat, it shouldn't assuming we only change the interface of SqlBackend.
Activity
gregwebs commentedon Feb 20, 2015
It would be great if you can come up with something even that only works on Postgres. You will want to maintain the current single insert code path and add a new one for multi-insert.
akurilin commentedon Feb 22, 2015
Actually I'm confused now, I just saw it do a multi-row insertion just fine through
insertMany_as you can see below in the Debug#SQL output:Could someone on your end confirm that this is already working as intended and doesn't need to be "fixed"?
Update:
I figured it out:
insertManyHAS to run the inserts independently, because Postgres will not actually give you the ids without using its customRETURNING idclause. It instead always gives you the # of rows inserted, which isn't super useful if you're looking for the id.From the looks of it we wouldn't need to actually add a new api to the SqlBackend in the postgres case, we could just update it to use
RETURNING. Not sure why that's not already done for theinsertManycase since I can seeRETURNINGbeing used in other places. I might be missing something here.MaxGabriel commentedon May 30, 2015
So this implements the Postgres RETURNING optimization: https://github.com/yesodweb/persistent/compare/postgresInsertManyReturning?expand=1
But it's not great to put postgres-specific behavior in the generic
Database.Persist.Sql.Orphan.PersistStore. Any ideas on moving the behavior to the postgres module?Based on some light research, it looks like this optimization won't be possible for SQLite. I'm not positive, but I don't think it can be implemented in MySQL either. A Stackoverflow question suggests you can accomplish this in MySQL by getting the first inserted row ID (e.g. 1), then adding the number of rows inserted (e.g. 50) minus 1 to get the ID of the last inserted row (50).
However, this definitely won't work if
innodb_autoinc_lock_modeis set to 2, which allows interleaved auto-increment values between differentINSERTstatements. There would also be some complexity created by auto-increment values greater than 1, because then you could have rows1, 11...501. Given all that, it's probably not worth implementing for MySQL.Edit: imo better version here, but I haven't worked out some cabal issues yet.
Use Postgres' RETURNING capability to optimize `insertMany`.
Use Postgres' RETURNING capability to optimize `insertMany`.