Date: Mon, 21 Dec 2020 16:36:07 +0100
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: psycopg@lists.postgresql.org, psycopg@postgresql.org
Subject: Re: psycopg3, prepared statements
Message-ID: <X+DA52EKAZg6S45N@hermes.hilbert.loc>
Mail-Followup-To: psycopg@lists.postgresql.org, psycopg@postgresql.org
References: 
 <CA+mi_8aAuORkdXZ9bG_GpU34iPCGktMO83ktn+ODjL+Y=Y=e3Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+mi_8aAuORkdXZ9bG_GpU34iPCGktMO83ktn+ODjL+Y=Y=e3Q@mail.gmail.com>
Ma_X_il-Followup-to: d
Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net
Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net
X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net
X-Pri_X_ority: 2 (High)
Sender: <karsten.hilbert@gmx.net>
X-Provags-ID: V03:K1:/WW6CG2yrOQjczVNmC2vdm/FDmft5nyAV4cDIns9lvl9dypGZ8r
 y7xqsC1tPRAthNwlx9ZQDSje53wHhtnbWX5dvNGANy3kdiDBR7x3e/FzjJwyRZRSbnXXhCL
 cbrBMbi4mefyZTG+oKSKO4gnrmiN8vFKaoUPP/HlR67Eps0WZtLFAS8sxgnbnaWCVmxe07d
 DlyLfn3T6Lh2+KMM75aLA==
X-Spam-Flag: NO
X-UI-Out-Filterresults: notjunk:1;V03:K0:BTDFZ12eyvw=:ObwY676NW2FfwTrFcmOEo3
 DQPfAMYEGvt5CZfUneqXuS/bnZKaqz0Iypf/zSGi4NW+0qxx+l3MYkWdPGRkcGSRetLnVS4Ds
 YkoFU5OhfCKgd5jZfYEKW7dzaoDzvK9Gt4zlZvquSPTLJvSKOub713iIqMOAeL5fiGRwtKfhG
 WDtDH9bC4Onq8tNY7mTs35vaKW6snLDr/TNlIn77vF99t5W3JGM6ICPPQAcP5hNqgWxWVFvvz
 l9lCW2RFEtjfLA2fC+QlLaQzqCuLGjiFodyK9U8TggLW6hMGdPL1HFqsBu1R+AOYQoiNPHRb3
 dxvH6Gt7Qg1xUA00FRzHTJlVnust7qAnFEZPmAAdgEAPXLujGRgalHXEqAZ4xwBGGb2fOELXa
 FWaqTK48a26aNZN0sfEDvA8rp/Qj0ku+xzKWbNqDjNn935U8BdmhYmIgTAfJu2IBqvJtKLBN9
 pF82l3WaBLbwzLw1l4PFuUNsCkzKo51ifFzNRKrt6EA9MavrmY04REcMt5cq4dutZWK80mSV2
 8A1Js8jJAqvH6alV6bXMgeXFy14XiExLQzl0uYcBZTxX9QvF55z33DFVSLTrd1wdERBmBnQ0g
 +Acyuc/qlWYXB9Gq2wmMWMfqVfUWfmmq9kmIn7v5vZyg7GMe3y4hcIWYd1uW/kYFI517zCpSI
 YimVhbBB04lESfwu+85ozPCTEPdq/CBLKoqovnQhCSWmHO34JcLtboAzxGN0Tt43si4W8KnEn
 cB+lQUH5yCvY6QTJoTutW8wqPKnCHC4HremKShcwAWtcAx9rSUVKQqpOJrYTUuLbIK1B0tLIo
 exeSRuMqZ9yy8ijuLRQRy7Y+DvVtOwtEA1QRuMpjKhUBYe7yQKwNT+K/FfN+dTT5YvzHiee2Y
 kBsrpvAbLIn7uJ3ghKHg==
Content-Transfer-Encoding: quoted-printable
List-Id: <psycopg.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:psycopg@lists.postgresql.org>
List-Owner: <mailto:psycopg-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/psycopg>
Precedence: bulk

Am Mon, Dec 21, 2020 at 02:24:37PM +0000 schrieb Daniele Varrazzo:

> What I'm thinking about is to prepare queries automatically with a schem=
a such:
>
> - decisions are made after the query is transformed to postgres format
> (i.e. it is reduced to bytes, all the client-side manipulations have
> been done, placeholders have been transformed to $ format). There is
> an object in psycopg3 that takes care of this transformation [2]
> - the number of times a query is seen is stored in a LRU cache on the co=
nnection
> - if a query is seen more than `connection.prepare_threshold` times
> (proposed default: 5) then it is prepared with the name
> f'pg3_{hash(query)}' and the following executions are prepared.
> - if more than `connection.prepared_number` queries are prepared, the
> one used least recently is deallocated and evicted from the cache
> (proposed default: 100).
> - Parameters may be fudged on the connection: prepared_threshold=3D0
> would prepare all queries, prepared_threshold=3DNone would disable
> preparing.
> - For the control freak, cursor.execute(query, params, prepare=3DTrue)
> would prepare the query immediately, if it isn't already,
> prepare=3DFalse would avoid preparation. The default None would enable
> the automatic choice.

:-)  seems like a well-balanced scheme to me !

Karsten
=2D-
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B