🐛 OperationalError during archive creation

[mbercx]

When trying to create an archive:

verdi archive create -G workchain/relax -- relax_workchains.aiida

I'm running into the following error

sqlalchemy.exc.OperationalError: (psycopg.OperationalError) sending query and params failed: number of parameters must be between 0 and 65535
[SQL: SELECT DISTINCT db_dbcomputer_1.id
FROM db_dbnode AS db_dbnode_1 JOIN db_dbcomputer AS db_dbcomputer_1 ON db_dbnode_1.dbcomputer_id = db_dbcomputer_1.id

Full Output + Traceback

Report:
Archive Parameters
--------------------  ---------------------
Path                  relax_workchains.aiida
Version               main_0001
Compression           6

Inclusion rules
----------------------------  --------
Computers/Nodes/Groups/Users  Selected
Computer Authinfos            False
Node Comments                 True
Node Logs                     True

Traversal rules
---------------------------------  -----
Follow links input calc forwards   False
Follow links input calc backwards  True
Follow links create forwards       True
Follow links create backwards      True
Follow links return forwards       True
Follow links return backwards      False
Follow links input work forwards   False
Follow links input work backwards  True
Follow links call calc forwards    True
Follow links call calc backwards   True
Follow links call work forwards    True
Follow links call work backwards   True

Traceback (most recent call last):
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/psycopg/server_cursor.py", line 292, in execute
    raise ex.with_traceback(None)
psycopg.OperationalError: sending query and params failed: number of parameters must be between 0 and 65535

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/mbercx/.aiida_venvs/mc3d/bin/verdi", line 8, in <module>
    sys.exit(verdi())
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/mbercx/project/mc3d/git/aiida-core/src/aiida/cmdline/groups/verdi.py", line 117, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/Users/mbercx/project/mc3d/git/aiida-core/src/aiida/cmdline/utils/decorators.py", line 104, in wrapper
    return wrapped(*args, **kwargs)
  File "/Users/mbercx/project/mc3d/git/aiida-core/src/aiida/cmdline/commands/cmd_archive.py", line 220, in create
    create_archive(entities, filename=output_file, archive_format=archive_format, **kwargs)
  File "/Users/mbercx/project/mc3d/git/aiida-core/src/aiida/tools/archive/create.py", line 244, in create_archive
    group_nodes, link_data = _collect_required_entities(
  File "/Users/mbercx/project/mc3d/git/aiida-core/src/aiida/tools/archive/create.py", line 541, in _collect_required_entities
    entity_ids[EntityTypes.COMPUTER].update(
  File "/Users/mbercx/project/mc3d/git/aiida-core/src/aiida/tools/archive/create.py", line 541, in <genexpr>
    entity_ids[EntityTypes.COMPUTER].update(
  File "/Users/mbercx/project/mc3d/git/aiida-core/src/aiida/orm/querybuilder.py", line 1083, in iterall
    for item in self._impl.iterall(self.as_dict(), batch_size):
  File "/Users/mbercx/project/mc3d/git/aiida-core/src/aiida/storage/psql_dos/orm/querybuilder/main.py", line 210, in iterall
    for resultrow in session.execute(stmt):
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
    return self._execute_internal(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2236, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/mbercx/.aiida_venvs/mc3d/lib/python3.10/site-packages/psycopg/server_cursor.py", line 292, in execute
    raise ex.with_traceback(None)
sqlalchemy.exc.OperationalError: (psycopg.OperationalError) sending query and params failed: number of parameters must be between 0 and 65535
[SQL: SELECT DISTINCT db_dbcomputer_1.id
FROM db_dbnode AS db_dbnode_1 JOIN db_dbcomputer AS db_dbcomputer_1 ON db_dbnode_1.dbcomputer_id = db_dbcomputer_1.id

System: macOS Ventura 13.4 (22F2063)
aiida-core version: main branch, installed locally.
Python version: 3.10.13

[giovannipizzi]

The issue is that we are probably generating a query that is very long, with something like 'id = 1 OR id = 2 OR id = 3 OR ....', with more than the max allowed paramerers. The output is truncated there? No closed square bracket? Not very helpful from the pat of SQLA :-)
I'm guessing you have many nodes in the group, and the query that actually fails is not the one reported (maybe). Can you report how many nodes you have in the group workchain/relax?
If the number is very large, can you try making a group with a subset of those nodes, and see if there is a threshold number of nodes for which the query stops working? (my guess is 65535 from the error message, but I'm not sure).

So we better understand if this is the problem, and can further debug.

[mbercx]

Thanks @giovannipizzi! In the end I did the export in two batches indeed. I should also note that I cut off the query, since the next line was a very long one, here is the tail -n3 of the error (cut off the third last line again):

70)s::INTEGER, %(id_1_414471)s::INTEGER, %(id_1_414472)s::INTEGER, %(id_1_414473)s::INTEGER, %(id_1_414474)s::INTEGER, %(id_1_414475)s::INTEGER, %(id_1_414476)s::INTEGER, %(id_1_414477)s::INTEGER, %(id_1_414478)s::INTEGER, %(id_1_414479)s::INTEGER, %(id_1_414480)s::INTEGER, %(id_1_414481)s::INTEGER, %(id_1_414482)s::INTEGER, %(id_1_414483)s::INTEGER, %(id_1_414484)s::INTEGER, %(id_1_414485)s::INTEGER, %(id_1_414486)s::INTEGER, %(id_1_414487)s::INTEGER, %(id_1_414488)s::INTEGER, %(id_1_414489)s::INTEGER, %(id_1_414490)s::INTEGER, %(id_1_414491)s::INTEGER, %(id_1_414492)s::INTEGER, %(id_1_414493)s::INTEGER, %(id_1_414494)s::INTEGER, %(id_1_414495)s::INTEGER, %(id_1_414496)s::INTEGER, %(id_1_414497)s::INTEGER, %(id_1_414498)s::INTEGER, %(id_1_414499)s::INTEGER, %(id_1_414500)s::INTEGER, %(id_1_414501)s::INTEGER, %(id_1_414502)s::INTEGER, %(id_1_414503)s::INTEGER, %(id_1_414504)s::INTEGER, %(id_1_414505)s::INTEGER, %(id_1_414506)s::INTEGER, %(id_1_414507)s::INTEGER, %(id_1_414508)s::INTEGER, %(id_1_414509)s::INTEGER, %(id_1_414510)s::INTEGER, %(id_1_414511)s::INTEGER, %(id_1_414512)s::INTEGER, %(id_1_414513)s::INTEGER, %(id_1_414514)s::INTEGER, %(id_1_414515)s::INTEGER, %(id_1_414516)s::INTEGER, %(id_1_414517)s::INTEGER, %(id_1_414518)s::INTEGER, %(id_1_414519)s::INTEGER, %(id_1_414520)s::INTEGER, %(id_1_414521)s::INTEGER, %(id_1_414522)s::INTEGER, %(id_1_414523)s::INTEGER, %(id_1_414524)s::INTEGER, %(id_1_414525)s::INTEGER, %(id_1_414526)s::INTEGER, %(id_1_414527)s::INTEGER, %(id_1_414528)s::INTEGER, %(id_1_414529)s::INTEGER, %(id_1_414530)s::INTEGER, %(id_1_414531)s::INTEGER, %(id_1_414532)s::INTEGER, %(id_1_414533)s::INTEGER, %(id_1_414534)s::INTEGER, %(id_1_414535)s::INTEGER, %(id_1_414536)s::INTEGER, %(id_1_414537)s::INTEGER, %(id_1_414538)s::INTEGER, %(id_1_414539)s::INTEGER, %(id_1_414540)s::INTEGER, %(id_1_414541)s::INTEGER, %(id_1_414542)s::INTEGER, %(id_1_414543)s::INTEGER, %(id_1_414544)s::INTEGER, %(id_1_414545)s::INTEGER, %(id_1_414546)s::INTEGER, %(id_1_414547)s::INTEGER, %(id_1_414548)s::INTEGER, %(id_1_414549)s::INTEGER, %(id_1_414550)s::INTEGER, %(id_1_414551)s::INTEGER, %(id_1_414552)s::INTEGER, %(id_1_414553)s::INTEGER, %(id_1_414554)s::INTEGER, %(id_1_414555)s::INTEGER, %(id_1_414556)s::INTEGER, %(id_1_414557)s::INTEGER, %(id_1_414558)s::INTEGER, %(id_1_414559)s::INTEGER, %(id_1_414560)s::INTEGER, %(id_1_414561)s::INTEGER, %(id_1_414562)s::INTEGER, %(id_1_414563)s::INTEGER, %(id_1_414564)s::INTEGER) AND CAST(db_dbnode_1.node_type AS VARCHAR) LIKE %(param_2)s::VARCHAR AND db_dblink_1.type IN (%(type_1_1)s::VARCHAR, %(type_1_2)s::VARCHAR, %(type_1_3)s::VARCHAR, %(type_1_4)s::VARCHAR)]
[parameters: {'param_1': '%', 'param_2': '%', 'id_1_1': 1, 'id_1_2': 3, 'id_1_3': 6, 'id_1_4': 7, 'id_1_5': 9, 'id_1_6': 10, 'id_1_7': 12, 'id_1_8': 13, 'id_1_9': 18, 'id_1_10': 19, 'id_1_11': 22, 'id_1_12': 23, 'id_1_13': 26, 'id_1_14': 30, 'id_1_15': 31, 'id_1_16': 37, 'id_1_17': 38, 'id_1_18': 39, 'id_1_19': 41, 'id_1_20': 48, 'id_1_21': 50, 'id_1_22': 51, 'id_1_23': 52, 'id_1_24': 56, 'id_1_25': 60, 'id_1_26': 62, 'id_1_27': 67, 'id_1_28': 71, 'id_1_29': 85, 'id_1_30': 87, 'id_1_31': 88, 'id_1_32': 94, 'id_1_33': 102, 'id_1_34': 105, 'id_1_35': 107, 'id_1_36': 108, 'id_1_37': 112, 'id_1_38': 117, 'id_1_39': 122, 'id_1_40': 126, 'id_1_41': 127, 'id_1_42': 128, 'id_1_43': 142, 'id_1_44': 145, 'id_1_45': 149, 'id_1_46': 150, 'id_1_47': 151, 'id_1_48': 154 ... 414470 parameters truncated ... 'id_1_414519': 1014279, 'id_1_414520': 1014368, 'id_1_414521': 1014369, 'id_1_414522': 1014370, 'id_1_414523': 1014371, 'id_1_414524': 1014372, 'id_1_414525': 1014387, 'id_1_414526': 1014388, 'id_1_414527': 1014389, 'id_1_414528': 1014390, 'id_1_414529': 1014391, 'id_1_414530': 1014393, 'id_1_414531': 1014394, 'id_1_414532': 1014395, 'id_1_414533': 1014396, 'id_1_414534': 1014397, 'id_1_414535': 1014398, 'id_1_414536': 1014399, 'id_1_414537': 1014400, 'id_1_414538': 1014401, 'id_1_414539': 1014402, 'id_1_414540': 1014433, 'id_1_414541': 1014435, 'id_1_414542': 1014436, 'id_1_414543': 1014437, 'id_1_414544': 1014438, 'id_1_414545': 1014439, 'id_1_414546': 1014441, 'id_1_414547': 1014442, 'id_1_414548': 1014443, 'id_1_414549': 1014444, 'id_1_414550': 1014489, 'id_1_414551': 1014536, 'id_1_414552': 1014537, 'id_1_414553': 1014538, 'id_1_414554': 1014539, 'id_1_414555': 1014578, 'id_1_414556': 1014579, 'id_1_414557': 1014580, 'id_1_414558': 1014581, 'id_1_414559': 1014582, 'id_1_414560': 1014583, 'id_1_414561': 1014584, 'id_1_414562': 1014585, 'id_1_414563': 1014586, 'id_1_414564': 1014587, 'type_1_1': 'create', 'type_1_2': 'return', 'type_1_3': 'call_calc', 'type_1_4': 'call_work'}]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

[giovannipizzi]

Ok. We need to rewrite those queries, most probably. Would be good to find which query this is, exactly - probably the one in your original stack trace. One would need to do a join, rather than an explicit list of pks, but one needs to look into the specifics...