Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ksDTr-000539-EQ for psycopg@arkaria.postgresql.org; Wed, 23 Dec 2020 23:23:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ksDTp-0001um-FG for psycopg@arkaria.postgresql.org; Wed, 23 Dec 2020 23:23:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ksDTp-0001uf-9o for psycopg@lists.postgresql.org; Wed, 23 Dec 2020 23:23:41 +0000 Received: from mail-vs1-xe32.google.com ([2607:f8b0:4864:20::e32]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ksDTi-0004BI-MD for psycopg@postgresql.org; Wed, 23 Dec 2020 23:23:40 +0000 Received: by mail-vs1-xe32.google.com with SMTP id q10so561652vsr.13 for ; Wed, 23 Dec 2020 15:23:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=BrWFEoUMAp7oVAMWSe7IhHcfHPPxiZrleD1mTb/5k6g=; b=nf/v/TSrIZhH1N+y+uocoeMWlNxxhbxGC+asKPsehz20btPUaJauRCTyqlAEPzmkgh +cZF7fIRPvf36qqUwqr7zuikE1vbqyq+g/O35+TVoxLFJvQyCNXeTKR7ofj5orqDfURv YGqQS5RxXnl+q1xu8pdFKl5Ou+XlCMD82gk966aL5ggX3Tu68EezITbr2+GBA2TRDK6g qBSIk1NbfNkjidebsSbQ6qfbP3z3lbiujQCyQcy3TbUsKWzlCSFYi64926N/aSrs7t6I q2aqGf+bS/P0P0c3xuIEtO0Al02pMRyjsE1cFuMT98J6bA0y+D/Hs6anwFLYc08wTJHH FcIg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=BrWFEoUMAp7oVAMWSe7IhHcfHPPxiZrleD1mTb/5k6g=; b=IJPvib3ydz9PXR2IHp31IX3/5p+6mOXAGX7ff8b7HVpXty7Wa1z1kvpZ0lVQkqHNPL eqnJkuXrzOAAs7t1iShVKEfrxsQ2XNpg03fOMDEKT18MYGbOljiXEh3u5dmUWiKZGPOG YMEpxhRIUU0wRWxRDfFV4DJ/hwJRjacvW76Bjrwq7rBR9IdvQD72Dzr4jc6GTJ5Mq0jZ sRfzjtQnz84hXqutuyzfcI/0L+GKIz54n5H7Es80NpHt3PeCvOc5c4nAVQWVy01p8Arh vRmxbZTpaT8Sp05ivGTRjee0cQ7eYvSBX7wgnRMpbZwoQg2+DfTGfPlhYy5o7Wtj2uOm VKgQ== X-Gm-Message-State: AOAM532fmpQwRbgRL8439Sb/lO0SLzuYvIVO4Cuej8B5ZCX2nzRN3yvL Vi8vg4LC+3QrCa8mDaOb/pUnDufewHh9gWprx6k= X-Google-Smtp-Source: ABdhPJxWDPoekzUJ9b6GkXVHNLUb7/6FVh1whMi4ypI1Oq5AaoIQS9IeSzNmUPRbgyg3m0JOoh3+g7l1zeQpk75JCOE= X-Received: by 2002:a67:2e49:: with SMTP id u70mr20089317vsu.56.1608765813026; Wed, 23 Dec 2020 15:23:33 -0800 (PST) MIME-Version: 1.0 References: <25a137a0-9ad3-bb9f-b008-263dcc81a645@aklaver.com> <331f273f-fc13-8d80-b209-c94c0960268b@aklaver.com> In-Reply-To: From: Vladimir Ryabtsev Date: Wed, 23 Dec 2020 15:23:22 -0800 Message-ID: Subject: Re: psycopg3, prepared statements To: Adrian Klaver Cc: Daniele Varrazzo , psycopg@postgresql.org Content-Type: multipart/alternative; boundary="000000000000cff46405b729fa5b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000cff46405b729fa5b Content-Type: text/plain; charset="UTF-8" Cause (query, types) can give more combinations than (query,)? On Wed, 23 Dec 2020 at 15:12, Adrian Klaver wrote: > On 12/23/20 2:53 PM, Daniele Varrazzo wrote: > > On Tue, 22 Dec 2020 at 22:36, Daniele Varrazzo > > wrote: > >> > >> On Tue, 22 Dec 2020 at 05:39, Vladimir Ryabtsev > wrote: > > > > > > > Heads up about this: it's better than I thought! > > > > I wrote a first implementation of the prepared statements cache using > > the query as a key, but it's actually enough to use the (query, types) > > tuple in order to tell apart statements that are executed with > > different types. This way even the "SELECT %s" case won't be a > > problem. Of course a statement executed with a mix of types will be > > prepared later than `prepare_threshold`, but I think it's perfectly > > Alright I was following you until you got to above. I'm not following > why it would overshoot prepare_threshold? > > > acceptable: the case doesn't happen often and having the query > > prepared after 10 times instead of 5 doesn't change much if it will be > > executed hundreds of times or more. > > > > What seems a feature-complete branch is available in [1]. The tests > > [2] illustrate the main behaviour of the prepared statements system. > > > > [1]: https://github.com/psycopg/psycopg3/tree/prepared-statements>. > > [2]: > https://github.com/psycopg/psycopg3/blob/prepared-statements/tests/test_prepared.py > > > > Off to do some benchmarks now... > > > > -- Daniele > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > --000000000000cff46405b729fa5b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Cause (query, types) can give more combinations=C2=A0than = (query,)?


On Wed, 23 Dec 2020 at 15:12, Adrian Klaver <adrian.klaver@aklaver.com>= wrote:
On 12/23= /20 2:53 PM, Daniele Varrazzo wrote:
> On Tue, 22 Dec 2020 at 22:36, Daniele Varrazzo
> <da= niele.varrazzo@gmail.com> wrote:
>>
>> On Tue, 22 Dec 2020 at 05:39, Vladimir Ryabtsev <greatvovan@gmail.com> wr= ote:
>

>
> Heads up about this: it's better than I thought!
>
> I wrote a first implementation of the prepared statements cache using<= br> > the query as a key, but it's actually enough to use the (query, ty= pes)
> tuple in order to tell apart statements that are executed with
> different types. This way even the "SELECT %s" case won'= t be a
> problem. Of course a statement executed with a mix of types will be > prepared later than `prepare_threshold`, but I think it's perfectl= y

Alright I was following you until you got to above. I'm not following <= br> why it would overshoot prepare_threshold?

> acceptable: the case doesn't happen often and having the query
> prepared after 10 times instead of 5 doesn't change much if it wil= l be
> executed hundreds of times or more.
>
> What seems a feature-complete branch is available in [1]. The tests > [2] illustrate the main behaviour of the prepared statements system. >
> [1]: https://github.com/psycopg/psy= copg3/tree/prepared-statements>.
> [2]: https:/= /github.com/psycopg/psycopg3/blob/prepared-statements/tests/test_prepared.p= y
>
> Off to do some benchmarks now...
>
> -- Daniele
>


--
Adrian Klaver
adrian.klave= r@aklaver.com
--000000000000cff46405b729fa5b--