Re: psycopg3 and adaptation choices

On Sun, 8 Nov 2020 at 18:43, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> If I'm following correctly in psycopg2 the adapter does type adaption on
> the client side and passes that to server with oid for processing. In
> psycopg3 you are proposing to let the server do more of the type
> adaption and to that end you are looking for lowest common denominator
> type to pass to server and then let it do the final casting. Is that
> about right or am I way off base?

No, not really. In psycopg2 we compose a query entirely client-side,
and we pass it to the server with no oid indication, only as a big
literal, like it was typed all in psql. In the example of
`cursor.execute("select %s, %s", ["hello", 10])`, the server receives
a literal `select 'hello', 10` and has no idea that there were two
parameters.

In psycopg3 the idea is to use a more advanced protocol, which
separates query and parameters. It brings several benefits: can use
prepared statements (send a query once, several parameters later),
passing large data doesn't bloat the parser (the params don't hit the
lexer/parser), can use binary format (useful to pass large binary
blobs without escaping them in a textual form), the format of the data
is more homogeneous (no need to quoting), so we can use Python objects
in COPY instead of limiting the interface for the copy functions to
file-like objects only.

Both in psycopg2 and 3 there is an adaptation from Python types to
Postgres string representation. In pg2 there is additional quoting,
because apart from numbers and bools you need to quote a literal
string to merge it to the query and make it syntactically valid.

-- Daniele