Thread: Postgres prepare statement caching issue in postgres command line
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;
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?
Soumya Prasad Ukil <soumyo_ju@yahoo.co.in> writes:
> 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
doesnot happen. It goes to parsing every time I execute
Perhaps "set plan_cache_mode = force_generic_plan" would help you.
Bear in mind that this is likely to be a net loss overall.
regards, tom lane
On 5/22/21 4:11 PM, Tom Lane wrote: > Soumya Prasad Ukil <soumyo_ju@yahoo.co.in> writes: >> 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 doesnot happen. It goes to parsing every time I execute > Perhaps "set plan_cache_mode = force_generic_plan" would help you. > > Bear in mind that this is likely to be a net loss overall. This (poorly) works around the problem, but does not answer the question. -- Angular momentum makes the world go 'round.
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
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
> 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
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