Subject: Re: psycopg3 and adaptation choices
To: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Cc: psycopg@postgresql.org
References: 
 <CA+mi_8Zdk_beGcbOjkpdo+fchTR5ot1XM2zZc=ujSGqP+WVh-g@mail.gmail.com>
 <2b9859f0-0964-2baa-b6bc-13f975ae0f67@aklaver.com>
 <CA+mi_8ZfV+rEDKaqM-pcSf43FuN4FUJGoGTxvEEXap-x_GWP0Q@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <88fb7a7e-a182-a816-c1a7-8a1f54b65215@aklaver.com>
Date: Sun, 8 Nov 2020 12:35:05 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.4.1
MIME-Version: 1.0
In-Reply-To: 
 <CA+mi_8ZfV+rEDKaqM-pcSf43FuN4FUJGoGTxvEEXap-x_GWP0Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 11/8/20 11:16 AM, Daniele Varrazzo wrote:
> On Sun, 8 Nov 2020 at 18:43, Adrian Klaver <adrian.klaver@aklaver.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.

Alright I understand now.
More below.

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

So the issue in the psycopg3 protocol is making the parameters that are 
passed in separately match up correctly in type to what the server is 
expecting(or can cast implicitly)?

> 
> -- Daniele
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com