Re: Prepared Statements

On 02/10/2020 23:10, Patrick REED wrote:
> Hi,
>
> I am having a hard time pinning down which function creates a prepared
> statement. Say in some language I create a Prepared Statement and send
> it off. Before the first time I execute the prepared statement, which
> function is the one that 'creates' the prepared statement. In other
> words, which function stores it. I know that StorePreparedStatement will
> cache it, but is there anything else.
>
> e.g.
> In your favorite language:
>
> |String statement = "Insert into table_one values 10"; PreparedStatement
> insert = con.prepareStatement(statement); insert.execute() |
>
> The very first time, does it store this just in the plancache or does it
> do something different to 'know' it has stored a Prepared Statement, so
> next time it will invoke it.

Most drivers use what the Extended Query Protocol. The client first
sends a Parse message that contains the SQL text. Next, it sends a Bind
message that contains the query parameters, and Execute to execute it.
The Bind+Execute steps can be repeated multiple times, with different
query parameters. The PREPARE and EXECUTE statements do essentially the
same thing.

In the server code, there is the plan cache. The plan cache tracks when
a plan needs to be invalidated and the query replanned. The handle to an
entry in the plan cache is a CachedPlanSource, which contains the SQL
original and enough information to (re-)plan the query as needed. The
plan cache has entries for all the prepared statements, but also for
statements in PL/pgSQL functions, statements prepared with SPI_prepare()
etc. The plan cache doesn't know or care where the statements came from,
they are all treated the same.

A prepared statement has a name and a CachedPlanSource. They are stored
in a hash table. See StorePreparedStatement() function. If you grep for
callers of StorePreparedStatement(), you'll see that there are two: one
in processing an EXECUTE statement, and one in handling the Extended
Query Protocol.

- Heikki