does jooq cache sql as prepared statements

[Rob Sargent]

I'm using jooq3.14, pg_bouncer, tomcat, postgres.  I have a long running process (20 hours) which starts with a select by id, does a million simulations, then does the select again (it's in a stateless servlet) and blows up with

SQL [select "base"."markerset"."id", "base"."markerset"."name", "base"."markerset"."chrom", "base"."markerset"."genome_id", "base"."markerset"."avg_theta", "base"."markerset"."std_theta" from "base"."markerset" where "base"."markerset"."id" = cast(? as uuid)]; ERROR: prepared statement "S_4" does not exist

Or is this pg_bouncer?

[Rob Sargent]

I should have mentioned that pg_bouncer is using

pool_mode = transaction

And the line generating the exception is

        markersetRec = dslContext.selectFrom(MARKERSET).where(MARKERSET.ID.equal(mid)).fetchOne();

Sub-note:  with only 10,000 simulations the entire process works - or at least the one test run did.

[Lukas Eder]

Hi Rob,

jOOQ doesn't cache anything. It just creates a JDBC PreparedStatement behind the scenes. Not sure what's causing this in your case. Perhaps, using StatementType.STATIC_STATEMENT in your Settings could help? That prevents using PreparedStatements in jOOQ and runs everything with inlined parameters and static JDBC Statement types...

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/3166861f-a427-0682-b729-c42ae3b4d39c%40gmail.com.

[Rob Sargent]

Thanks Lukas,
I think I may have to go down that path.  I believe the interjection of pg_bouncer has broken my setup.  I'm going to try another run without pgb, since my tomcat-only setup at AWS has not had the prepared statement issue.

Clarification request: From jooq perspective the prep'd statement is sent every time? Who knows it, the prep'd, as "S_4"?

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6HLkGTLEtZDS_0CEfXcurji44-NDzRuzj9Hu-DLOXCwA%40mail.gmail.com.

[Rob Sargent]

On 2/11/22 13:41, Lukas Eder wrote:

Hi Rob,

jOOQ doesn't cache anything. It just creates a JDBC PreparedStatement behind the scenes. Not sure what's causing this in your case. Perhaps, using StatementType.STATIC_STATEMENT in your Settings could help? That prevents using PreparedStatements in jOOQ and runs everything with inlined parameters and static JDBC Statement types...

Lukas

Until I get a handle on jooq/pgbouncer interaction I'm very tempted by  your suggestion. Since my clients particularly light on db-interaction: get (largish) input data from server; spin for 3 day; send (lots of) results to server I don't see a downside to StatementType.STATIC_STATEMENT.  Does anything jump out to you?

As always, many thanks,
rjs

On Fri, Feb 11, 2022 at 9:11 PM Rob Sargent <robjs...@gmail.com> wrote:

On 2/11/22 12:44, Rob Sargent wrote:

I'm using jooq3.14, pg_bouncer, tomcat, postgres.  I have a long running process (20 hours) which starts with a select by id, does a million simulations, then does the select again (it's in a stateless servlet) and blows up with

SQL [select "base"."markerset"."id", "base"."markerset"."name", "base"."markerset"."chrom", "base"."markerset"."genome_id", "base"."markerset"."avg_theta", "base"."markerset"."std_theta" from "base"."markerset" where "base"."markerset"."id" = cast(? as uuid)]; ERROR: prepared statement "S_4" does not exist

Or is this pg_bouncer?

I should have mentioned that pg_bouncer is using

pool_mode = transaction

And the line generating the exception is

        markersetRec = dslContext.selectFrom(MARKERSET).where(MARKERSET.ID.equal(mid)).fetchOne();

Sub-note:  with only 10,000 simulations the entire process works - or at least the one test run did.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/3166861f-a427-0682-b729-c42ae3b4d39c%40gmail.com.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6HLkGTLEtZDS_0CEfXcurji44-NDzRuzj9Hu-DLOXCwA%40mail.gmail.com.

[Lukas Eder]

On Fri, Feb 11, 2022 at 9:55 PM Rob Sargent <robjs...@gmail.com> wrote:

Thanks Lukas,
I think I may have to go down that path.  I believe the interjection of pg_bouncer has broken my setup.  I'm going to try another run without pgb, since my tomcat-only setup at AWS has not had the prepared statement issue.

Clarification request: From jooq perspective the prep'd statement is sent every time? Who knows it, the prep'd, as "S_4"?

jOOQ prepares the statement afresh every time, yes, assuming that smart connection pools or drivers take care of caching themselves, if appropriate (e.g. ojdbc does this). "S_4" is probably what the server generates, I think? 

[Lukas Eder]

Until I get a handle on jooq/pgbouncer interaction I'm very tempted by  your suggestion. Since my clients particularly light on db-interaction: get (largish) input data from server; spin for 3 day; send (lots of) results to server I don't see a downside to StatementType.STATIC_STATEMENT.  Does anything jump out to you?

PostgreSQL doesn't have a sophisticated execution plan cache. Otherwise, static statements would be prohibitive, of course. I tend to see this as a workaround for this specific case. It's probably still better to investigate why the problem is happening if you have time, but if you don't, then static statements might be good enough. 

[Rob Sargent]

On 2/14/22 02:25, Lukas Eder wrote:

Until I get a handle on jooq/pgbouncer interaction I'm very tempted by  your suggestion. Since my clients particularly light on db-interaction: get (largish) input data from server; spin for 3 day; send (lots of) results to server I don't see a downside to StatementType.STATIC_STATEMENT.  Does anything jump out to you?

PostgreSQL doesn't have a sophisticated execution plan cache. Otherwise, static statements would be prohibitive, of course. I tend to see this as a workaround for this specific case. It's probably still better to investigate why the problem is happening if you have time, but if you don't, then static statements might be good enough. 

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7iLvnsZ9nTak90D4S2nmvGAVyuRP093E1A4N0%3DHxQ2Xg%40mail.gmail.com.

As usual, it was all my fault:  I did have "?prepareThreshold=0" set on the initial proof-of-connectivity test but not set in the servlets' context.xml. 
An interesting(?) anecdote:  the "missing prepared statement" error messages all incremented the counter by two, starting at one. PS-1, PS-3, PG-5 etc.  Never an even number in almost 100 repetitions.