Prepared Statements

On Fri, Oct 2, 2020 at 1:14 PM Patrick REED <patrickreed352@gmail.com>
wrote:

Hi,

I am having a hard time pinning down which function creates a prepared
statement.

...

e.g.
In your favorite language:

This doesn't seem like the correct list for this question. Ponder the
following and consider sending any follow-up fresh to the -general list.

The behavior would be specific to "your favorite language" so you should
ask there. Client-side drivers can do lots of things besides interacting
directly with the server.
The docs for the server cover what facilities it provides for handling
prepared statements, both in the protocol [1]https://www.postgresql.org/docs/13/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY and in SQL [2]https://www.postgresql.org/docs/13/sql-prepare.html.

You might find that saying why you are investigating this elicits more
helpful responses.

David J.

[1]: https://www.postgresql.org/docs/13/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
https://www.postgresql.org/docs/13/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
[2]: https://www.postgresql.org/docs/13/sql-prepare.html

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

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

Thank you, the functions in the grep are the ones I was looking for.

Patrick

On Sun, Oct 4, 2020 at 11:32 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote: