Skip to content
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.

Sign up for GitHub

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

Jump to bottom

use psycopg2 "values" mode by default; separate out from "batch" #5401

Closed
zzzeek opened this issue Jun 17, 2020 · 2 comments
Closed

use psycopg2 "values" mode by default; separate out from "batch" #5401

zzzeek opened this issue Jun 17, 2020 · 2 comments
Labels
performance where performance can be improved. add "bug" only if it's a performance degradation postgresql sql
Milestone
1.4

Comments

@zzzeek
Copy link
Member

zzzeek commented Jun 17, 2020

psycopg2's executemany is very slow so we should test having "values" mode by default throughout CI, i.e. https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-executemany-mode . this will also integrate with #5263 so that the ORM with psycopg2 will by insanely fast for inserts compared to how it is now.
@zzzeek zzzeek added requires triage New issue that requires categorization performance where performance can be improved. add "bug" only if it's a performance degradation postgresql sql and removed requires triage New issue that requires categorization labels Jun 17, 2020
@zzzeek zzzeek added this to the 1.4 milestone Jun 17, 2020
@zzzeek zzzeek changed the title use psycopg2 "values" mode by default use psycopg2 "values" mode by default; separate out from "batch" Jun 24, 2020
@zzzeek
Copy link
Member Author

zzzeek commented Jun 24, 2020

So the "batch" mode that's implied by "values" at the moment is only for UPDATE statements and per psycopg2 documentation it removes "cursor.rowcount". I think "values" for UPDATE is less compelling as a default so I will break out "values" and "batch" into two separate binary bits, and the default will be a new token "values_only". "values" will become "values_plus_batch".

@sqla-tester
Copy link
Collaborator

Mike Bayer has proposed a fix for this issue in the master branch:

Default psycopg2 executemany mode to "values_only" https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2051

sqlalchemy-bot pushed a commit that referenced this issue Jun 28, 2020
@zzzeek
ORM executemany returning
08c46ee 
Build on #5401 to allow the ORM to take advanage
of executemany INSERT + RETURNING.

Implemented the feature

updated tests

to support INSERT DEFAULT VALUES, needed to come up with
a new syntax for compiler INSERT INTO table (anycol) VALUES (DEFAULT)
which can then be iterated out for executemany.

Added graceful degrade to plain executemany for PostgreSQL <= 8.2

Renamed EXECUTEMANY_DEFAULT to EXECUTEMANY_PLAIN

Fix issue where unicode identifiers or parameter names wouldn't
work with execute_values() under Py2K, because we have to
encode the statement and therefore have to encode the
insert_single_values_expr too.

Correct issue from #5401 to support executemany + return_defaults
for a PK that is explicitly pre-generated, meaning we aren't actually
getting RETURNING but need to return it from compiled_parameters.

Fixes: #5263
Change-Id: Id68e5c158c4f9ebc33b61c06a448907921c2a657
@Taragolis Taragolis mentioned this issue Mar 13, 2024
Closed
@Taragolis Taragolis mentioned this issue Mar 21, 2024
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
performance where performance can be improved. add "bug" only if it's a performance degradation postgresql sql
Projects
None yet
Milestone
1.4
Development

No branches or pull requests
2 participants
@zzzeek @sqla-tester