-
Notifications
You must be signed in to change notification settings - Fork 418
Question: batch optimizations for RETURNING clauses. #448
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
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 |
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: