Re: Postgres prepare statement caching issue in postgres command line

On 5/23/21 12:04 AM, Soumya Prasad Ukil wrote:
> postgres=> execute parallel_execute1('a', 'b');
> postgres=> execute parallel_execute2(1, 2);
> 
> I have enabled pg_stats_statement chrome extension. I have enabled 
> postgres log inside post_parse_analyze_hook 

> and also debugging through gdb using break point. I see it is always 
> going through parse callback. But when I use the same sql statement 
> using Java program through JDBC prepare statement, it does not go to 
> parse callback all the time. It stops after 6th iteration onwards. What 
> I have read in JDBC is that, postgres has a default threshold for 
> prepare statement to 5 
> https://jdbc.postgresql.org/documentation/head/server-prepare.html 
> <https://jdbc.postgresql.org/documentation/head/server-prepare.html>. 
> But after 6th statement onwards, what I see parse callback is not 
> triggered. However the same does not happen in psql command line. If I 
> execute the same statement 10 times, all the times it goes through 
> parsing. Not sure what JDBC does extra.


Take a look at the Notes section here:

https://www.postgresql.org/docs/current/sql-prepare.html

It goes into detail on how a PREPARE statement is handled as regards 
parsing. See if the conditions mentioned there cover your psql case or not.

> 
> 
> On Sunday, 23 May, 2021, 03:53:55 am IST, Adrian Klaver 
> <adrian.klaver@aklaver.com> wrote:
> 
> 
> On 5/22/21 1:25 PM, Soumya Prasad Ukil wrote:
>  > I have been using postgres prepare statement feature through JDBC. I
>  > could see the same prepare statement multiple times, parsing is not
>  > happening in postgres backend, which is expected. However I do the same
>  > in psql command line, that does not happen. It goes to parsing every
>  > time I execute
>  >
>  > Using the following query:
>  >
>  > postgres=> PREPARE parallel_execute1(text, text) AS insert into actor
>  > (first_name, last_name) values ($1, $2);
>  > postgres=> PREPARE parallel_execute2(int, int) AS insert into film_actor
>  > (actor_id, film_id) values($1, $2);
>  > postgres=> execute parallel_execute1;
>  > postgres=> execute parallel_execute2;
> 
> Where are the arguments for the parameters?
> 
> 
>  >
>  >
>  > I have executed both prepared statements 10 times. I could see every
>  > time both queries went through parsing. How do I make sure that they do
>  > not need parsing in command line? JDBC it works in expected manner. Same
>  > code does not go for parsing each time. How can I have same behaviour in
>  > pgsql command line?
> 
>  >
> 
> How are you determining the above?
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com