Building a Django driver for Psycopg 3 • Hacker News Board

Re: Building a Django driver for Psycopg 3

#21

Post by acdha » Mon, Aug 02, 2021, 7:56 PM UTC

Waterluvian wrote: post #17

parhamn wrote: post #12

I was wondering what the major changes between psycopg2 and psycopg3 were. Found a post from the maintainer here: https://www.varrazzo.com/blog/2020/03/06/thinking-psycopg3/ Main takeaways: - Asyncio from the ground up - Uses PQexecParams to do database-side escaping and interpolation - ContextManager and transaction api improvements for - Python only fallback if the C extension fails to build

That last one is great if it’s hidden behind a flag and some loud messages saying “failed to build, consider using —-python-only with performance limitation.” Otherwise it’s going to be a source of mysterious performance issues with a single error message hidden somewhere in your CI or other logs.

I'm a bit mixed on a flag because C library dependencies are a fair support burden and an awful lot of people do not have workloads where a pure Python implementation is a significant performance issue because it's lost in the actual work done by the database.

I'd agree with something like a warning on first load or maybe something like an optional package so you could pin `psycopg3[cext]` if you want to guarantee the extension installed.

Re: Building a Django driver for Psycopg 3

#22

Post by slownews45 » Mon, Aug 02, 2021, 8:13 PM UTC

nerdponx wrote: post #18

slownews45 wrote: post #14

The example syntax for connection handling is perfect. The footgun of not releasing the connection (especially from pools at the end of the with) is real. And the concepts are different (block of transactions vs normal with file xxx. ) with connect(DSN) as conn: with conn.transaction(): do_something() with conn.transaction(): do_something_nested() with conn.transaction() as tx: do_something_else() # we were just test…

For what it's worth, using context managers for this kind of thing is pretty much standard nowadays, and not supporting this behavior feels jarring and archaic.

Agreed - I spent so much time trouble shooting this exact issue because I honestly couldn't even imagine that I wasn't freeing the connection.

I ended just giving up and doing a close() after searching for every use. But even then didn't immediately understand what was going on.

With XXX do Y

is totally standard - but here we open but do not close.

Re: Building a Django driver for Psycopg 3

#23

Post by dvarrazzo » Mon, Aug 02, 2021, 9:54 PM UTC

Waterluvian wrote: post #17

parhamn wrote: post #12

I was wondering what the major changes between psycopg2 and psycopg3 were. Found a post from the maintainer here: https://www.varrazzo.com/blog/2020/03/06/thinking-psycopg3/ Main takeaways: - Asyncio from the ground up - Uses PQexecParams to do database-side escaping and interpolation - ContextManager and transaction api improvements for - Python only fallback if the C extension fails to build

That last one is great if it’s hidden behind a flag and some loud messages saying “failed to build, consider using —-python-only with performance limitation.” Otherwise it’s going to be a source of mysterious performance issues with a single error message hidden somewhere in your CI or other logs.

There is a mechanism to ensure that there is no unexpected regression. Exposing the PSYCOPG_IMPL env var a program can make sure to obtain a specific implementation and import fails if it's not available. https://www.psycopg.org/psycopg3/docs/api/pq.html

Re: Building a Django driver for Psycopg 3

#24

Post by dvarrazzo » Mon, Aug 02, 2021, 9:57 PM UTC

jsmeaton wrote: post #4

Casually building a django db backend as an acceptance test. Nice work and great write up! I had déjà vu reading the section on bind parameters in aggregate queries. The oracle backend had a similar issue that was fixed/hacked by comparing values and grouping them into named parameters. https://code.djangoproject.com/ticket/27632

This is interesting, thank you for pointing it out. Worth looking if the problems can be fixed the same way in both the adapter (and probably everything else using server-side binding).

Re: Building a Django driver for Psycopg 3

#25

Post by anarazel » Tue, Aug 03, 2021, 12:40 AM UTC

slownews45 wrote: post #14

The example syntax for connection handling is perfect. The footgun of not releasing the connection (especially from pools at the end of the with) is real. And the concepts are different (block of transactions vs normal with file xxx. ) with connect(DSN) as conn: with conn.transaction(): do_something() with conn.transaction(): do_something_nested() with conn.transaction() as tx: do_something_else() # we were just test…

Could you expand on the "pushing into pg" bit?

Do you want to specify parameters that have a scope larger than a single statement and send the whole parameterized batch in one go?

Re: Building a Django driver for Psycopg 3

#26

Post by [deleted] » Tue, Aug 03, 2021, 9:01 AM UTC

nerdponx wrote: post #19

geenat wrote: post #11

Really great to see more than one viable async database library aside from asyncpg. Thank you!!

What isn't viable about Aiopg? (Assuming you are only talking about Postgres libraries.) Maybe it's somewhat obsolete now that Psycopg itself supports async. I think most people used Asyncpg because it's "easier" and supports useful stuff like connection pools.

[deleted]