Presto: PreparedStatement#close does not deallocate the prepared statement from session

[Micka33]

Hi Guys,

Here are the versions:

• OSX (does the same on my production environment - Ubuntu)
• Presto JDBC Driver/0.206
• Presto CLI 0.206
• Same for presto server
• NiFi: stable 1.7.1

The actual issue

I am using NiFi and its DBCPConnectionPool to query Presto with the JDBC Driver.
It works the first few times. But after a while it doesn't work anymore and I get the following error on presto side:

tail -f /usr/local/var/presto/data/var/log/server.log
2018-07-23T17:32:11.402+0200    WARN    http-worker-520 org.eclipse.jetty.http.HttpParser   Header is too large 8193>8192

The first request send to Presto via NiFi (found it via mitmproxy) is:

X-Presto-User:                 micka
X-Presto-Source:               presto-jdbc
X-Presto-Time-Zone:            Europe/Paris
X-Presto-Language:             en-US
X-Presto-Transaction-Id:       NONE
X-Presto-Client-Capabilities:  PATH
User-Agent:                    Presto JDBC Driver/0.206
Content-Type:                  text/plain; charset=utf-8
Content-Length:                64
Host:                          127.0.0.1
Connection:                    Keep-Alive
Accept-Encoding:               gzip
---RAW BODY---
PREPARE statement1 FROM SELECT * FROM pg.public.csv_test limit 5

The seconde one is:

X-Presto-User:                 micka
X-Presto-Source:               presto-jdbc
X-Presto-Time-Zone:            Europe/Paris
X-Presto-Language:             en-US
X-Presto-Prepared-Statement:   statement1=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Transaction-Id:       NONE
X-Presto-Client-Capabilities:  PATH
User-Agent:                    Presto JDBC Driver/0.206
Content-Type:                  text/plain; charset=utf-8
Content-Length:                18
Host:                          127.0.0.1
Connection:                    Keep-Alive
Accept-Encoding:               gzip
---RAW BODY---
EXECUTE statement1

When it stops working with the header to large error, it is:

X-Presto-User:                 micka
X-Presto-Source:               presto-jdbc
X-Presto-Time-Zone:            Europe/Paris
X-Presto-Language:             en-US
X-Presto-Prepared-Statement:   statement36=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement37=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement34=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement78=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement35=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement79=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement9=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement7=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement38=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement8=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement39=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement5=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement6=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement3=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement4=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement1=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement2=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement72=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement73=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement70=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement71=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement32=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement76=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement33=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement77=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement30=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement74=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement31=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement75=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement47=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement48=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement45=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement46=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement49=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement80=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement83=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement40=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement84=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement81=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement82=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement43=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement44=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement41=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement85=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement42=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement14=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement58=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement15=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement59=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement12=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement56=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement13=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement57=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement18=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement19=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement16=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement17=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement50=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement51=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement10=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement54=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement11=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement55=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement52=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement53=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement25=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement69=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement26=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement23=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement67=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement24=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement68=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement29=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement27=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement28=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement61=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement62=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement60=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement21=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement65=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement22=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement66=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement63=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement20=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement64=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Transaction-Id:       NONE
X-Presto-Client-Capabilities:  PATH
User-Agent:                    Presto JDBC Driver/0.206
Content-Type:                  text/plain; charset=utf-8
Content-Length:                19
Host:                          127.0.0.1
Connection:                    Keep-Alive
Accept-Encoding:               gzip
---RAW BODY---
EXECUTE statement85

Why I post it here

I use the exact same flow with the PostgreSQL JDBC Driver, and it works perfectly. So, I figured the issue might be in the PrestoDB JDBC driver.

Reproduce it

The bug is easily reproducible in a local environment.

1. Install NiFi
2. Install Presto (create a catalog, any will do)
3. Download the Official Presto JDBC Driver and move into a directory accessible by NiFi.
4. Configure NiFi to query Presto, each 2 secondes.
5. Capture the request using a tool such as mitmproxy.
   My presto server run on 127.0.0.1:9090, I configured NiFi to query presto on 127.0.0.1:8484 and use mitmproxy in between, like so :
   mitmproxy --mode reverse:http://127.0.0.1:9090 --listen-host 127.0.0.1 --listen-port 8484 --replacements :~s:\/\/127\.0\.0\.1/:\/\/127\.0\.0\.1\:9090/

