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

Current set_session() implementation doesn't play well with connection pooling #503

Closed
dvarrazzo opened this Issue Feb 3, 2017 · 2 comments

Comments

Projects
None yet
1 participant
@dvarrazzo
Copy link
Member

dvarrazzo commented Feb 3, 2017

Currently set_session() executes SET default_transaction_... to change new sessions parameters. This doesn't play well with connection poolers like pgbouncer caching connections at transaction levels. If a session is set in read-only, other users of the pool can expect bad things happening downstream (real story).

The SET default_transaction_* thing was started to avoid sending extra queries at every transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
...

however postgres has long had parameters on begin (since 8.0) so we can just start the transaction with:

BEGIN ISOLATION LEVEL SERIALIZABLE;

and if we really really still support PG 7.4, for that version we can issue instead:

BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

https://www.postgresql.org/docs/current/static/sql-set-transaction.html

@dvarrazzo dvarrazzo added this to the psycopg 2.7 milestone Feb 3, 2017

@dvarrazzo

This comment has been minimized.

Copy link
Member

dvarrazzo commented Feb 5, 2017

@dvarrazzo

This comment has been minimized.

Copy link
Member

dvarrazzo commented Feb 7, 2017

Merged to master this is the merged patch

@dvarrazzo dvarrazzo closed this Feb 7, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment