asyncpg
asyncpg copied to clipboard
MagicStack
Question: batch optimizations for RETURNING clauses.
Hi, I would like to know if you plan to provide a way of using the kind of "batch optimization" you already did (executemany, copy_from_records...) using the "RETURNING" psql statement?
Right now since I need to use fetch to access the result of my inserts, and fetch do not have any "batch optimization", I am planing to use a preparedstatement for each inserted row. (I build the query once and then use python unpacking * syntax on each row to feed the statement).
I saw your answer on https://stackoverflow.com/questions/43739123/best-way-to-insert-multiple-rows-with-asyncpg/43939504#43939504, But I need the "Defaulf feature".
It would have been so nice if copy_records_to_table or executemany gave acces to the returned rows :(
If you do not plan to do that, the question is the following:
How would you to efficiently implement the function with asyncpg:
insertRowsAndGetResult(asyncpgConnection, rows, table, columns)
-"rows" being a big list of list of value to insert. -"columns" being the list of target columns (it define on which columns the values are mapped) -The existings columns not specified should be inserted with default value
Thanks for your work. Sorry for bothering you but I think a lot of people who need to use the returning clause are encountering the same problem/question.
We can possibly allow both execute and executemany to return the statement result.
Or add a fetch many method. I imagine that this is cool to have one efficient way of interacting with the db without waiting any returns (exec, execmany, copy), and the other way a little bit slower because it also wait and manage the result of the queries to pass it to python(fetch,fetchmany, fetchcopy_from_records...).
:+1: for this
I guess while this is still being implemented, a workaround is to use a temp table in conjunction with copy. Something like this:
async with conn.transaction():
await conn.execute('CREATE TEMP TABLE IF NOT EXISTS tmptable (LIKE dst)')
await conn.execute('TRUNCATE tmptable')
await conn.copy_records_to_table('tmptable', records)
res = await conn.fetch('INSERT INTO dst SELECT * FROM tmptable RETURNING *')
We can possibly allow both execute and executemany to return the statement result.
This would be fantastic. ATM I have to trade off using conn.execute() for readability vs executemany() for performance.
