Error when running first notebook cell block of "Querying for data" section

[unkcpz]

Got the following error when running cell block https://aiida-tutorials.readthedocs.io/en/tutorial-2022-intro/sections/managing_data/querying.html#querying-for-data from notebook. I guess this might be an aiida-core issue? @chrisjsewell

09/27/2022 10:08:53 AM <2081> sqlalchemy.pool.impl.QueuePool: [ERROR] Error closing cursor
Traceback (most recent call last):
  File "/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py", line 1099, in fetchmany
    new = dbapi_cursor.fetchmany(size - lb)
psycopg2.ProgrammingError: named cursor isn't valid anymore

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1995, in _safe_close_cursor
    cursor.close()
psycopg2.ProgrammingError: named cursor isn't valid anymore
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py in fetchmany(self, result, dbapi_cursor, size)
   1098             try:
-> 1099                 new = dbapi_cursor.fetchmany(size - lb)
   1100             except BaseException as e:

ProgrammingError: named cursor isn't valid anymore

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

ProgrammingError                          Traceback (most recent call last)
<ipython-input-1-916cde5a3649> in <module>
    134         structure.set_extra('formula', structure.get_formula(mode='count'))
    135 
--> 136 store_formula_in_extra()

<ipython-input-1-916cde5a3649> in store_formula_in_extra()
    131     query = QueryBuilder()
    132     query.append(StructureData, filters={'extras':{'!has_key':'formula'}})
--> 133     for structure, in query.iterall():
    134         structure.set_extra('formula', structure.get_formula(mode='count'))
    135 

/opt/conda/lib/python3.9/site-packages/aiida/orm/querybuilder.py in iterall(self, batch_size)
   1048         :returns: a generator of lists
   1049         """
-> 1050         for item in self._impl.iterall(self.as_dict(), batch_size):
   1051             # Convert to AiiDA frontend entities (if they are such)
   1052             for i, item_entry in enumerate(item):

/opt/conda/lib/python3.9/site-packages/aiida/storage/psql_dos/orm/querybuilder/main.py in iterall(self, data, batch_size)
    167             stmt = build.query.statement.execution_options(yield_per=batch_size)
    168 
--> 169             for resultrow in self.get_session().execute(stmt):
    170                 yield [self.to_backend(rowitem) for rowitem in resultrow]
    171 

/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/result.py in iterrows(self)
    380 
    381             def iterrows(self):
--> 382                 for row in self._fetchiter_impl():
    383                     row = make_row(row) if make_row else row
    384                     if post_creational_filter:

/opt/conda/lib/python3.9/site-packages/sqlalchemy/orm/loading.py in chunks(size)
    140 
    141             if yield_per:
--> 142                 fetch = cursor.fetchmany(yield_per)
    143 
    144                 if not fetch:

/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/result.py in fetchmany(self, size)
   1095         """
   1096 
-> 1097         return self._manyrow_getter(self, size)
   1098 
   1099     def all(self):

/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/result.py in manyrows(self, num)
    540                     num = real_result._yield_per
    541 
--> 542                 rows = self._fetchmany_impl(num)
    543                 if make_row:
    544                     rows = [make_row(row) for row in rows]

/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py in _fetchmany_impl(self, size)
   1806 
   1807     def _fetchmany_impl(self, size=None):
-> 1808         return self.cursor_strategy.fetchmany(self, self.cursor, size)
   1809 
   1810     def _raw_row_iterator(self):

/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py in fetchmany(self, result, dbapi_cursor, size)
   1099                 new = dbapi_cursor.fetchmany(size - lb)
   1100             except BaseException as e:
-> 1101                 self.handle_exception(result, dbapi_cursor, e)
   1102             else:
   1103                 if not new:

/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py in handle_exception(self, result, dbapi_cursor, err)
    939 
    940     def handle_exception(self, result, dbapi_cursor, err):
