Question: batch optimizations for RETURNING clauses. #448
Comments
|
We can possibly allow both |
|
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...). |
|
👍 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 *') |
This would be fantastic. ATM I have to trade off using |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.
The text was updated successfully, but these errors were encountered: