PREPARE, EXECUTE, DEALLOCATE

Why this stage exists

A web application runs the same few statements thousands of times, with different values. Look up the user with this id, insert this order. Every time, the server tokenizes the text, parses it, checks every name and type, and plans it, and all of that gives the same answer as the time before. Only the values changed.

A prepared statement splits the two. PREPARE does the work that does not depend on the values, once. EXECUTE supplies the values and runs.

$droid --db shop.db
Welcome to droid!
>prepare by_id as select id, name from t where id = $1;
Prepare statement executed successfully
>execute by_id (2);
id | name
2 | bob
(1 rows)
[EXEC] rows_examined=1 index_lookups=1
>execute by_id (1);
id | name
1 | ann
(1 rows)
[EXEC] rows_examined=1 index_lookups=1
>deallocate by_id;
Deallocate statement executed successfully

This stage is the SQL form of the idea, and it needs no network: it works in the REPL as well as over the wire. The next stage does the same through the protocol's own messages, which is what drivers actually use. The stage after that keeps the plan too, and deals with plans that go stale.

Where a parameter's type comes from

$1 has no type of its own. It gets one from where it stands. In where id = $1, it is compared with an INT column, so it is an INT. In values ($1, $2) it is assigned to a column, so it has that column's type. In $1 + 1 it is an operand of arithmetic, so it is an INT. Anywhere else nothing fixes it, and it is a VARCHAR.

Knowing the types at PREPARE time pays off at EXECUTE. A value that cannot be of its parameter's type is refused before anything runs: 'abc' for an INT parameter is 22P02. A string that holds a number, such as '2', is that number, because that is how PostgreSQL reads a quoted value for an int, and clients rely on it.

Every number from $1 to the highest one used must appear somewhere: a statement that uses $2 and never $1 is 42P18, the rule PostgreSQL also applies. A parameter that does appear but stands where nothing fixes its type is simply a VARCHAR.

A statement checked once

PREPARE does not merely store text. It parses the statement and checks it, as if about to run it. A missing table is reported at PREPARE, not at the first EXECUTE, and nothing is kept under that name. What survives PREPARE is a statement that was correct when it was prepared. Whether it is still correct after someone drops an index or a table is the plan-cache stage's question.

Prepared statements belong to the session, like its transaction. Another connection has its own names, and can use the same ones for different statements. When a session ends, its statements go with it.

Output contract for this stage

These are the exact lines, tags and codes the tests check. The full definition is on the output contract page, section 98.

What you should see when done

$droid --db s.db
Welcome to droid!
>create table t (id int primary key, name varchar(20));
Create table statement executed successfully
>prepare ins as insert into t (id, name) values ($1, $2);
Prepare statement executed successfully
>execute ins (1, 'ann');
Insert statement executed successfully (1 rows)
>execute ins ('two', 'bob');
[ERROR:01913] parameter $1 is an INT, and 'two' is not a number
>execute ins (2);
[ERROR:01912] ins takes 2 values, got 1
>prepare ins as select 1;
[ERROR:01910] a statement named ins is already prepared
>deallocate all;
Deallocate statement executed successfully
>execute ins (3, 'cy');
[ERROR:01911] no statement named ins is prepared
>.exit
exiting.. good bye!

Common bugs in this stage

• Substituting the values into the text and parsing again. It works, but it throws away the point of preparing. Worse, a value containing a quote can change the statement. Keep the parsed statement and put values into it.
• Checking only at EXECUTE. A PREPARE over a missing table must fail at once, and keep nothing.
• One global table of statements. Names belong to a session; two clients can both have a p.
• Forgetting statements when a session ends. The next session with that number is someone else.
• Reading '2' as a string for an INT parameter. PostgreSQL clients send numbers that way, and expect it to work.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: a small array of prepared statements in each session struct, searched by name. Parameters in the AST as a node holding their number, filled from an array of values at EXECUTE.
• C++: a std::unordered_map<std::string, Prepared> in the session; a Param{index} alternative in the expression variant.
• Rust: HashMap<String, Prepared> in the session; an Expr::Param(usize) variant, replaced by walking the tree with the values.
• Zig: a std.StringHashMap(Prepared) owned by the session, freed with it.