Error Using dbtoyaml With Supabase Schema

[benjamin-kirkbride]

I am wanting to use Pyrseas with Supabase (basically pre-configured Postgrest). I get the following error:

$ dbtoyaml -H db.nynstytcyprpinthzhos.supabase.co -U postgres --password postgres
Password: 
Traceback (most recent call last):
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/bin/dbtoyaml", line 8, in <module>
    sys.exit(main())
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/database.py", line 483, in to_map
    dbmap.update(self.db.eventtrigs.to_map(self.db, opts))
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/dbobject/__init__.py", line 662, in to_map
    objmap = obj.to_map(db, opts.no_owner, opts.no_privs)
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/dbobject/eventtrig.py", line 90, in to_map
    dct = super(EventTrigger, self).to_map(db, no_owner)
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/dbobject/__init__.py", line 361, in to_map
    deps -= self.get_implied_deps(db)
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/dbobject/eventtrig.py", line 113, in get_implied_deps
    deps.add(db.functions[(sch, fnc, '')])
KeyError: ('pg_catalog', 'set_graphql_placeholder', '')

To reproduce:

1. Make account on Supabase
2. Make a test project (free)
3. Go to Settings -> Database -> Connection Info
4. Use dbtoyaml with said info.

It appears this is the same issue as #236.

[jmafc]

My first reaction is that since #236 was in essence fixed in the master branch, you should try reproducing the problem using a head version to verify if that solves it.

[benjamin-kirkbride]

It appears to have the same result:

dbtoyaml -H db.nynstytcyprpinthzhos.supabase.co -U postgres --password postgres
Password: 
Traceback (most recent call last):
  File "/home/bkirkbride/projects/Pyrseas/.venv/bin/dbtoyaml", line 33, in <module>
    sys.exit(load_entry_point('Pyrseas==0.9.1', 'console_scripts', 'dbtoyaml')())
  File "/home/bkirkbride/projects/Pyrseas/.venv/lib/python3.10/site-packages/Pyrseas-0.9.1-py3.10.egg/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/home/bkirkbride/projects/Pyrseas/.venv/lib/python3.10/site-packages/Pyrseas-0.9.1-py3.10.egg/pyrseas/database.py", line 483, in to_map
    dbmap.update(self.db.eventtrigs.to_map(self.db, opts))
  File "/home/bkirkbride/projects/Pyrseas/.venv/lib/python3.10/site-packages/Pyrseas-0.9.1-py3.10.egg/pyrseas/dbobject/__init__.py", line 662, in to_map
    objmap = obj.to_map(db, opts.no_owner, opts.no_privs)
  File "/home/bkirkbride/projects/Pyrseas/.venv/lib/python3.10/site-packages/Pyrseas-0.9.1-py3.10.egg/pyrseas/dbobject/eventtrig.py", line 90, in to_map
    dct = super(EventTrigger, self).to_map(db, no_owner)
  File "/home/bkirkbride/projects/Pyrseas/.venv/lib/python3.10/site-packages/Pyrseas-0.9.1-py3.10.egg/pyrseas/dbobject/__init__.py", line 361, in to_map
    deps -= self.get_implied_deps(db)
  File "/home/bkirkbride/projects/Pyrseas/.venv/lib/python3.10/site-packages/Pyrseas-0.9.1-py3.10.egg/pyrseas/dbobject/eventtrig.py", line 113, in get_implied_deps
    deps.add(db.functions[(sch, fnc, '')])
KeyError: ('pg_catalog', 'set_graphql_placeholder', '')

[benjamin-kirkbride]

So interestingly enough, it appears that most (all?) other extensions are working. I will investigate the set_graphql_placeholder object further and see what I can find.

$ python -m pdb -c continue .venv/bin/dbtoyaml -H db.nynstytcyprpinthzhos.supabase.co -U postgres --password postgres
Password: 
Traceback (most recent call last):
  File "/usr/lib/python3.10/pdb.py", line 1726, in main
    pdb._runscript(mainpyfile)
  File "/usr/lib/python3.10/pdb.py", line 1586, in _runscript
    self.run(statement)
  File "/usr/lib/python3.10/bdb.py", line 597, in run
    exec(cmd, globals, locals)
  File "<string>", line 1, in <module>
  File "/home/bkirkbride/projects/Pyrseas/.venv/bin/dbtoyaml", line 33, in <module>
    sys.exit(load_entry_point('Pyrseas', 'console_scripts', 'dbtoyaml')())
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/database.py", line 483, in to_map
    dbmap.update(self.db.eventtrigs.to_map(self.db, opts))
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/__init__.py", line 662, in to_map
    objmap = obj.to_map(db, opts.no_owner, opts.no_privs)
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/eventtrig.py", line 90, in to_map
    dct = super(EventTrigger, self).to_map(db, no_owner)
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/__init__.py", line 361, in to_map
    deps -= self.get_implied_deps(db)
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/eventtrig.py", line 113, in get_implied_deps
    deps.add(db.functions[(sch, fnc, '')])
KeyError: ('pg_catalog', 'set_graphql_placeholder', '')
Uncaught exception. Entering post mortem debugging
Running 'cont' or 'step' will restart the program
> /home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/eventtrig.py(113)get_implied_deps()
-> deps.add(db.functions[(sch, fnc, '')])
(Pdb) sch
'pg_catalog'
(Pdb) fnc
'set_graphql_placeholder'
(Pdb) db.functions
{('auth', 'email', ''): <function email() at 0x7fe8ac15b4f0>, ('auth', 'jwt', ''): <function jwt() at 0x7fe8ac15b460>, ('auth', 'role', ''): <function role() at 0x7fe8ac15b370>, ('auth', 'uid', ''): <function uid() at 0x7fe8ac15b0a0>, ('extensions', 'grant_pg_cron_access', ''): <function grant_pg_cron_access() at 0x7fe8ac15b400>, ('extensions', 'grant_pg_graphql_access', ''): <function grant_pg_graphql_access() at 0x7fe8ac15b550>, ('extensions', 'grant_pg_net_access', ''): <function grant_pg_net_access() at 0x7fe8ac15b580>, ('extensions', 'pgrst_ddl_watch', ''): <function pgrst_ddl_watch() at 0x7fe8ac15aec0>, ('extensions', 'pgrst_drop_watch', ''): <function pgrst_drop_watch() at 0x7fe8ac15b4c0>, ('extensions', 'set_graphql_placeholder', ''): <function set_graphql_placeholder() at 0x7fe8ac15aef0>, ('pgbouncer', 'get_auth', 'p_usename text'): <function get_auth(p_usename text) at 0x7fe8ac15b5e0>, ('storage', 'extension', 'name text'): <function extension(name text) at 0x7fe8ac15b5b0>, ('storage', 'filename', 'name text'): <function filename(name text) at 0x7fe8ac159c00>, ('storage', 'foldername', 'name text'): <function foldername(name text) at 0x7fe8ac15ac20>, ('storage', 'get_size_by_bucket', ''): <function get_size_by_bucket() at 0x7fe8ac15b760>, ('storage', 'search', 'prefix text, bucketname text, limits integer, levels integer, offsets integer, search text, sortcolumn text, sortorder text'): <function search(prefix text, bucketname text, limits integer, levels integer, offsets integer, search text, sortcolumn text, sortorder text) at 0x7fe8ac15b730>, ('storage', 'update_updated_at_column', ''): <function update_updated_at_column() at 0x7fe8ac15b6a0>, ('supabase_functions', 'http_request', ''): <function http_request() at 0x7fe8ac15b2b0>}

[benjamin-kirkbride]

Something I just noticed, db.functions already contains set_graphql_placeholder, but under the extensions schema, not pg_catalog. Seems like that is the actual issue here. Let me see if I can figure out why Pyrseas is trying to refer to it from pg_catalog.

This is the schema definition for set_graphql_placeholder.

CREATE FUNCTION extensions.set_graphql_placeholder() RETURNS event_trigger
    LANGUAGE plpgsql
    AS $_$
    DECLARE
    graphql_is_dropped bool;
    BEGIN
    graphql_is_dropped = (
        SELECT ev.schema_name = 'graphql_public'
        FROM pg_event_trigger_dropped_objects() AS ev
        WHERE ev.schema_name = 'graphql_public'
    );

    IF graphql_is_dropped
    THEN
        create or replace function graphql_public.graphql(
            "operationName" text default null,
            query text default null,
            variables jsonb default null,
            extensions jsonb default null
        )
            returns jsonb
            language plpgsql
        as $$
            DECLARE
                server_version float;
            BEGIN
                server_version = (SELECT (SPLIT_PART((select version()), ' ', 2))::float);

                IF server_version >= 14 THEN
                    RETURN jsonb_build_object(
                        'errors', jsonb_build_array(
                            jsonb_build_object(
                                'message', 'pg_graphql extension is not enabled.'
                            )
                        )
                    );
                ELSE
                    RETURN jsonb_build_object(
                        'errors', jsonb_build_array(
                            jsonb_build_object(
                                'message', 'pg_graphql is only available on projects running Postgres 14 onwards.'
                            )
                        )
                    );
                END IF;
            END;
        $$;
    END IF;

    END;
$_$;

[benjamin-kirkbride]

Okay, upon further digging, this specific error takes place in the EventTrigger class. The above function is part of an event trigger:

--
-- Name: issue_graphql_placeholder; Type: EVENT TRIGGER; Schema: -; Owner: supabase_admin
--

CREATE EVENT TRIGGER issue_graphql_placeholder ON sql_drop
         WHEN TAG IN ('DROP EXTENSION')
   EXECUTE FUNCTION extensions.set_graphql_placeholder();

Is it possible that whatever was done to fix #236 missed event triggers specifically?

[jmafc]

Actually, I was wrong in thinking that the fix for the problems with extensions were in master. They were only r0.9. I've merged them into master now. Please try your test after pulling from master. The fix did not include changes to dbobject/eventtrig.py so if your test still fails we'll have to look at the EventTrigger.query in that file.

[benjamin-kirkbride]

Okay, some progress.

Traceback (most recent call last):
  File "/usr/lib/python3.10/pdb.py", line 1726, in main
    pdb._runscript(mainpyfile)
  File "/usr/lib/python3.10/pdb.py", line 1586, in _runscript
    self.run(statement)
  File "/usr/lib/python3.10/bdb.py", line 597, in run
    exec(cmd, globals, locals)
  File "<string>", line 1, in <module>
  File "/home/bkirkbride/projects/Pyrseas/.venv/bin/dbtoyaml", line 33, in <module>
    sys.exit(load_entry_point('Pyrseas', 'console_scripts', 'dbtoyaml')())
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/database.py", line 443, in to_map
    self.from_catalog(True)
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/database.py", line 353, in from_catalog
    self._link_refs(self.db)
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/database.py", line 222, in _link_refs
    db.schemas.link_refs(db, copycfg)
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/schema.py", line 321, in link_refs
    link_one(targ, type_, keys, 'views')
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/schema.py", line 294, in link_one
    schema = self[objkeys[0]]
KeyError: 'graphql'

objkeys = ('graphql', 'enum_value')

[benjamin-kirkbride]

Here are the values of the arguments to link_one:

