Split a query with too many arguments in SQLAlchemy

Question

In my program when I did not run a database update for a long time and then try to update my data, my sqlalchemy script generates a postgresql upsert query with >4000 params each with >8 items.

When the query is executed with databases.Database.execute(query) I end up with this error:

asyncpg.exceptions._base.InterfaceError: the number of query arguments cannot exceed 32767

My idea is to automatically split the query based on the number of arguments as threshold and execute it in two parts and merge the results.

Do you have an idea how to resolve that problem?

Answer 1

I ended up writing a check for the number of query arguments by getting the length of the first argument's dictionary keys in my list of argument dictionaries as they all have the same number of keys=arguments per list item:

args_per_row = len(args_dict_list[0])

PSQL_QUERY_ALLOWED_MAX_ARGS = 32767

allowed_args_per_query = int(math.floor(PSQL_QUERY_ALLOWED_MAX_ARGS/args_per_row))

Then I divided the args_dict_list into parts that have the size of allowed args per query:

query_args_sets = [
    args_dict_list[x:x + allowed_args_per_query] for x in range(
        0,
        len(args_dict_list),
        allowed_args_per_query
    )
]

and finally looped over the query_args_sets and generated and executed a separate query for each set:

for arg_set in query_args_sets:
    query = query_builder.build_upsert(values=arg_set)
    await database.execute(query)