Double prepare
Hi, ALL,
Is there a way to see if the query has been prepared already (in libpq)?
Trying to avoid a following scenario (pseudo-code):
[code]
int main()
{
test();
test();
}
void test()
{
PGresult *res = PQprepare( conn, "my query", "SELECT * FROM
my_table", /* rest of params */ );
PQclear( res );
}
[/code]
I presume there is a penalty for that even if the code succeeds.
Or is it better to run PQprepare for all known parameterized queries
in the very beginning of the program and just execute them when needed?
Thank you.
Igor Korot <ikorot01@gmail.com> writes:
Is there a way to see if the query has been prepared already (in libpq)?
Use the pg_prepared_statements view. I don't think libpq keeps any
client-side state about this.
Or is it better to run PQprepare for all known parameterized queries
in the very beginning of the program and just execute them when needed?
Very probably. Querying pg_prepared_statements every time would be
expensive.
regards, tom lane
On May 16, 2026, at 7:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Igor Korot <ikorot01@gmail.com> writes:
Is there a way to see if the query has been prepared already (in libpq)?
Use the pg_prepared_statements view. I don't think libpq keeps any
client-side state about this.Or is it better to run for all known parameterized queries
in the very beginning of the program and just execute them when needed?Very probably. Querying every time would be
expensive.
If I am following correctly, one may query pg_prepared_statements with a specific query in hand. Should it not then be possible to cache that query as having been planned/prepared and proceed accordingly?
Might there be value in calling PQprepare as late as possible against most up-to-dare data?
Show quoted text
Might there be value in calling PQprepare as late as possible against most
up-to-dare data?
Or is it better to run PQprepare for all known parameterized queries
in the very beginning of the program and just execute them when needed?
In pgjdbc we maintain a client-side cache, so we don't have to prepare
everything in advance.
Frankly, I find it was a very helpful tool from the library perspective, as
it automatically optimized applications without
requiring application rebuild. The added prepared statement cache cut the
app server's response times from 1s to 0.5s (think of a generic enterprise
webpage).
Note that sql text is not a sufficient caching key: the same sql text might
have completely different
execution plans depending on the parameter types.
Note that statements prepare per-connection, so if you prepare everything
in advance, there might be noticeable overhead (cpu and memory)
if the specific connection uses only a few queries.
Vladimir
A couple more things to keep in mind:
1) The client has to re-prepare statements on "deallocate all", "alter",
"create", "drop", "alter", "set search_path" statements.
The backend does not automatically keep the prepared statement workable
across those calls.
The offending error messages are "ERROR: cached plan must not change result
type", "ERROR: prepared statement "S_2" does not exist"
2) I've an answer regarding "prepared statement lifespan" at
https://stackoverflow.com/questions/32297503/whats-the-life-span-of-a-postgresql-server-side-prepared-statement/32300222#32300222
Looking forward to adding more references there. Feel free pinging me in
the comments.
Vladimir