The NiFi DataFlow

The ExecuteQuery configuration

The DBCPConnectionPool configuration

[kokosing]

You can try to play with http-server.max-request-header-size config property, see (https://groups.google.com/forum/#!topic/presto-users/CMumJdf2kU0).

You might find more information about the issue here:

• https://github.com/prestodb/presto/issues/5759
  Issue is going to be fixed with v2 protocol and work is tracked here: -
• https://github.com/prestodb/presto/pull/9607

Also you might want to test Starburst Presto release (see https://docs.starburstdata.com/latest/release/release-0.203-e.html) which has Support prepared statements that are longer than 4K bytes..

[findepi]

this looks like you're not closing prepared statements. I vaguely recall JDBC spec mandating the driver to keep track of prepared statements and closing them for the user, but in actual application programming it never was a recommended approach.

@Micka33 Can you verify you need all these prepared statements to be open at the same time?

X-Presto-Prepared-Statement:   statement36=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement37=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement34=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement78=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement35=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement79=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement9=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement7=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement38=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement8=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement39=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement5=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement6=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement3=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement4=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement1=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement2=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement72=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement73=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement70=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement71=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement32=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement76=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement33=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement77=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement30=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement74=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement31=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement75=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement47=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement48=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement45=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement46=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement49=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement80=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement83=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement40=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement84=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement81=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement82=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement43=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement44=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement41=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement85=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement42=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement14=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement58=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement15=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement59=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement12=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement56=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement13=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement57=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement18=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement19=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement16=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement17=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement50=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement51=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement10=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement54=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement11=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement55=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement52=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement53=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement25=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement69=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement26=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement23=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement67=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement24=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement68=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement29=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement27=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement28=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement61=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement62=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement60=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement21=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement65=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement22=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement66=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement63=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement20=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A
X-Presto-Prepared-Statement:   statement64=SELECT+*%0AFROM%0A++pg.public.csv_test%0ALIMIT+5%0A

[Micka33]

@kokosing, @findepi
Thank you for your help.

I tested the starbust jdbc driver. But the same issue happens. It stacks the statements in the header.
Actually, I tested all the following providers: Teradata, Simba, Prestodb, and now Starburst. And they all stack the statements in the header.

Even though I believe having a prepared statement inside a header is not ideal, I don't think this is the issue here. I think increasing max-request-header-size, would only delay the issue (and not for long).

@findepi, I don't really know how prepared statements work, but I am pretty sure I don't need them to stay. The query I run returns a result immediatly (if it has anything to do with it).

I took a look at the source code of ExecuteSQL processor in NiFi.

L276                    hasResults = st.getMoreResults(Statement.CLOSE_CURRENT_RESULT);

I am unsure this line close the statement. What I am sure about is that this code run the same for all JDBC connectors, and it works with the PostgreSQL JDBC.

I am no Java expert, but If you tell me the ExecuteSQL processor doesn't close the statement, I will try myself to write my own InvokeScriptedProcessor in Groovy to make sure the statement is closed.

[findepi]

@Micka33 this line closes a ResultSet, not PreparedStatement. In the NiFi code the following line is responsible for closing the PreparedStatement:
https://github.com/apache/nifi/blob/rel/nifi-1.7.1/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/ExecuteSQL.java#L219-L220

so actually we have a bug. Apologies for closing this too fast.

[findepi]

Fix https://github.com/prestodb/presto/pull/11122

[kokosing]

I tested the starbust jdbc driver.

To have support for long prepared statements you need to have server and client installed, not only driver (client).

[Micka33]

@findepi Nice! thank you for fixing! Do you know when/how to get the JDBC file from your merge request?

@kokosing Yep, I understood that, but I only really wanted to test the JDBC behavior. 👍