querylog_* tables in MonetDBe

[dancromartie]

Transferring this from MonetDB/MonetDB#7240, as supposedly this issue is limited to the Python version.

Describe the bug

I'm not sure if this is intended to work in embedded Monet, but the "history" tables here don't seem to be populating.

Also, a section heading on that page has a typo: "queyrlog_history", if it impacts documentation search results

Tangential: it also hits the known issue where certain types don't play well with Python, so a select * will fail with KeyError: 6, even if there are no rows to show. At least in querylog_history, the id field seems to cause this issue. That's a little harder to work through than when doing a sum or something where you know which expression is the cause.

To Reproduce

import monetdbe

conn = monetdbe.connect("test.db")
conn.cursor().execute("CREATE TABLE IF NOT EXISTS foo (a TEXT, b TEXT)")
conn.commit()

print("Getting history rows")
# No rows
print("History DF:", conn.cursor().execute("SELECT * FROM sys.querylog_history").fetchdf(), "\n")

print("Getting calls rows")
# "select *" will cause an error, but start,stop are fine
print("Calls DF:", conn.cursor().execute("SELECT start, stop, arguments FROM sys.querylog_calls").fetchdf(), "\n")

print("Getting catalog rows")
# letting the KeyError happen now
print("Catalog DF:", conn.cursor().execute("SELECT * FROM sys.querylog_catalog").fetchdf(), "\n")

For me, the output is:

Getting history rows
History DF: Empty DataFrame
Columns: [id, owner, defined, query, pipe, plan, mal, optimize, start, stop, arguments, tuples, run, ship, cpu, io]
Index: [] 

Getting calls rows
Calls DF: Empty DataFrame
Columns: [start, stop, arguments]
Index: [] 

Getting catalog rows
Traceback (most recent call last):
  File "/home/dcromartie/dev/try-monet/history_example.py", line 17, in <module>
    print("Catalog DF:", conn.cursor().execute("SELECT * FROM sys.querylog_catalog").fetchdf(), "\n")
  File "/home/dcromartie/virtualenvs/trymonet/lib/python3.9/site-packages/monetdbe/cursors.py", line 155, in execute
    return self._execute_monetdbe(operation, parameters)
  File "/home/dcromartie/virtualenvs/trymonet/lib/python3.9/site-packages/monetdbe/cursors.py", line 143, in _execute_monetdbe
    self._set_description()
  File "/home/dcromartie/virtualenvs/trymonet/lib/python3.9/site-packages/monetdbe/cursors.py", line 51, in _set_description
    self.description = self.connection.get_description()
  File "/home/dcromartie/virtualenvs/trymonet/lib/python3.9/site-packages/monetdbe/connection.py", line 143, in get_description
    descriptions = list(zip(name, type_code, display_size, internal_size, precision, scale, null_ok))
  File "/home/dcromartie/virtualenvs/trymonet/lib/python3.9/site-packages/monetdbe/connection.py", line 137, in <genexpr>
    type_code = (monet_c_type_map[rcol.type].sql_type for rcol in columns)
KeyError: 6

Expected behavior

1 or more rows returned

Software versions

• monetdbe version 0.11 installed from pypi

**Issue labeling **

Sorry, I'm not sure if I can add my own labels or not. Let me know if I'm missing the feature. This is low urgency, and I'm completely playing around.

[njnes]

indeed the id column seems the problem, ie type oid isn't supported by the current monetdbe.
Not sure yet why querylog isn't working (even after call sys.querylog_enable();)

[njnes]

solved type oid in default

[njnes]

all statements in the monetdbe context are executed via prepare/execute, these are not caught by the querylog.