--> 941         result.connection._handle_dbapi_exception(
    942             err, None, None, dbapi_cursor, result.context
    943         )

/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2122                 util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2123             elif should_wrap:
-> 2124                 util.raise_(
   2125                     sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2126                 )

/opt/conda/lib/python3.9/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    206 
    207         try:
--> 208             raise exception
    209         finally:
    210             # credit to

/opt/conda/lib/python3.9/site-packages/sqlalchemy/engine/cursor.py in fetchmany(self, result, dbapi_cursor, size)
   1097         if size > lb:
   1098             try:
-> 1099                 new = dbapi_cursor.fetchmany(size - lb)
   1100             except BaseException as e:
   1101                 self.handle_exception(result, dbapi_cursor, e)

ProgrammingError: (psycopg2.ProgrammingError) named cursor isn't valid anymore
(Background on this error at: https://sqlalche.me/e/14/f405)

[mbercx]

Strange, I don't think @eimrek encountered this when running in his own environment with 2.0.3? Perhaps this was introduced in the fix for the QueryBuilder included in 2.0.4?

[unkcpz]

This is the first cell, I think it might frighten people away with such a big block of exceptions 🤨

[chrisjsewell]

What versions of packages do you have installed

[unkcpz]

I use the aiidalab server @mbercx prepared, and here is the list:

Package                       Version
----------------------------- -------------------
aiida-core                    2.0.4
aiida-pseudo                  0.7.0
aiida-quantumespresso         4.0.1
aio-pika                      6.8.1
aiormq                        3.3.1
alembic                       1.6.5
anyio                         3.2.0
archive-path                  0.4.1
argon2-cffi                   20.1.0
ase                           3.22.1
async-generator               1.10
attrs                         21.2.0
Babel                         2.9.1
backcall                      0.2.0
backports.functools-lru-cache 1.6.4
bcrypt                        3.2.2
bleach                        3.3.0
blinker                       1.4
brotlipy                      0.7.0
certifi                       2022.9.24
certipy                       0.1.3
cffi                          1.14.5
cftime                        1.6.2
chardet                       4.0.0
circus                        0.17.1
click                         8.1.3
click-config-file             0.6.0
click-spinner                 0.1.10
conda                         4.10.1
conda-package-handling        1.7.3
configobj                     5.0.6
cryptography                  3.4.7
cycler                        0.11.0
decorator                     5.0.9
defusedxml                    0.7.1
deprecation                   2.1.0
disk-objectstore              0.6.0
elementpath                   2.5.3
entrypoints                   0.3
Flask                         2.1.3
future                        0.18.2
graphviz                      0.20.1
greenlet                      1.1.0
idna                          2.10
importlib-metadata            4.5.0
importlib-resources           5.9.0
ipykernel                     5.5.5
ipython                       7.24.1
ipython-genutils              0.2.0
itsdangerous                  2.1.2
jedi                          0.18.0
Jinja2                        3.0.1
json5                         0.9.5
jsonschema                    3.2.0
jupyter-client                6.1.12
jupyter-core                  4.7.1
jupyter-server                1.8.0
jupyter-telemetry             0.1.0
jupyterhub                    1.4.1
jupyterlab                    3.0.16
jupyterlab-pygments           0.1.2
jupyterlab-server             2.6.0
kiwipy                        0.7.6
kiwisolver                    1.4.4
latexcodec                    2.0.1
loguru                        0.6.0
Mako                          1.1.4
mamba                         0.14.0
MarkupSafe                    2.0.1
matplotlib                    3.4.3
matplotlib-inline             0.1.2
mistune                       0.8.4
monty                         2022.9.9
mpmath                        1.2.1
multidict                     6.0.2
nbclassic                     0.3.1
nbclient                      0.5.3
nbconvert                     6.0.7
nbformat                      5.1.3
nest-asyncio                  1.5.1
netCDF4                       1.6.0
networkx                      2.8.6
notebook                      6.4.0
numpy                         1.21.4
oauthlib                      3.1.1
olefile                       0.46
packaging                     20.9
palettable                    3.3.0
pamela                        1.0.0
pamqp                         2.3.0
pandas                        1.5.0
pandocfilters                 1.4.2
paramiko                      2.11.0
parso                         0.8.2
pexpect                       4.8.0
pgsu                          0.2.2
pickleshare                   0.7.5
Pillow                        8.3.1
Pint                          0.16.1
pip                           22.0.4
plotly                        5.10.0
plumpy                        0.21.0
prometheus-client             0.11.0
prompt-toolkit                3.0.19
psutil                        5.9.2
psycopg2                      2.9.2
psycopg2-binary               2.9.2
ptyprocess                    0.7.0
pybtex                        0.24.0
PyCifRW                       4.4.3
pycosat                       0.6.3
pycparser                     2.20
pycurl                        7.43.0.6
Pygments                      2.9.0
PyJWT                         2.1.0
pymatgen                      2022.0.17
PyMySQL                       0.9.3
PyNaCl                        1.5.0
pyOpenSSL                     20.0.1
pyparsing                     2.4.7
pyrsistent                    0.17.3
PySocks                       1.7.1
python-dateutil               2.8.1
python-editor                 1.0.4
python-json-logger            2.0.1
pytray                        0.3.2
pytz                          2021.1
PyYAML                        5.4.1
pyzmq                         22.1.0
qe-tools                      2.0.0
requests                      2.25.1
ruamel.yaml                   0.17.9
ruamel.yaml.clib              0.2.2
ruamel-yaml-conda             0.15.80
scipy                         1.9.1
seekpath                      1.9.7
Send2Trash                    1.5.0
setuptools                    49.6.0.post20210108
shortuuid                     1.0.9
six                           1.16.0
sniffio                       1.2.0
spglib                        1.16.5
SQLAlchemy                    1.4.41
sympy                         1.11.1
tabulate                      0.8.10
tenacity                      8.1.0
terminado                     0.10.1
testpath                      0.5.0
tornado                       6.1
tqdm                          4.61.1
traitlets                     5.0.5
typing_extensions             4.3.0
uncertainties                 3.1.7
upf-to-json                   0.9.2
urllib3                       1.26.5
vtk                           9.1.0
wcwidth                       0.2.5
webencodings                  0.5.1
websocket-client              0.57.0
Werkzeug                      2.1.2
wheel                         0.36.2
wrapt                         1.11.2
xmlschema                     1.11.3
yarl                          1.7.2
zipp                          3.4.1

[chrisjsewell]

--> 133     for structure, in query.iterall():
    134         structure.set_extra('formula', structure.get_formula(mode='count'))

try changing this to somehting like:

structures = query.all()
for structure in structures:
     structure.set_extra('formula', structure.get_formula(mode='count'))

i.e. collect all the structures from the query first, then make changes to them

[unkcpz]

@chrisjsewell thanks that solving the issue, cheers! I add the commit to PR #436.

What is this issue caused? We should better add this to the documentation.

[chrisjsewell]

cheers, I guess we should open an issue on aiida-core for it also, since its obviously a change in behaviour to previous

[unkcpz]

cheers, I guess we should open an issue on aiida-core for it also, since its obviously a change in behaviour to previous

Agree, can you do it? I may not able to describe the issue precisely. Is that because the node entity is modified on the fly?

[chrisjsewell]

if you could open it please, then I can always append to/modify it later

[chrisjsewell]

Is that because the node entity is modified on the fly?

yes, iterall has an open connection, which is pulling data from the database, but then set_extra is writing to the database during this, and apparently closing the transaction, so then when iterall goes to pull more it can't.
If it worked before though, then obviously something has changed

[unkcpz]

This one can be closed? Since #436 merged with using all to replace iterall, and the issue opened on aiidateam/aiida-core#5672.

Feel free to reopen it if not proper.