public inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedDouble prepare
5+ messages / 4 participants
[nested] [flat]
* Double prepare
@ 2026-05-16 06:40 Igor Korot <ikorot01@gmail.com>
2026-05-16 13:46 ` Re: Double prepare Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Igor Korot @ 2026-05-16 06:40 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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 06:40 Double prepare Igor Korot <ikorot01@gmail.com>
@ 2026-05-16 13:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-05-16 14:07 ` Re: Double prepare Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2026-05-16 13:46 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double prepare
2026-05-16 06:40 Double prepare Igor Korot <ikorot01@gmail.com>
2026-05-16 13:46 ` Re: Double prepare Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-05-16 14:07 ` Rob Sargent <robjsargent@gmail.com>
2026-05-16 14:31 ` Re: Double prepare Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rob Sargent @ 2026-05-16 14:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Igor Korot <ikorot01@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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?
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double prepare
2026-05-16 06:40 Double prepare Igor Korot <ikorot01@gmail.com>
2026-05-16 13:46 ` Re: Double prepare Tom Lane <tgl@sss.pgh.pa.us>
2026-05-16 14:07 ` Re: Double prepare Rob Sargent <robjsargent@gmail.com>
@ 2026-05-16 14:31 ` Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
2026-05-16 14:37 ` Re: Double prepare Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Vladimir Sitnikov @ 2026-05-16 14:31 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; Rob Sargent <robjsargent@gmail.com>
>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 06:40 Double prepare Igor Korot <ikorot01@gmail.com>
2026-05-16 13:46 ` Re: Double prepare Tom Lane <tgl@sss.pgh.pa.us>
2026-05-16 14:07 ` Re: Double prepare Rob Sargent <robjsargent@gmail.com>
2026-05-16 14:31 ` Re: Double prepare Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
@ 2026-05-16 14:37 ` Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Vladimir Sitnikov @ 2026-05-16 14:37 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; Rob Sargent <robjsargent@gmail.com>
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 <ikorot01@gmail.com>
2026-05-16 13:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-05-16 14:07 ` Rob Sargent <robjsargent@gmail.com>
2026-05-16 14:31 ` Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
2026-05-16 14:37 ` Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox