I was using postgres java jdbc driver. This error pop up when I was doing a large batch query SELECT * FROM mytable where (pk1, pk2, pk3) in ((?,?,?),(?,?,?).....) with ~20k composite ids (i.e., ~60k placeholder).

The callstack for the exception:

org.postgresql.util.PSQLException: ERROR: stack depth limit exceeded
  Hint: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
        at io.agroal.pool.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:78)
...

This looks like a server side error. It's tricky because:

1. it's hard for me to configure server side things...
2. even I can configure that, but it's hard to know "how large is the query that will blow up the server side stack"

Any ideas for this? Or what's the best practice to do such large id query.

CodePudding user response：

I am not sure about the maximum entries for an IN clause (1000?), but it is way less than 20K. The common way to handle that many would be to create a staging table, in this case containing the variables. Call then v1, v2, v3. Now load the staging table from a file (CSV) then use:

select * 
  from mytable 
 where (pk1, pk2, pk3) in
       (select v1,v2,v3 
          from staging_table
       ); 

With this format there is no limit for items in the staging table. Once the process is complete truncate the staging table in preparation for the next cycle.

CodePudding user response：

I am not sure about the maximum entries for an IN clause (1000?), but it is way less than 20K. The common way to handle that many would be to create a stage table, in this case containing the variables. Call then v1, v2, v3. Now load the stage table from a file (CSV) then use: select * from mytable where (pk1, pk2, pk3) in (select v1,v2,v3 from staging_table