targdict = {('auth', 'audit_log_entries'): <table audit_log_entries at 0x7f2ebf94d030>, ('auth', 'identities'): <table identities at 0x7f2ebf94cf40>, ('auth', 'instances'): <table instances at 0x7f2ebf94d150>, ('auth', 'refresh_tokens'): <table refresh_tokens at 0x7f2ebf94d4b0>, ('auth', 'schema_migrations'): <table schema_migrations at 0x7f2ebf94cca0>, ('auth', 'users'): <table users at 0x7f2ebf94d240>, ('public', 'lmao'): <table lmao at 0x7f2ebf94d270>, ('public', 'todos'): <table todos at 0x7f2ebf94d0c0>, ('storage', 'buckets'): <table buckets at 0x7f2ebf94d090>, ('storage', 'migrations'): <table migrations at 0x7f2ebf94d300>, ('storage', 'objects'): <table objects at 0x7f2ebf94d330>, ('supabase_functions', 'hooks'): <table hooks at 0x7f2ebf94d1e0>, ('supabase_functions', 'migrations'): <table migrations at 0x7f2ebf94cf70>, ('auth', 'refresh_tokens_id_seq'): <sequence refresh_tokens_id_seq at 0x7f2ebf94d390>, ('public', 'lmao_id_seq'): <sequence lmao_id_seq at 0x7f2ebf94c8b0>, ('public', 'todos_id_seq'): <sequence todos_id_seq at 0x7f2ebf94d210>, ('supabase_functions', 'hooks_id_seq'): <sequence hooks_id_seq at 0x7f2ebf94d2a0>, ('extensions', 'pg_stat_statements'): <view pg_stat_statements at 0x7f2ebf94cd00>, ('extensions', 'pg_stat_statements_info'): <view pg_stat_statements_info at 0x7f2ebf94d2d0>, ('graphql', 'enum_value'): <view enum_value at 0x7f2ebf94d510>, ('graphql', 'field'): <view field at 0x7f2ebf94d4e0>, ('graphql', 'type'): <view type at 0x7f2ebf94d3c0>, ('pgsodium', 'masking_rule'): <view masking_rule at 0x7f2ebf94d600>, ('pgsodium', 'valid_key'): <view valid_key at 0x7f2ebf94d930>, ('graphql', 'entity'): <materialized view entity at 0x7f2ebf94d6f0>, ('graphql', 'entity_column'): <materialized view entity_column at 0x7f2ebf94d120>, ('graphql', 'entity_unique_columns'): <materialized view entity_unique_columns at 0x7f2ebf94d720>, ('graphql', 'relationship'): <materialized view relationship at 0x7f2ebf94d570>}
objtype = 'tables'
objkeys = ('graphql', 'enum_value')
subtype = 'views'

[benjamin-kirkbride]

Here is the value of self:

{'auth': <schema auth at 0x7f2ebf94cb20>, 'extensions': <schema extensions at 0x7f2ebf94c820>, 'graphql_public': <schema graphql_public at 0x7f2ebf94cc10>, 'pg_catalog': <schema pg_catalog at 0x7f2ebf94cdc0>, 'pgbouncer': <schema pgbouncer at 0x7f2ebf94d000>, 'pgsodium': <schema pgsodium at 0x7f2ebf94c220>, 'public': <schema public at 0x7f2ebf94cac0>, 'realtime': <schema realtime at 0x7f2ebf94cdf0>, 'storage': <schema storage at 0x7f2ebf94cc40>, 'supabase_functions': <schema supabase_functions at 0x7f2ebf94ce80>}

[jmafc]

You may want to execute (from psql) the query that appears in dbtype.py Enum.query() and see what rows are returned.

[jmafc]

But actually, if graphql is an enum_value it may be stored in some other catalog.

[benjamin-kirkbride]

You may want to execute (from psql) the query that appears in dbtype.py Enum.query()

I'm not seeing that in the stack trace :/ can you give some more detail on what you mean by that?

[benjamin-kirkbride]

I added a breakpoint() to the init of Enum in dbtype.py and it never hits, so it seems like it is never getting to that point?

[jmafc]

The query is in

Pyrseas/pyrseas/dbobject/dbtype.py

Line 375 in f06c80a

SELECT nspname AS schema, typname AS name, rolname AS owner,

Sorry for being terse. Whenever I refer to a file by name only, you can expect it's in pyrseas/dbobject. The flow of dttoyaml is driven by what may be called the "collection" types (usually named <ObjectType>Dict), in this case, TypeDict in its _from_catalog() method, which invokes `self.fetch() and ends up executing the query in question.