Error Using dbtoyaml With Supabase Schema #244

benjamin-kirkbride · 2022-08-20T17:43:03Z

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:

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

It appears this is the same issue as #236.

The text was updated successfully, but these errors were encountered:

jmafc · 2022-08-20T21:21:20Z

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 · 2022-08-21T04:10:10Z

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 · 2022-08-21T18:28:20Z

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 · 2022-08-21T18:42:37Z

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 · 2022-08-21T20:37:21Z

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 · 2022-08-21T21:41:36Z

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 · 2022-08-22T23:35:52Z

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 · 2022-08-23T00:04:16Z

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 · 2022-08-23T00:05:50Z

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 · 2022-08-23T00:09:32Z

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

jmafc · 2022-08-23T00:11:25Z

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

benjamin-kirkbride · 2022-08-23T00:32:28Z

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 · 2022-08-23T00:35:44Z

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 · 2022-08-23T00:45:27Z

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.

benjamin-kirkbride · 2022-08-23T00:47:54Z

Gotcha, sorry didn't understand what you meant by that.

No rows returned

Not sure what that implies.

jmafc · 2022-08-23T01:01:51Z

Looking at the query, I see that it is supposed to fetch the enum values (see the ARRAY(SELECT ...). You may want to experiment with the query, e.g., by excluding the last AND clause to see if it returns something or start from the other end, e.g., by selecting from pg_enum and/or pg_type.

benjamin-kirkbride · 2022-08-23T01:02:12Z

Okay, if I remove the AND t.oid NOT IN (SELECT objid FROM pg_depend WHERE deptype = 'e' AND classid = 'pg_type'::regclass) then it returns:

but I appear to get a similar (?) error:

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 309, in link_refs
    link_one(targ, 'types', keys)
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/schema.py", line 294, in link_one
    schema = self[objkeys[0]]
KeyError: 'graphql'
Uncaught exception. Entering post mortem debugging
Running 'cont' or 'step' will restart the program
> /home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/schema.py(294)link_one()
-> schema = self[objkeys[0]]
(Pdb) a
targdict = {('graphql', 'cardinality'): <type cardinality at 0x7f40933f1030>, ('graphql', 'column_order_direction'): <type column_order_direction at 0x7f40933f0f40>, ('graphql', 'comparison_op'): <type comparison_op at 0x7f40933f1150>, ('graphql', 'field_meta_kind'): <type field_meta_kind at 0x7f40933f14b0>, ('graphql', 'meta_kind'): <type meta_kind at 0x7f40933f0ca0>, ('graphql', 'type_kind'): <type type_kind at 0x7f40933f1240>, ('net', 'request_status'): <type request_status at 0x7f40933f1270>, ('pgsodium', 'key_status'): <type key_status at 0x7f40933f10c0>, ('pgsodium', 'key_type'): <type key_type at 0x7f40933f1090>}
objtype = 'types'
objkeys = ('graphql', 'cardinality')
subtype = None

jmafc · 2022-08-23T01:20:29Z

I'm afraid that the only way to debug this will be to install whatever graphql library is used in supabase on a local db, like I did with pg_cron before. A web search returned "Six Easy Ways to add a GraphQL API to your Postgres Database" in first place, but further down I saw a supabase.com entry referring to pg_graphql, so I presume that's what's installed. I'm sorry but I don't know how soon I'll be able to take a look at that.

jmafc · 2022-08-23T01:22:35Z

BTW, are you using GraphQL? And if not, does Supabase have a facility to not include it in you database?

benjamin-kirkbride · 2022-08-23T01:38:01Z

BTW, are you using GraphQL? And if not, does Supabase have a facility to not include it in you database?

I am not, though I'll admit my goal here is to make this work out-of-the-box with Supabase.

I disabled GraphQL and it gets a bit further:

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 223, in _link_refs
    db.tables.link_refs(db.columns, db.constraints, db.indexes, db.rules,
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/table.py", line 966, in link_refs
    assert self[(sch, tbl)]
KeyError: ('pgsodium', 'key')
Uncaught exception. Entering post mortem debugging
Running 'cont' or 'step' will restart the program
> /home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/table.py(966)link_refs()
-> assert self[(sch, tbl)]
(Pdb) a
self = {('auth', 'audit_log_entries'): <table audit_log_entries at 0x7f89ba775060>, ('auth', 'identities'): <table identities at 0x7f89ba775030>, ('auth', 'instances'): <table instances at 0x7f89ba774f40>, ('auth', 'refresh_tokens'): <table refresh_tokens at 0x7f89ba775150>, ('auth', 'schema_migrations'): <table schema_migrations at 0x7f89ba7754b0>, ('auth', 'users'): <table users at 0x7f89ba774ca0>, ('public', 'lmao'): <table lmao at 0x7f89ba775240>, ('public', 'todos'): <table todos at 0x7f89ba775270>, ('storage', 'buckets'): <table buckets at 0x7f89ba7750c0>, ('storage', 'migrations'): <table migrations at 0x7f89ba775090>, ('storage', 'objects'): <table objects at 0x7f89ba775300>, ('supabase_functions', 'hooks'): <table hooks at 0x7f89ba775330>, ('supabase_functions', 'migrations'): <table migrations at 0x7f89ba7751e0>, ('auth', 'refresh_tokens_id_seq'): <sequence refresh_tokens_id_seq at 0x7f89ba775360>, ('public', 'lmao_id_seq'): <sequence lmao_id_seq at 0x7f89ba774e50>, ('public', 'todos_id_seq'): <sequence todos_id_seq at 0x7f89ba775390>, ('supabase_functions', 'hooks_id_seq'): <sequence hooks_id_seq at 0x7f89ba775210>, ('extensions', 'pg_stat_statements'): <view pg_stat_statements at 0x7f89ba7752d0>, ('extensions', 'pg_stat_statements_info'): <view pg_stat_statements_info at 0x7f89ba775180>, ('pgsodium', 'masking_rule'): <view masking_rule at 0x7f89ba7755a0>, ('pgsodium', 'valid_key'): <view valid_key at 0x7f89ba775510>}
dbcolumns = {('auth', 'audit_log_entries'): [<column instance_id at 0x7f89ba7754e0>, <column id at 0x7f89ba7753c0>, <column payload at 0x7f89ba775600>, <column created_at at 0x7f89ba775930>, <column ip_address at 0x7f89ba775120>], ('auth', 'identities'): [<column id at 0x7f89ba7756f0>, <column user_id at 0x7f89ba775720>, <column identity_data at 0x7f89ba775570>, <column provider at 0x7f89ba775540>, <column last_sign_in_at at 0x7f89ba7757b0>, <column created_at at 0x7f89ba7757e0>, <column updated_at at 0x7f89ba775690>], ('auth', 'instances'): [<column id at 0x7f89ba7753f0>, <column uuid at 0x7f89ba775810>, <column raw_base_config at 0x7f89ba775840>, <column created_at at 0x7f89ba7756c0>, <column updated_at at 0x7f89ba775750>], ('auth', 'refresh_tokens'): [<column instance_id at 0x7f89ba7758d0>, <column id at 0x7f89ba775900>, <column token at 0x7f89ba775630>, <column user_id at 0x7f89ba775780>, <column revoked at 0x7f89ba7751b0>, <column created_at at 0x7f89ba775a20>, <column updated_at at 0x7f89ba7759f0>, <column parent at 0x7f89ba775960>], ('auth', 'schema_migrations'): [<column version at 0x7f89ba775ae0>], ('auth', 'users'): [<column instance_id at 0x7f89ba775de0>, <column id at 0x7f89ba7759c0>, <column aud at 0x7f89bbc0b8e0>, <column role at 0x7f89bbc0b910>, <column email at 0x7f89bbe9a260>, <column encrypted_password at 0x7f89bbe9b130>, <column email_confirmed_at at 0x7f89bbe9ad40>, <column invited_at at 0x7f89ba7746a0>, <column confirmation_token at 0x7f89ba7744c0>, <column confirmation_sent_at at 0x7f89ba7746d0>, <column recovery_token at 0x7f89ba7745e0>, <column recovery_sent_at at 0x7f89ba7742b0>, <column email_change_token_new at 0x7f89ba774610>, <column email_change at 0x7f89ba774550>, <column email_change_sent_at at 0x7f89ba774310>, <column last_sign_in_at at 0x7f89ba7743a0>, <column raw_app_meta_data at 0x7f89ba774520>, <column raw_user_meta_data at 0x7f89ba774430>, <column is_super_admin at 0x7f89ba7742e0>, <column created_at at 0x7f89ba7740a0>, <column updated_at at 0x7f89ba774700>, <column phone at 0x7f89ba774040>, <column phone_confirmed_at at 0x7f89ba774340>, <column phone_change at 0x7f89ba775660>, <column phone_change_token at 0x7f89ba7741f0>, <column phone_change_sent_at at 0x7f89ba774160>, <column confirmed_at at 0x7f89ba775b70>, <column email_change_token_current at 0x7f89ba775ba0>, <column email_change_confirm_status at 0x7f89ba775a80>, <column banned_until at 0x7f89ba775a50>, <column reauthentication_token at 0x7f89ba775c30>, <column reauthentication_sent_at at 0x7f89ba775c60>], ('extensions', 'pg_stat_statements'): [<column userid at 0x7f89ba775b10>, <column dbid at 0x7f89ba7755d0>, <column toplevel at 0x7f89ba775c90>, <column queryid at 0x7f89ba775cc0>, <column query at 0x7f89ba775b40>, <column plans at 0x7f89ba775bd0>, <column total_plan_time at 0x7f89ba775d50>, <column min_plan_time at 0x7f89ba775d80>, <column max_plan_time at 0x7f89ba775870>, <column mean_plan_time at 0x7f89ba775c00>, <column stddev_plan_time at 0x7f89ba775e10>, <column calls at 0x7f89ba775e40>, <column total_exec_time at 0x7f89ba7758a0>, <column min_exec_time at 0x7f89ba775cf0>, <column max_exec_time at 0x7f89ba775ed0>, <column mean_exec_time at 0x7f89ba775f00>, <column stddev_exec_time at 0x7f89ba775db0>, <column rows at 0x7f89ba775d20>, <column shared_blks_hit at 0x7f89ba775990>, <column shared_blks_read at 0x7f89ba776020>, <column shared_blks_dirtied at 0x7f89ba775f90>, <column shared_blks_written at 0x7f89ba775f60>, <column local_blks_hit at 0x7f89ba775e70>, <column local_blks_read at 0x7f89ba776080>, <column local_blks_dirtied at 0x7f89ba7778e0>, <column local_blks_written at 0x7f89ba775f30>, <column temp_blks_read at 0x7f89ba776170>, <column temp_blks_written at 0x7f89ba7761a0>, <column blk_read_time at 0x7f89ba775ff0>, <column blk_write_time at 0x7f89ba775fc0>, <column wal_records at 0x7f89ba776230>, <column wal_fpi at 0x7f89ba776260>, <column wal_bytes at 0x7f89ba776860>], ('extensions', 'pg_stat_statements_info'): [<column dealloc at 0x7f89ba775ea0>, <column stats_reset at 0x7f89ba7762f0>], ('net', '_http_response'): [<column id at 0x7f89ba776320>, <column status_code at 0x7f89ba7760b0>, <column content_type at 0x7f89ba776140>, <column headers at 0x7f89ba7763b0>, <column content at 0x7f89ba7763e0>, <column timed_out at 0x7f89ba776290>, <column error_msg at 0x7f89ba776200>], ('net', 'http_request_queue'): [<column id at 0x7f89ba776410>, <column method at 0x7f89ba776440>, <column url at 0x7f89ba7762c0>, <column headers at 0x7f89ba776350>, <column body at 0x7f89ba7764d0>, <column timeout_milliseconds at 0x7f89ba776500>, <column created at 0x7f89ba7760e0>], ('net', 'http_response'): [<column status_code at 0x7f89ba776380>, <column headers at 0x7f89ba776590>, <column body at 0x7f89ba7765c0>], ('net', 'http_response_result'): [<column status at 0x7f89ba776050>, <column message at 0x7f89ba776470>, <column response at 0x7f89ba776650>], ('pgsodium', '_key_id_context'): [<column key_id at 0x7f89ba776680>, <column key_context at 0x7f89ba776530>], ('pgsodium', 'crypto_box_keypair'): [<column public at 0x7f89ba7764a0>, <column secret at 0x7f89ba776710>], ('pgsodium', 'crypto_kx_keypair'): [<column public at 0x7f89ba776740>, <column secret at 0x7f89ba7761d0>], ('pgsodium', 'crypto_kx_session'): [<column rx at 0x7f89ba7765f0>, <column tx at 0x7f89ba7767d0>], ('pgsodium', 'crypto_sign_keypair'): [<column public at 0x7f89ba776800>, <column secret at 0x7f89ba7766b0>], ('pgsodium', 'crypto_signcrypt_keypair'): [<column public at 0x7f89ba776620>, <column secret at 0x7f89ba776890>], ('pgsodium', 'crypto_signcrypt_state_key'): [<column state at 0x7f89ba7768c0>, <column shared_key at 0x7f89ba776560>], ('pgsodium', 'key'): [<column id at 0x7f89ba776770>, <column status at 0x7f89ba776950>, <column created at 0x7f89ba776980>, <column expires at 0x7f89ba776830>, <column key_type at 0x7f89ba7767a0>, <column key_id at 0x7f89ba776110>, <column key_context at 0x7f89ba776aa0>, <column comment at 0x7f89ba776a10>, <column user_data at 0x7f89ba7769e0>], ('pgsodium', 'masking_rule'): [<column attrelid at 0x7f89ba7768f0>, <column attnum at 0x7f89ba776b00>, <column relnamespace at 0x7f89ba777160>, <column relname at 0x7f89ba7769b0>, <column attname at 0x7f89ba776bf0>, <column format_type at 0x7f89ba776c20>, <column col_description at 0x7f89ba776a70>, <column key_id_column at 0x7f89ba776a40>, <column key_id at 0x7f89ba776cb0>, <column associated_column at 0x7f89ba776ce0>, <column nonce_column at 0x7f89ba776bc0>, <column priority at 0x7f89ba776920>], ('pgsodium', 'valid_key'): [<column id at 0x7f89ba776d10>, <column status at 0x7f89ba776d40>, <column created at 0x7f89ba776b90>, <column expires at 0x7f89ba776c50>, <column key_type at 0x7f89ba776dd0>, <column key_id at 0x7f89ba776e00>, <column key_context at 0x7f89ba776b30>, <column comment at 0x7f89ba776c80>, <column user_data at 0x7f89ba776e90>], ('public', 'lmao'): [<column id at 0x7f89ba776ec0>, <column created_at at 0x7f89ba776b60>, <column yo at 0x7f89ba776d70>], ('public', 'todos'): [<column id at 0x7f89ba776f50>, <column user_id at 0x7f89ba776f80>, <column task at 0x7f89ba776e30>, <column is_complete at 0x7f89ba776da0>, <column inserted_at at 0x7f89ba777010>], ('storage', 'buckets'): [<column id at 0x7f89ba777040>, <column name at 0x7f89ba776ad0>, <column owner at 0x7f89ba776ef0>, <column created_at at 0x7f89ba7770d0>, <column updated_at at 0x7f89ba777100>, <column public at 0x7f89ba776fb0>], ('storage', 'migrations'): [<column id at 0x7f89ba776f20>, <column name at 0x7f89ba777190>, <column hash at 0x7f89ba7771c0>, <column executed_at at 0x7f89ba776e60>], ('storage', 'objects'): [<column id at 0x7f89ba777070>, <column bucket_id at 0x7f89ba777250>, <column name at 0x7f89ba777280>, <column owner at 0x7f89ba777130>, <column created_at at 0x7f89ba7770a0>, <column updated_at at 0x7f89ba7766e0>, <column last_accessed_at at 0x7f89ba7773a0>, <column metadata at 0x7f89ba777310>, <column path_tokens at 0x7f89ba7772e0>], ('supabase_functions', 'hooks'): [<column id at 0x7f89ba7771f0>, <column hook_table_id at 0x7f89ba777400>, <column hook_name at 0x7f89ba777a60>, <column created_at at 0x7f89ba7772b0>, <column request_id at 0x7f89ba7774f0>], ('supabase_functions', 'migrations'): [<column version at 0x7f89ba777520>, <column inserted_at at 0x7f89ba777370>]}
dbconstrs = {('auth', 'users', 'users_email_change_confirm_status_check'): <check users_email_change_confirm_status_check at 0x7f89ba7774c0>, ('pgsodium', 'key', 'key_key_context_check'): <check key_key_context_check at 0x7f89ba7775e0>, ('public', 'todos', 'todos_task_check'): <check todos_task_check at 0x7f89ba777220>, ('auth', 'users', 'users_pkey'): <primary key users_pkey at 0x7f89ba777640>, ('auth', 'refresh_tokens', 'refresh_tokens_pkey'): <primary key refresh_tokens_pkey at 0x7f89ba777610>, ('auth', 'instances', 'instances_pkey'): <primary key instances_pkey at 0x7f89ba777490>, ('auth', 'audit_log_entries', 'audit_log_entries_pkey'): <primary key audit_log_entries_pkey at 0x7f89ba777550>, ('auth', 'schema_migrations', 'schema_migrations_pkey'): <primary key schema_migrations_pkey at 0x7f89ba7776d0>, ('auth', 'identities', 'identities_pkey'): <primary key identities_pkey at 0x7f89ba777700>, ('public', 'lmao', 'lmao_pkey'): <primary key lmao_pkey at 0x7f89ba777430>, ('public', 'todos', 'todos_pkey'): <primary key todos_pkey at 0x7f89ba777580>, ('storage', 'buckets', 'buckets_pkey'): <primary key buckets_pkey at 0x7f89ba777790>, ('storage', 'objects', 'objects_pkey'): <primary key objects_pkey at 0x7f89ba7777c0>, ('storage', 'migrations', 'migrations_pkey'): <primary key migrations_pkey at 0x7f89ba777460>, ('supabase_functions', 'migrations', 'migrations_pkey'): <primary key migrations_pkey at 0x7f89ba777670>, ('supabase_functions', 'hooks', 'hooks_pkey'): <primary key hooks_pkey at 0x7f89ba777850>, ('auth', 'refresh_tokens', 'refresh_tokens_parent_fkey'): <foreign key refresh_tokens_parent_fkey at 0x7f89ba777730>, ('auth', 'identities', 'identities_user_id_fkey'): <foreign key identities_user_id_fkey at 0x7f89ba777880>, ('public', 'todos', 'todos_user_id_fkey'): <foreign key todos_user_id_fkey at 0x7f89ba7776a0>, ('storage', 'buckets', 'buckets_owner_fkey'): <foreign key buckets_owner_fkey at 0x7f89ba777910>, ('storage', 'objects', 'objects_bucketId_fkey'): <foreign key objects_bucketId_fkey at 0x7f89ba777940>, ('storage', 'objects', 'objects_owner_fkey'): <foreign key objects_owner_fkey at 0x7f89ba7773d0>, ('supabase_functions', 'hooks', 'hooks_request_id_fkey'): <foreign key hooks_request_id_fkey at 0x7f89ba7777f0>, ('auth', 'users', 'users_email_key'): <unique users_email_key at 0x7f89ba777a00>, ('auth', 'users', 'users_phone_key'): <unique users_phone_key at 0x7f89ba7779d0>, ('auth', 'refresh_tokens', 'refresh_tokens_token_unique'): <unique refresh_tokens_token_unique at 0x7f89ba7778b0>, ('storage', 'migrations', 'migrations_name_key'): <unique migrations_name_key at 0x7f89ba777820>}
dbindexes = {('auth', 'users', 'confirmation_token_idx'): <index confirmation_token_idx at 0x7f89ba777ac0>, ('auth', 'users', 'email_change_token_current_idx'): <index email_change_token_current_idx at 0x7f89ba777a90>, ('auth', 'users', 'email_change_token_new_idx'): <index email_change_token_new_idx at 0x7f89ba777760>, ('auth', 'users', 'reauthentication_token_idx'): <index reauthentication_token_idx at 0x7f89ba777970>, ('auth', 'users', 'recovery_token_idx'): <index recovery_token_idx at 0x7f89ba777b50>, ('auth', 'users', 'users_instance_id_email_idx'): <index users_instance_id_email_idx at 0x7f89ba777b80>, ('auth', 'users', 'users_instance_id_idx'): <index users_instance_id_idx at 0x7f89ba777a30>, ('auth', 'refresh_tokens', 'refresh_tokens_instance_id_idx'): <index refresh_tokens_instance_id_idx at 0x7f89ba7779a0>, ('auth', 'refresh_tokens', 'refresh_tokens_instance_id_user_id_idx'): <index refresh_tokens_instance_id_user_id_idx at 0x7f89ba775ab0>, ('auth', 'refresh_tokens', 'refresh_tokens_parent_idx'): <index refresh_tokens_parent_idx at 0x7f89ba777d30>, ('auth', 'refresh_tokens', 'refresh_tokens_token_idx'): <index refresh_tokens_token_idx at 0x7f89ba777cd0>, ('auth', 'audit_log_entries', 'audit_logs_instance_id_idx'): <index audit_logs_instance_id_idx at 0x7f89ba777c70>, ('auth', 'identities', 'identities_user_id_idx'): <index identities_user_id_idx at 0x7f89ba777be0>, ('net', 'http_request_queue', 'created_idx'): <index created_idx at 0x7f89ba777b20>, ('pgsodium', 'key', 'key_key_id_key_context_key_type_idx'): <index key_key_id_key_context_key_type_idx at 0x7f89ba777c40>, ('pgsodium', 'key', 'key_status_idx'): <index key_status_idx at 0x7f89ba777e20>, ('pgsodium', 'key', 'key_status_idx1'): <index key_status_idx1 at 0x7f89ba777e50>, ('storage', 'buckets', 'bname'): <index bname at 0x7f89ba777af0>, ('storage', 'objects', 'bucketid_objname'): <index bucketid_objname at 0x7f89ba777ca0>, ('storage', 'objects', 'name_prefix_search'): <index name_prefix_search at 0x7f89ba777ee0>, ('supabase_functions', 'hooks', 'supabase_functions_hooks_h_table_id_h_name_idx'): <index supabase_functions_hooks_h_table_id_h_name_idx at 0x7f89ba777f10>, ('supabase_functions', 'hooks', 'supabase_functions_hooks_request_id_idx'): <index supabase_functions_hooks_request_id_idx at 0x7f89ba777bb0>}
dbrules = {}
dbtriggers = {('storage', 'objects', 'update_objects_updated_at'): <trigger update_objects_updated_at at 0x7f89ba73dde0>}

pgsodium is used to generate jwt's and do other auth type stuff, so that unfortunately is not actually optional. I will try to debug that.

benjamin-kirkbride · 2022-08-23T01:39:41Z

I'm afraid that the only way to debug this will be to install whatever graphql library is used in supabase on a local db, like I did with pg_cron before.

Can I ask why you feel like this must be done on a local db, as opposed to the one that Supabase provides access to (which I have been using to troubleshoot so far)?

On a side-note, do you use/are you familiar with Docker? If so, I can probably whip up a compose file that you could use that would reproduce these issues locally.

benjamin-kirkbride · 2022-08-23T01:45:19Z

To give some more color to the above, I'm using the local Docker version of Supabase personally, and ran into issues using Pyrseas with it. I thought it would be easier for you or others to reproduce/follow along using the SaaS version though, hence that being the focus of this issue. The issue I run into locally is:

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 91, 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 114, in get_implied_deps
    deps.add(db.functions[(sch, fnc, '')])
KeyError: ('pg_catalog', 'notify_api_restart', '')
Uncaught exception. Entering post mortem debugging
Running 'cont' or 'step' will restart the program
> /home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/eventtrig.py(114)get_implied_deps()
-> deps.add(db.functions[(sch, fnc, '')])
(Pdb) a
self = <event trigger api_restart at 0x7fd8b612bd00>
db = <pyrseas.database.Database.Dicts object at 0x7fd8b61288e0>

and the SQL for it:

CREATE OR REPLACE FUNCTION extensions.notify_api_restart()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NOTIFY ddl_command_end;
END;
$$;
CREATE EVENT TRIGGER api_restart ON ddl_command_end
EXECUTE PROCEDURE extensions.notify_api_restart();
COMMENT ON FUNCTION extensions.notify_api_restart IS 'Sends a notification to the API to restart. If your database schema has changed, this is required so that Supabase can rebuild the relationships.';

If you feel like this issue is more tractable than the above ones, I'm happy to make a separate issue for it.

jmafc · 2022-08-23T01:55:33Z

Can I ask why you feel like this must be done on a local db, as opposed to the one that Supabase provides access to (which I have been using to troubleshoot so far)?

I guess it's mostly a question of habit. I can easily drop an extension locally, not having to worry about breaking anything. I can also run stuff locally both as a simple user or PG superuser.

If you feel like this issue is more tractable than the above ones, I'm happy to make a separate issue for it.

No need. We're still basically dealing with dbtoyaml and Supabase (and extensions).

benjamin-kirkbride · 2022-08-23T14:42:21Z

I guess it's mostly a question of habit. I can easily drop an extension locally, not having to worry about breaking anything. I can also run stuff locally both as a simple user or PG superuser.

Understood! I just wasn't sure if you were saying that there is something inherent about using the remote Supabase DB that precludes further troubleshooting.

How about this then, I will dump the schema out of the remote Supabase DB, and then figure out how to install the extensions and provide those here to make it easier to follow locally. Do you use docker, or would you prefer to not have it set up that way?

jmafc · 2022-08-23T14:52:45Z

Well I was going to ask you if you knew of some way/place to download pgsodium because their GH readme appears to say one has to "make install" and pgxn doesn't have download files. As far as Docker is concerned, I've tried it a couple of times and for some reason that I've forgotten, I couldn't make much headway.

benjamin-kirkbride · 2022-08-23T15:00:35Z

https://supabase.com/docs/guides/hosting/docker

If you aren't interested in using it, that's fine, but it is probably worth a try as it would make this take only a few commands vs having to rebuild it from scratch. If you have trouble with it let me know and I'd be happy to help, even if you wanted to jump on a call or something.

As far as where to find pg_sodium I'm really not sure. You sure there are no binaries on pgxn seems like they are but I may be missing something? https://pgxn.org/dist/pgsodium/

Thanks so much for all your help on this, by the way. I really appreciate it.

jmafc · 2022-08-23T15:19:45Z

The pgxn file available for pgsodium is essentially a zip of the GH repo, and unlike the one for Pyrseas, it's C source files that need to be compiled, instead of a Python egg.

benjamin-kirkbride · 2022-08-23T16:30:28Z

Gotcha. Yeah looking closer into it closer it seems like you are correct. The Supabase docker stack has that all pre-configured.

benjamin-kirkbride · 2022-08-25T01:22:00Z

So I have managed to trace down where the ('pgsodium', 'key', 'key_key_context_check') is added. Doesn't seem like much (and I'm sure it's not) but it did take like 8 hours to get to this point so.. yay? I'm going to continue and see if I can't figure out why it's not in the ClassDict.

benjamin-kirkbride · 2022-08-25T18:58:38Z

I think that the former is pretty simple to implement

@dvarrazzo are you able to point me in the direction of how that might look/where to start?

dvarrazzo · 2022-08-25T22:24:20Z

Most of the dependencies calculation is built by querying the pg_depend table. The table lists which object (classid, objid) depends on another object (refclassid, refobjid). We mostly care about the normal relations (deptype='n'), and we have to consider the extensions relations (deptype='e') differently.

So far what we did was to exclude from the queries that find the objects in the database all the objects that belong to an extension, because the algorithm "first create the extensions, then everything else" sort of works ok. But the consequence is that we have to filter out every object and errors are possible.

I would say that I would do:

grep for deptype, drop every where clause which exclude objects in extensions: they look like:

              # pyrseas/dbobject/operator.py
              AND o.oid NOT IN (
                  SELECT objid FROM pg_depend WHERE deptype = 'e'
                               AND classid = 'pg_operator'::regclass)

building the dependency graph, include the e dependencies too (WHERE deptype in ('e', 'n')). The deptype should also be part of the output of the queries in this function: we have to note down which object (classid, objid) depends on an exception.
When we build the output, we have to exclude the objects depending on an exception, so here there should be something like:

if self.db.is_extension_object(new):
    continue

which would check if new is one of the objects found as belonging to an extension.

benjamin-kirkbride · 2023-03-22T00:11:48Z

I'm back! Like I was never gone ;)

jmafc · 2023-03-22T00:15:20Z

Welcome back then!

benjamin-kirkbride · 2023-03-22T00:17:30Z

I'm assuming no progress has been made since I fell off the earth? I'm going to move forward with what @dvarrazzo suggested, and keep everyone posted here.

jmafc · 2023-03-22T00:29:37Z

I guess it depends on what was the point-in-time when you fell off. I have released 0.10.0 and made three commits since then, including one that involved removing one of those SELECT objid FROM pg_depend WHERE deptype = 'e' clauses.

benjamin-kirkbride · 2023-03-22T00:32:33Z

I see, well I am going to start from scratch with this and we will see where it goes :)

benjamin-kirkbride · 2023-03-22T00:56:21Z

Create a new test project on supabase.com
navigate to Project Settings > Database
clone Pyrseas
install Pyrseas, as well as psycopg[binary] Fails to Install: Error: pg_config executable not found #243 (comment)
dbtoyaml.py -U postgres -H db.hbpbvxczweoxlfswwgfa.supabase.co -p 5432 --password postgres

Traceback (most recent call last):
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/./dbtoyaml.py", line 57, in <module>
    main()
  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 444, 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 223, in _link_refs
    db.tables.link_refs(db.columns, db.constraints, db.indexes, db.rules,
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/table.py", line 947, in link_refs
    assert self[(sch, tbl)]
           ~~~~^^^^^^^^^^^^
KeyError: ('pgsodium', 'key')

benjamin-kirkbride · 2023-03-22T01:09:34Z

Okay, so I (believe I) have done what @dvarrazzo suggested, excepting the if self.db.is_extension_object(new): continue portion. I get the following exception now:

Traceback (most recent call last):
  File "/home/bkirkbride/projects/Pyrseas/./pyrseas/dbtoyaml.py", line 57, in <module>
    main()
  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 486, in to_map
    dbmap.update(self.db.eventtrigs.to_map(self.db, opts))
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/__init__.py", line 657, in to_map
    objmap = obj.to_map(db, opts.no_owner, opts.no_privs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/eventtrig.py", line 87, in to_map
    dct = super(EventTrigger, self).to_map(db, no_owner)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/__init__.py", line 356, in to_map
    deps -= self.get_implied_deps(db)
            ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/eventtrig.py", line 110, in get_implied_deps
    deps.add(db.functions[(sch, fnc, '')])
             ~~~~~~~~~~~~^^^^^^^^^^^^^^^^
KeyError: ('pg_catalog', 'set_graphql_placeholder', '')

jmafc · 2023-03-22T13:18:51Z

It's unclear what exactly you did when you state having "done what @dvarrazzo suggested". Although Daniele wrote that he thought "the former is pretty simple to implement", it does entail changes to almost every other file in pyrseas/dbobject (10 out of 23 by my reckoning and several files have more than one query). Did you do that or did you limit yourself to some files somehow?

benjamin-kirkbride · 2023-03-23T02:04:13Z

I did that:

benjamin-kirkbride@0f7c478

benjamin-kirkbride · 2023-03-23T02:04:52Z

Apologies for the extraneous changes, I am using Ruff and Black and they move some stuff around.

benjamin-kirkbride · 2023-03-23T02:17:49Z

I also added the

if self.db.is_extension_object(new):
    continue

part, but the error occurs before that area gets hit, so it makes no difference.

benjamin-kirkbride · 2023-03-23T02:29:46Z

EDIT: IGNORE THIS MESSAGE, I messed something up and this is wrong (?)

Here is what the query returns for the ProcDict (which is I believe what the db.functions is):

[
    {
        "schema": "pgsodium",
        "name": "crypto_sign_update_agg",
        "arguments": "message bytea",
        "owner": "supabase_admin",
        "privileges": None,
        "sfunc": "pgsodium.crypto_sign_update_agg1",
        "stype": "bytea",
        "sspace": 0,
        "finalfunc": "-",
        "finalfunc_extra": False,
        "initcond": None,
        "sortop": "0",
        "msfunc": "-",
        "minvfunc": "-",
        "mstype": "-",
        "msspace": 0,
        "mfinalfunc": "-",
        "mfinalfunc_extra": False,
        "minitcond": None,
        "kind": "n",
        "combinefunc": "-",
        "serialfunc": "-",
        "deserialfunc": "-",
        "parallel": "u",
        "description": "Multi-part message signing aggregate that returns a state which can\nthen be finalised using crypto_sign_final() or to which other parts\ncan be added crypto_sign_update() or another message signing aggregate\nfunction.\n\nNote that when signing mutli-part messages using aggregates, the order\nin which message parts is processed is critical. You *must* ensure\nthat the order of messages passed to the aggregate is invariant.",
        "oid": 16701,
    },
    {
        "schema": "pgsodium",
        "name": "crypto_sign_update_agg",
        "arguments": "state bytea, message bytea",
        "owner": "supabase_admin",
        "privileges": None,
        "sfunc": "pgsodium.crypto_sign_update_agg2",
        "stype": "bytea",
        "sspace": 0,
        "finalfunc": "-",
        "finalfunc_extra": False,
        "initcond": None,
        "sortop": "0",
        "msfunc": "-",
        "minvfunc": "-",
        "mstype": "-",
        "msspace": 0,
        "mfinalfunc": "-",
        "mfinalfunc_extra": False,
        "minitcond": None,
        "kind": "n",
        "combinefunc": "-",
        "serialfunc": "-",
        "deserialfunc": "-",
        "parallel": "u",
        "description": "Multi-part message signing aggregate that returns a state which can\nthen be finalised using crypto_sign_final() or to which other parts\ncan be added crypto_sign_update() or another message signing aggregate\nfunction.\n\nThe first argument to this aggregate is the input state. This may be\nthe result of a previous crypto_sign_update_agg(), a previous\ncrypto_sign_update().\n\nNote that when signing mutli-part messages using aggregates, the order\nin which message parts is processed is critical. You *must* ensure\nthat the order of messages passed to the aggregate is invariant.",
        "oid": 16702,
    },
]

Not sure why it doesn't have the GraphQL stuff...

benjamin-kirkbride · 2023-03-23T02:31:51Z

Oh, duh.

SELECT nspname AS schema, proname AS name,
                   pg_get_function_identity_arguments(p.oid) AS arguments,
                   pg_get_function_arguments(p.oid) AS allargs,
                   pg_get_function_result(p.oid) AS returns, rolname AS owner,
                   array_to_string(proacl, ',') AS privileges,
                   l.lanname AS language, provolatile AS volatility,
                   proisstrict AS strict, prosrc AS source,
                   probin::text AS obj_file, proconfig AS configuration,
                   prosecdef AS security_definer, procost AS cost,
                   proleakproof AS leakproof, prorows::integer AS rows,
                   obj_description(p.oid, 'pg_proc') AS description, p.oid
            FROM pg_proc p JOIN pg_roles r ON (r.oid = proowner)
                 JOIN pg_namespace n ON (pronamespace = n.oid)
                 JOIN pg_language l ON (prolang = l.oid)
            WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema')
              AND %s
            ORDER BY nspname, proname

ENHANCE

WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema')

I assume there was a (good) reason for excluding pg_catalog, but that is where the GraphQL stuff appears to be...

benjamin-kirkbride · 2023-03-23T02:39:56Z

Removing that condition:

Traceback (most recent call last):
  File "/home/bkirkbride/projects/Pyrseas/./pyrseas/dbtoyaml.py", line 57, in <module>
    main()
  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 528, in to_map
    dbmap.update(self.db.eventtrigs.to_map(self.db, opts))
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/__init__.py", line 692, in to_map
    objmap = obj.to_map(db, opts.no_owner, opts.no_privs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/eventtrig.py", line 87, in to_map
    dct = super(EventTrigger, self).to_map(db, no_owner)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/__init__.py", line 371, in to_map
    deps -= self.get_implied_deps(db)
            ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbobject/eventtrig.py", line 110, in get_implied_deps
    deps.add(db.functions[(sch, fnc, '')])
             ~~~~~~~~~~~~^^^^^^^^^^^^^^^^
KeyError: ('pg_catalog', 'set_graphql_placeholder', '')

Contents of ProcDict are now much, much too large to put anywhere but a pastebin, but I was able to find this in the output:

{
    "schema": "extensions",
    "name": "set_graphql_placeholder",
    "arguments": "",
    "allargs": "",
    "returns": "event_trigger",
    "owner": "supabase_admin",
    "privileges": "=X/supabase_admin,supabase_admin=X/supabase_admin,postgres=X*/supabase_admin",
    "language": "plpgsql",
    "volatility": "v",
    "strict": False,
    "source": "\n    DECLARE\n    graphql_is_dropped bool;\n    BEGIN\n    graphql_is_dropped = (\n        SELECT ev.schema_name = 'graphql_public'\n        FROM pg_event_trigger_dropped_objects() AS ev\n        WHERE ev.schema_name = 'graphql_public'\n    );\n\n    IF graphql_is_dropped\n    THEN\n        create or replace function graphql_public.graphql(\n            \"operationName\" text default null,\n            query text default null,\n            variables jsonb default null,\n            extensions jsonb default null\n        )\n            returns jsonb\n            language plpgsql\n        as $$\n            DECLARE\n                server_version float;\n            BEGIN\n                server_version = (SELECT (SPLIT_PART((select version()), ' ', 2))::float);\n\n                IF server_version >= 14 THEN\n                    RETURN jsonb_build_object(\n                        'errors', jsonb_build_array(\n                            jsonb_build_object(\n                                'message', 'pg_graphql extension is not enabled.'\n                            )\n                        )\n                    );\n                ELSE\n                    RETURN jsonb_build_object(\n                        'errors', jsonb_build_array(\n                            jsonb_build_object(\n                                'message', 'pg_graphql is only available on projects running Postgres 14 onwards.'\n                            )\n                        )\n                    );\n                END IF;\n            END;\n        $$;\n    END IF;\n\n    END;\n",
    "obj_file": None,
    "configuration": None,
    "security_definer": False,
    "cost": 100.0,
    "leakproof": False,
    "rows": 0,
    "description": "Reintroduces placeholder function for graphql_public.graphql",
    "oid": 16613,
}

But why is the schema extensions and not pg_catalog 🤔

benjamin-kirkbride · 2023-03-23T02:48:06Z

Okay, so it seems that set_graphql_placeholder has been in db.functions all along.

If I execute db.functions[("extensions", "set_graphql_placeholder", "")] it returns <function set_graphql_placeholder() at 0x7ff2e5b9bad0> (which I can only assume is correct)

So the question is, why does self.procedure think that it's schema is pg_catalog and not where it truly is: extensions

benjamin-kirkbride · 2023-03-23T02:56:53Z

We default to pg_catalog if a function is unqualified: https://github.com/perseas/Pyrseas/blob/master/pyrseas/dbobject/__init__.py#L74

graphql.increment_schema_version -- ('graphql', 'increment_schema_version')
graphql.increment_schema_version -- ('graphql', 'increment_schema_version')
set_graphql_placeholder -- ('pg_catalog', 'set_graphql_placeholder')
grant_pg_cron_access -- ('pg_catalog', 'grant_pg_cron_access')
grant_pg_graphql_access -- ('pg_catalog', 'grant_pg_graphql_access')
grant_pg_net_access -- ('pg_catalog', 'grant_pg_net_access')
pgrst_ddl_watch -- ('pg_catalog', 'pgrst_ddl_watch')
pgrst_drop_watch -- ('pg_catalog', 'pgrst_drop_watch')
pgsodium.trg_mask_update -- ('pgsodium', 'trg_mask_update')

You can see how this works on a handful of functions above. For some reason the graphql ones are all coming in unqualified.

jmafc · 2023-03-23T02:58:11Z

Initially pg_catalog was excluded because it would've meant dealing with a lot of internal minutiae, e.g., all of the basic PG types, all of the PG catalog relations, all of the PG functions, when the aim was to show user information. IIRC, extensions didn't even exist back then. In any case, when extensions were added to PG we had to show them, because they are user "entities", albeit most of the sub-entities of an extension are in a sense "hidden", so we had to deal with all those quirks. The idea of eliminating the "special cases" for extensions is essentially part of #185, which we found necessary for things other than extensions, e.g., someone can create an aggregate using ts_vector (see #175), and we don't know about that type because it's in pg_catalog. So we really would have to read all the pg_catalog schema (I suspect it wouldn't be ideal for performance) and then carefully exclude any item that is truly internal, like a PG int2 datatype (but not a user's compound type that uses an int2 [or worse an int2 defined in a user schema]). And that is just on the dbtoyaml side.

benjamin-kirkbride · 2023-03-23T03:00:14Z

Thanks for the insight @jmafc, it seems like in this case though, it makes no difference, so I put it back to how it was.

jmafc · 2023-03-23T03:10:05Z

But why is the schema extensions and not pg_catalog

The mystery lies (I think) in how pg_depend stores stuff and how the casting functions (like '::regclass') work. If we read in all of the pg_catalogs, the pg_depend subqueries could just retrieve integers and we could make sense of them by our own functions but that's a lot of extra work--and it's on something that could change from one PG release to the next.

benjamin-kirkbride · 2023-03-23T03:11:56Z

Could we dump the results for each version and store them statically? I assume it wouldn't vary from one install to the other, assuming a given version

jmafc · 2023-03-23T03:32:13Z

I don't think there's a standard "install" of a given PG version because a particular user database depends first of all on a "template" database and also on the command used to create the database (things like encoding, collation, etc.). In any case this is getting into the design of a solution for #185, which could entail a YAML "reference" file where we store the contents of the pg_catalog schema from a previous invocation.

benjamin-kirkbride · 2023-03-23T05:52:33Z

I see, so do you feel like this is a dead end without resolving #185, or ?

jmafc · 2023-03-23T12:11:35Z

Not entirely. If I were dealing with this I would first try to install graphql locally, then I would query the pg_proc catalog with the name of one of the "misplaced" functions, then pg_depend with the oid of the function against potential column matches and then recheck our Function query to see where it may be wrong. And since graphql is an extension, I'd also check the relevant schema and extension oids. And come to think of it, there's another possible source of errror in #165: the split_schema function(s) were an early attempt to parse qualified/unqualified names and the PG quirk of defaulting schema names based on the settings in search_path.

benjamin-kirkbride · 2023-03-23T15:53:31Z

search_path was the direction I was about to head with it. Unfortunately I'm about to travel for a week or so, but I have every intention™ of picking this back up when I'm back

benjamin-kirkbride · 2023-03-23T18:01:03Z

@jmafc @dvarrazzo would either of you happen to be going to PyCon this year?

jmafc · 2023-03-23T18:08:24Z

Pas moi (or considering Daniele is the other one queried, maybe I should say, non me).

jmafc added the dbtoyaml label Aug 20, 2022

jmafc added the extensions Problems caused by PG extensions label Aug 30, 2022

jmafc mentioned this issue Mar 23, 2023

Build a cache of Postgres system objects #185

Open

Error Using dbtoyaml With Supabase Schema #244

Error Using dbtoyaml With Supabase Schema #244

Comments

benjamin-kirkbride commented Aug 20, 2022 • edited Loading

jmafc commented Aug 20, 2022

benjamin-kirkbride commented Aug 21, 2022 • edited Loading

benjamin-kirkbride commented Aug 21, 2022 • edited Loading

benjamin-kirkbride commented Aug 21, 2022 • edited Loading

benjamin-kirkbride commented Aug 21, 2022

jmafc commented Aug 21, 2022

benjamin-kirkbride commented Aug 22, 2022

benjamin-kirkbride commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022

jmafc commented Aug 23, 2022

jmafc commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022

jmafc commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022

jmafc commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022

jmafc commented Aug 23, 2022

jmafc commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022 • edited Loading

jmafc commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022 • edited Loading

jmafc commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022

jmafc commented Aug 23, 2022

benjamin-kirkbride commented Aug 23, 2022

benjamin-kirkbride commented Aug 25, 2022

benjamin-kirkbride commented Aug 25, 2022

dvarrazzo commented Aug 25, 2022

benjamin-kirkbride commented Mar 22, 2023

jmafc commented Mar 22, 2023

benjamin-kirkbride commented Mar 22, 2023

jmafc commented Mar 22, 2023

benjamin-kirkbride commented Mar 22, 2023

benjamin-kirkbride commented Mar 22, 2023

benjamin-kirkbride commented Mar 22, 2023

jmafc commented Mar 22, 2023

benjamin-kirkbride commented Mar 23, 2023

benjamin-kirkbride commented Mar 23, 2023

benjamin-kirkbride commented Mar 23, 2023

benjamin-kirkbride commented Mar 23, 2023 • edited Loading

EDIT: IGNORE THIS MESSAGE, I messed something up and this is wrong (?)

benjamin-kirkbride commented Mar 23, 2023 • edited Loading

benjamin-kirkbride commented Mar 23, 2023

benjamin-kirkbride commented Mar 23, 2023 • edited Loading

benjamin-kirkbride commented Mar 23, 2023

jmafc commented Mar 23, 2023

benjamin-kirkbride commented Mar 23, 2023

jmafc commented Mar 23, 2023

benjamin-kirkbride commented Mar 23, 2023

jmafc commented Mar 23, 2023

benjamin-kirkbride commented Mar 23, 2023

jmafc commented Mar 23, 2023

benjamin-kirkbride commented Mar 23, 2023

benjamin-kirkbride commented Mar 23, 2023

jmafc commented Mar 23, 2023

benjamin-kirkbride commented Aug 20, 2022 •

edited

Loading

benjamin-kirkbride commented Aug 21, 2022 •

edited

Loading

benjamin-kirkbride commented Aug 21, 2022 •

edited

Loading

benjamin-kirkbride commented Aug 21, 2022 •

edited

Loading

benjamin-kirkbride commented Aug 23, 2022 •

edited

Loading

benjamin-kirkbride commented Aug 23, 2022 •

edited

Loading

benjamin-kirkbride commented Mar 23, 2023 •

edited

Loading

benjamin-kirkbride commented Mar 23, 2023 •

edited

Loading

benjamin-kirkbride commented Mar 23, 2023 •

edited

Loading