public inbox for [email protected]
help / color / mirror / Atom feedDouble prepare
5+ messages / 4 participants
[nested] [flat]
* Double prepare
@ 2026-05-16 06:40 Igor Korot <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Igor Korot @ 2026-05-16 06:40 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double prepare
@ 2026-05-16 13:46 Tom Lane <[email protected]>
parent: Igor Korot <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2026-05-16 13:46 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Igor Korot <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double prepare
@ 2026-05-16 14:07 Rob Sargent <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Rob Sargent @ 2026-05-16 14:07 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
> On May 16, 2026, at 7:46 AM, Tom Lane <[email protected]> wrote:
>
> Igor Korot <[email protected]> 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?
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double prepare
@ 2026-05-16 14:31 Vladimir Sitnikov <[email protected]>
parent: Rob Sargent <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Vladimir Sitnikov @ 2026-05-16 14:31 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>; Rob Sargent <[email protected]>
>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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double prepare
@ 2026-05-16 14:37 Vladimir Sitnikov <[email protected]>
parent: Vladimir Sitnikov <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Vladimir Sitnikov @ 2026-05-16 14:37 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>; Rob Sargent <[email protected]>
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-prepare...
Looking forward to adding more references there. Feel free pinging me in
the comments.
Vladimir
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-05-16 14:37 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-16 06:40 Double prepare Igor Korot <[email protected]>
2026-05-16 13:46 ` Tom Lane <[email protected]>
2026-05-16 14:07 ` Rob Sargent <[email protected]>
2026-05-16 14:31 ` Vladimir Sitnikov <[email protected]>
2026-05-16 14:37 ` Vladimir Sitnikov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox