Large Bundles with lots of new CodeSystems and Ids hits JDBC Parameter Limit

[prb112]

Describe the bug
A clear and concise description of what the bug is.

Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 54842

Environment
Which version of IBM FHIR Server? main (and any with common_token_values)

To Reproduce
Steps to reproduce the behavior:

1. Create an empty db
2. Load fhir-server-test/src/test/resources/testdata/Bundle-ValueSets.json in a Transaction
3. See the exception

Size 27421
[AUDIT   ] J2CA0056I: The Connection Manager received a fatal connection error from the Resource Adapter for resource jdbc/fhir_default_default. The exception is: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.:java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 54842
[ERROR   ]  INSERT INTO common_token_values (token_value, code_system_id)       SELECT v.token_value, v.code_system_id         FROM (VALUES (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VAVARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS
....

1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)), (CAST(? AS VARCHAR(1024)),CAST(? AS INT)) ) AS v(token_value, code_system_id)     ORDER BY v.code_system_id, v.token_value  ON CONFLICT DO NOTHING 
An I/O error occurred while sending to the backend.
[ERROR   ] Unexpected error while processing token value records.
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
[ERROR   ] Failed persisting parameter transaction data. Marking transaction for rollback
Unexpected error while processing token value records.
[ERROR   ] DSRA0304E:  XAException occurred. XAException contents and details are: 
Caused by org.postgresql.util.PSQLException: This connection has been closed.
The error code is: 0
The SQL State is: 08003
.
[ERROR   ] DSRA0302E:  XAException occurred.  Error code is: XAER_RMFAIL (-7).  Exception is: Error rolling back prepared transaction. rollback xid={XidImpl: formatId(57415344), gtrid_length(36), bqual_length(54),
data(0000017da109aa890000000103e7bb10b25c34e944ee51fe8607ab962e5c11ff9c9386460000017da109aa890000000103e7bb10b25c34e944ee51fe8607ab962e5c11ff9c938646000000010000000000000000000000000001)}, preparedXid=null, currentXid={2}
[ERROR   ] failed to commit transaction
javax.transaction.RollbackException
[ERROR   ] Commit global transaction failed. See server log for details
Commit global transaction failed. See server log for details

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

https://stackoverflow.com/questions/1009706/postgresql-max-number-of-parameters-in-in-clause
https://luppeng.wordpress.com/2020/05/20/postgresql-jdbc-driver-upper-limit-on-parameters-in-preparedstatement/

[prb112]

Refactored to be more deterministic and easier to read.
There is agreement that we won't implement a GTT and use a merge into at this juncture.

[punktilious]

Generates invalid SQL when there are more than 16383 items to process:

INSERT INTO common_token_values (token_value, code_system_id)       SELECT v.token_value, v.code_system_id         FROM (VALUES  ) AS v(token_value, code_system_id)     ORDER BY v.code_system_id, v.token_value  ON CONFLICT DO NOTHING
org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"

[prb112]

Ran through the code - the window was pruned, now a sliding window.

[punktilious]

Loaded fhir-server-test/src/test/resources/testdata/Bundle-ValueSets.json which created the new entries, then loaded the same bundle a few more times to make sure there is no regression in the parts of the code where the values already exist.