Re: Postgres prepare statement caching issue in postgres command line - Mailing list pgsql-general

From Soumya Prasad Ukil
Subject Re: Postgres prepare statement caching issue in postgres command line
Date
Msg-id 327759732.2385802.1621753452973@mail.yahoo.com
Whole thread Raw
In response to Re: Postgres prepare statement caching issue in postgres command line  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Postgres prepare statement caching issue in postgres command line
List pgsql-general
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. 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. 


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

pgsql-general by date:

Previous
From: Bo Peng
Date:
Subject: Re: Proposed Chinese Translation of Community Code of Conduct
Next
From: Adrian Klaver
Date:
Subject: Re: The contents of the pg_timezone_names view bring some surprises