Mathesar fails to open a database where pgmemento is installed

[dv01sw]

Repro:

• Create a PG and install and activate pgmemento on it.
• Connect to the db using mathesar.
• While installing the schemas there is a failure, because the drop_table event trigger of pgmemento is triggered at a moment where something is referencing mathesar_types, but mathesar_types is not present yet.

Solution:

• I guess these schemas should be installed in an order that ensures even between steps all references are valid.

[zackkrida]

Thanks for the report @dv01sw. We want to make sure Mathesar works with auditing tools like pgMemento, especially considering we don't offer our own auditing functionality and that our unofficial "it's just Postgres" philosophy means we should work with all extensions.

I was able to replicate this and get the following error:

InvalidSchemaName: schema "'mathesar_types" does not exist CONTEXT: SQL statement "SELECT table_ident::regclass" PL/pgSQL function pgmemento.split_table_from_query(text) line 31 at PERFORM SQL statement "SELECT query, audit_table_name, audit_schema_name, audit_id_column_name, audit_old_data FROM pgmemento.split_table_from_query(ddl_text)" PL/pgSQL function pgmemento.table_drop_pre_trigger() line 25 at SQL statement

[-30149] InvalidSchemaName: schema "'mathesar_types" does not exist
CONTEXT:  SQL statement "SELECT table_ident::regclass"
PL/pgSQL function pgmemento.split_table_from_query(text) line 31 at PERFORM
SQL statement "SELECT
    query,
    audit_table_name,
    audit_schema_name,
    audit_id_column_name,
    audit_old_data
    FROM pgmemento.split_table_from_query(ddl_text)"
PL/pgSQL function pgmemento.table_drop_pre_trigger() line 25 at SQL statement

Traceback (most recent call last):
  File "/code/mathesar/rpc/exceptions/handlers.py", line 16, in safe_func
    return f(*args, **kwargs)
  File "/code/mathesar/rpc/databases/setup.py", line 108, in connect_existing
    result = permissions.set_up_preexisting_database_for_user(
  File "/usr/local/lib/python3.9/contextlib.py", line 79, in inner
    return func(*args, **kwds)
  File "/code/mathesar/utils/permissions.py", line 83, in set_up_preexisting_database_for_user
    user_database_role.database.install_sql(
  File "/code/mathesar/models/base.py", line 69, in install_sql
    install(conn)
  File "/code/db/sql/install.py", line 36, in install
    step(conn)
  File "/code/db/sql/install.py", line 16, in _install
    load_file_with_conn(conn, file_handle)
  File "/code/db/connection.py", line 60, in load_file_with_conn
    conn.execute(file_handle.read())
  File "/usr/local/lib/python3.9/site-packages/psycopg/connection.py", line 251, in execute
    raise ex.with_traceback(None)
psycopg.errors.InvalidSchemaName: schema "'mathesar_types" does not exist

CONTEXT:  SQL statement "SELECT table_ident::regclass"
PL/pgSQL function pgmemento.split_table_from_query(text) line 31 at PERFORM
SQL statement "SELECT
    query,
    audit_table_name,
    audit_schema_name,
    audit_id_column_name,
    audit_old_data
    FROM pgmemento.split_table_from_query(ddl_text)"
PL/pgSQL function pgmemento.table_drop_pre_trigger() line 25 at SQL statement

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/modernrpc/handlers/jsonhandler.py", line 182, in process_single_request
    result_data = _method.execute(context, args, kwargs)
  File "/usr/local/lib/python3.9/site-packages/modernrpc/core.py", line 143, in execute
    return self.function(*args, **kwargs)
  File "/code/mathesar/analytics.py", line 43, in wrapped
    return f(*args, **kwargs)
  File "/code/mathesar/rpc/exceptions/handlers.py", line 18, in safe_func
    _raise_generic_error(e)
  File "/code/mathesar/rpc/exceptions/handlers.py", line 25, in _raise_generic_error
    raise RPCException(err_code, e.__class__.__name__ + ": " + str(e))
modernrpc.exceptions.RPCException: [-30149] InvalidSchemaName: schema "'mathesar_types" does not exist

I'm doing a deeper dive into the issue now and I'm hoping to have some answers.

[zackkrida]

This is super interesting. @mathemancer, the culprit here seems to be this line in 00_msar_all_objects_table.sql:

DROP TABLE IF EXISTS msar.all_mathesar_objects;

Somehow, that's producing the pgmemento error above. If I comment out that line, and add CASCADE to the schema dropping code:

diff --git a/db/sql/02_msar_remove.sql b/db/sql/02_msar_remove.sql
index 547122673..c5b92a833 100644
--- a/db/sql/02_msar_remove.sql
+++ b/db/sql/02_msar_remove.sql
@@ -73,7 +73,7 @@ BEGIN
       LOOP
         IF remove_custom_types OR sch <> 'mathesar_types' THEN
           RAISE NOTICE 'Dropping Schema %', sch;
-          EXECUTE(format('DROP SCHEMA IF EXISTS %I', sch));
+          EXECUTE(format('DROP SCHEMA IF EXISTS %I CASCADE', sch));
         END IF;
       END LOOP;
   END IF;

The error is resolved.

I found this through very arbitrary trial-and-error, manually running the different installation sql files, commenting them out and trying to install, and so on.

[zackkrida]

And also, for anyone testing this: I used this very useful repo to run a postgres instance with pgmemento installed locally. I just did:

gh repo clone stephane-klein/pgmemento-playground
cd pgmemento-playground
docker-compose up

and then after logging in with psql -h localhost -p 6000 -U postgres:

CREATE EXTENSION pgmemento;
-- Enable pgmemento on the public schema
SELECT pgmemento.init('public');

Then I connected the DB to Mathesar.

[mathemancer]

1. This appears to be a bug with pgMemento: it's mangling the name of the schema mathesar_types, and also seems not to understand that dropping a namespaced table with IF EXISTS should not produce an error even if the namespace doesn't exist.
2. pgMemento project appears to be dead project.

We should not spend any more time on this.

[zackkrida]

Hi @dv01sw this definitely appears to be a bug in pgMemento, unfortunately. To move forward, I suspect you could experiment with pausing pgMemento before running Mathesar's install, and then resuming pgMemento after installation is complete.

You could also go create an issue at https://github.com/pgMemento/pgMemento but it doesn't appear the project has seen an update in over 2 years.