Import with many application roles cause a PostgreSQL exception

[robotdan]

Import with many application roles cause a PostgreSQL exception

Description

When using PostgreSQL and using the Import API with many application roles, a PostgreSQL limit may be exceeded that will cause an exception.

PostgreSQL seems to be using a 2 byte integer to address the number of parameters in a prepared statement. A 2 byte integer has a max value of 32767 and once this is exceeded it cannot address the parameters and blows chunks.

I don't know for sure if this is a just a limitation of the PostgreSQL JDBC driver, or if this is defined by the PostgreSQL server. In any case, it doesn't work.

Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 35442
    at org.postgresql.core.PGStream.sendInteger2(PGStream.java:266)
    at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1492)
    at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1815)
    at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1378)

In code path

at com.sun.proxy.$Proxy73.addRolesToUserRegistrationBulk(Unknown Source)
    at io.fusionauth.api.service.user.DefaultUserService.processRegistrations(DefaultUserService.java:1903)

Thanks to @leafknode for reporting the issue and assisting with debug.

Steps to reproduce

1. Call the Import API with 5k users
2. Each user having two registrations each with 20 application roles

Expected behavior

No exceptions.

Platform

PostgreSQL only, MySQL does not seem to fail this same scenario.

Additional context

• How to resolve the "Tried to send an out-of-range integer as a 2-byte value" error pgjdbc/pgjdbc#1311
• Work around PostgreSQL's limit of 32767 bind variables per statement jOOQ/jOOQ#5701
• https://makk.es/blog/postgresql-parameter-limitation/