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