Thread: Prepared Statements

Prepared Statements

From
Patrick REED
Date:
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.

Thanks,
Patrick

Re: Prepared Statements

From
"David G. Johnston"
Date:
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.

Re: Prepared Statements

From
Heikki Linnakangas
Date:
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



Re: Prepared Statements

From
Patrick REED
Date:
"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:
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