Thread

1. Double prepare

   Igor Korot <ikorot01@gmail.com> — 2026-05-16T06:40:32Z

   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.
   
   
   
   

2. Re: Double prepare

   Tom Lane <tgl@sss.pgh.pa.us> — 2026-05-16T13:46:27Z

   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
   
   
   
   

3. Re: Double prepare

   Rob Sargent <robjsargent@gmail.com> — 2026-05-16T14:07:01Z

   
   > 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?
   
   
   >    
   
   
   
   

4. Re: Double prepare

   Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2026-05-16T14:31:49Z

   >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
   

5. Re: Double prepare

   Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2026-05-16T14:37:38Z

   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