Skip to content

Question: batch optimizations for RETURNING clauses. #448

New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Closed
crazyhouse33 opened this issue May 25, 2019 · 5 comments · Fixed by #1175
Closed

Question: batch optimizations for RETURNING clauses. #448

crazyhouse33 opened this issue May 25, 2019 · 5 comments · Fixed by #1175

Comments

@crazyhouse33
Copy link

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.

@elprans
Copy link
Member

elprans commented Jun 3, 2019

We can possibly allow both execute and executemany to return the statement result.

@crazyhouse33
Copy link
Author

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...).

@charles-cooper
Copy link

👍 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 *')

@matthewhegarty
Copy link

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.

@serozhenka
Copy link

@elprans any updates on this one? Will this be possible at some point of time?

# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
None yet
Projects
None yet
5 participants