Thread: Prepared Statements
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()
Thanks,
Patrick
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] and in SQL [2].
You might find that saying why you are investigating this elicits more helpful responses.
David J.
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."
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:
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