OperationalError writing large datasets to the database

[danielhuppmann]

An upload to the iam-diagnostics-internal failed twice with strange error messages:

sqlalchemy.exc.OperationalError: (psycopg.OperationalError) sending query and params failed: number of parameters must be between 0 and 65535
[SQL: SELECT DISTINCT model.name
FROM iamc_timeseries JOIN run ON run.id = iamc_timeseries.run__id JOIN model ON model.id = run.model__id
WHERE iamc_timeseries.id IN (%(id_1_1)s::INTEGER, %(id_1_2)s:: ... ::INTEGER)]
[parameters: {'id_1_1': 1, 'id_1_2': 2, ..., 'id_1_72148': 72148}]

When trying to process that file again, the process again failed with the following error message:

sqlalchemy.exc.IntegrityError: (psycopg.errors.ForeignKeyViolation) update or delete on table "iamc_measurand" violates foreign key constraint "fk_iamc_timeseries_measurand__id_iamc_measurand" on table "iamc_timeseries"
DETAIL:  Key (id)=(2549) is still referenced from table "iamc_timeseries".
[SQL: DELETE FROM iamc_measurand WHERE iamc_measurand.id IN (SELECT iamc_measurand_version.id
FROM iamc_measurand_version
WHERE (iamc_measurand_version.id NOT IN (SELECT iamc_measurand_version.id
FROM iamc_measurand_version
WHERE (EXISTS (SELECT iamc_timeseries_version.id
FROM iamc_timeseries_version
WHERE iamc_timeseries_version.measurand__id = iamc_measurand_version.id AND iamc_timeseries_version.run__id = %(run__id_1)s::INTEGER)) AND iamc_measurand_version.transaction_id <= %(transaction_id_1)s::BIGINT AND iamc_measurand_version.operation_type != %(operation_type_1)s::SMALLINT AND (iamc_measurand_version.end_transaction_id > %(end_transaction_id_1)s::BIGINT OR iamc_measurand_version.end_transaction_id IS NULL))) AND iamc_measurand_version.id IN (SELECT iamc_measurand_version.id
FROM iamc_measurand_version
WHERE (EXISTS (SELECT iamc_timeseries_version.id
FROM iamc_timeseries_version
WHERE iamc_timeseries_version.measurand__id = iamc_measurand_version.id AND iamc_timeseries_version.run__id = %(run__id_1)s::INTEGER)) AND iamc_measurand_version.transaction_id <= %(transaction_id_2)s::BIGINT AND iamc_measurand_version.operation_type != %(operation_type_2)s::SMALLINT AND (iamc_measurand_version.end_transaction_id > %(end_transaction_id_2)s::BIGINT OR iamc_measurand_version.end_transaction_id IS NULL)) AND iamc_measurand_version.transaction_id <= %(transaction_id_3)s::BIGINT AND iamc_measurand_version.operation_type != %(operation_type_3)s::SMALLINT AND (iamc_measurand_version.end_transaction_id > %(end_transaction_id_3)s::BIGINT OR iamc_measurand_version.end_transaction_id IS NULL)) RETURNING iamc_measurand.id]
[parameters: {'run__id_1': 3, 'transaction_id_1': 149884, 'operation_type_1': 2, 'end_transaction_id_1': 149884, 'transaction_id_2': 296734, 'operation_type_2': 2, 'end_transaction_id_2': 296734, 'transaction_id_3': 296734, 'operation_type_3': 2, 'end_transaction_id_3': 296734}]

I assume that the first error occurs because there are a lot variables x regions in that particular run.

Can you please take a look @meksor?

FYI @lisahligono

[danielhuppmann]

The link to that file and processing log is https://iam-diagnostics-internal.apps.ece.iiasa.ac.at/submission?jobId=196ead34-2f90-4376-bd11-569f276001e3&fileId=1705

[meksor]

Lisah already made me aware.
The problem is that there are like 100k timeseries in that dataset and we want to write it all to the database at once.
I could add a bunch of safeguarding around the repositories in scse-toolkit but. I almost think that it's smarter to do the chunking either in the facade layer or in scse-processing since otherwise we have to make sure EVERY SINGLE QUERY has a "data too long" edge case handler.
For context, I already fixed something very similar after the update that allowed pyam to write mixed data to IXMP4.
To make matters worse, the parameter limit is configurable per database so it is not always the same + sqlite has a default that is a lot lower (999 instead of 32k or 65k on pg)