search

perseas / Pyrseas

Provides utilities for Postgres database schema versioning.
https://perseas.github.io/
BSD 3-Clause "New" or "Revised" License
395 stars 67 forks source link

Error Using dbtoyaml With Supabase Schema #244

Open benjamin-kirkbride opened 2 years ago

benjamin-kirkbride commented 2 years ago

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 https://github.com/perseas/Pyrseas/issues/236.

jmafc commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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

jmafc commented 2 years ago

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

benjamin-kirkbride commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

The query is in https://github.com/perseas/Pyrseas/blob/f06c80a66f8670bb6d4578dbc26e03bc909698cc/pyrseas/dbobject/dbtype.py#L375

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 commented 2 years ago

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

No rows returned

Not sure what that implies.

jmafc commented 2 years ago

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 commented 2 years ago

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: image

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 commented 2 years ago

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 commented 2 years ago

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

benjamin-kirkbride commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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

benjamin-kirkbride commented 2 years ago

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.

jmafc commented 2 years ago

Now it's my turn to thank you venturing on and helping.

benjamin-kirkbride commented 2 years ago

The query found in CheckConstraint returns the following:

                  name                   |  schema  |    table     | is_domain_check | columns |                                 expression                                  | inherited |  oid  | description 
-----------------------------------------+----------+--------------+-----------------+---------+-----------------------------------------------------------------------------+-----------+-------+-------------
 users_email_change_confirm_status_check | auth     | auth.users   | f               | {29}    | ((email_change_confirm_status >= 0) AND (email_change_confirm_status <= 2)) | f         | 17642 | 
 key_key_context_check                   | pgsodium | pgsodium.key | f               | {7}     | (length(key_context) = 8)                                                   | f         | 16566 | 
 todos_task_check                        | public   | todos        | f               | {3}     | (char_length(task) > 3)                                                     | f         | 17785 | 
(3 rows)

The second row is the one causing the problems.

benjamin-kirkbride commented 2 years ago 
postgres=# \d+ pgsodium.key
                                                                         Table "pgsodium.key"
   Column    |            Type             | Collation | Nullable |                   Default                    | Storage  | Compression | Stats target | Description 
-------------+-----------------------------+-----------+----------+----------------------------------------------+----------+-------------+--------------+-------------
 id          | uuid                        |           | not null | gen_random_uuid()                            | plain    |             |              | 
 status      | pgsodium.key_status         |           |          | 'valid'::pgsodium.key_status                 | plain    |             |              | 
 created     | timestamp without time zone |           | not null | CURRENT_TIMESTAMP                            | plain    |             |              | 
 expires     | timestamp without time zone |           |          |                                              | plain    |             |              | 
 key_type    | pgsodium.key_type           |           |          |                                              | plain    |             |              | 
 key_id      | bigint                      |           | not null | nextval('pgsodium.key_key_id_seq'::regclass) | plain    |             |              | 
 key_context | bytea                       |           | not null | '\x7067736f6469756d'::bytea                  | extended |             |              | 
 comment     | text                        |           |          |                                              | extended |             |              | 
 user_data   | jsonb                       |           |          |                                              | extended |             |              | 
Indexes:
    "key_pkey" PRIMARY KEY, btree (id)
    "key_key_id_key_context_key_type_idx" UNIQUE, btree (key_id, key_context, key_type)
    "key_status_idx" btree (status) WHERE status = ANY (ARRAY['valid'::pgsodium.key_status, 'default'::pgsodium.key_status])
    "key_status_idx1" UNIQUE, btree (status) WHERE status = 'default'::pgsodium.key_status
Check constraints:
    "key_key_context_check" CHECK (length(key_context) = 8)
Access method: heap
jmafc commented 2 years ago

Ok, so the problem is that the last AND clause in that query is not excluding the key_key_context_check row as expected. To fix this, it's usually necessary to examine the pg_depend table and figure out why the row is still making it through even though it's obviously installed by an extension.

benjamin-kirkbride commented 2 years ago

the problem is that the last AND clause in that query is not excluding the key_key_context_check row as expected

So we don't want that constraint to be processed? Why is that?

benjamin-kirkbride commented 2 years ago

So just for fun I added AND conname <> 'key_key_context_check' to the WHERE clause and it did make it further:

Traceback (most recent call last):
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbtoyaml.py", line 59, in <module>
    main()
  File "/home/bkirkbride/projects/Pyrseas/pyrseas/dbtoyaml.py", line 51, 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 975, in link_refs
    constr._references = self[(
KeyError: ('net', 'http_request_queue')

I will further investigate how to holistically handle this.

benjamin-kirkbride commented 2 years ago 
$ psql -h db.nynstytcyprpinthzhos.supabase.co -U postgres postgres -f const.sql 
Password for user postgres: 
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype 
---------+-------+----------+------------+----------+-------------+---------
    2606 | 16566 |        0 |       1259 |    16558 |           7 | a
    2606 | 16566 |        0 |       1259 |    16558 |           7 | n
(2 rows)

These are the rows in question (I believe). note the deptype is not e. Not sure why.

jmafc commented 2 years ago

Unfortunately, PG extensions, although they may be useful, they make internal relationships very obscure. If you look at pg_depend all you'll find is a bunch of OIDs, sometimes seemingly repeated for the same object (there's a minor OID or deptype diff). @dvarrazzo had suggested that perhaps we ought to load all objects and all catalogs and then filter stuff on output. While this may be doable and perhaps would be a solution (we wouldn't need those NOT IN (SELECT ... FROM pg_depend) clauses, it's not an easy task. With respect to the last comment and the lack of deptype = 'e', it's a good example of what I was writing above: there's some object that is probably indirectly related to that constraint and it's not obvious how to exclude it. FYI, I will be unavailable until about Monday.

benjamin-kirkbride commented 2 years ago

I see.. I feared that this can of worms might take more than just a few tweaks here and there.

FYI, I will be unavailable until about Monday.

Understood. Thanks so much for all your help! Is your @ freedomcircle still a good way to contact you privately? I'd love to have a chat about this project if that is something you are open to.

dvarrazzo commented 2 years ago

@dvarrazzo had suggested that perhaps we ought to load all objects and all catalogs and then filter stuff on output. While this may be doable and perhaps would be a solution (we wouldn't need those NOT IN (SELECT ... FROM pg_depend) clauses, it's not an easy task.

That's correct. At the moment we are special-casing every kind of object to deal with dependencies from extensions, which is very brittle. We should drop all these deptype='e' filters instead and include extensions objects in the normal dependency graph calculation.

After we have such graph, the problem can be solved in different ways: excluding from the output every object belonging to an extension is the simplest one, provided that a dependency on the extension itself exists so the extension gets actually installed.

In practice, I think that the graph should contain:

my_objects --(depends on) --> an_extension_object --(belongs to)--> an_extension

and for it we need an output:

CREATE EXTENSION an_extension;
# no "an_extension_object" in the output
CREATE my_object;

In order to produce it, either we exclude every object with a (belongs to) relation from the output generation, or we manipulate the graph dropping the intermediate objects:

my_objects --(depends on) --> an_extension

I think that the former is pretty simple to implement, and that the latter throws away useful information.

benjamin-kirkbride commented 2 years ago

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 commented 2 years ago

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:

              # pyrseas/dbobject/operator.py
              AND o.oid NOT IN (
                  SELECT objid FROM pg_depend WHERE deptype = 'e'
                               AND classid = 'pg_operator'::regclass)
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 commented 1 year ago

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

jmafc commented 1 year ago

Welcome back then!

benjamin-kirkbride commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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

benjamin-kirkbride commented 1 year ago
  1. Create a new test project on supabase.com
  2. navigate to Project Settings > Database
  3. clone Pyrseas
  4. install Pyrseas, as well as psycopg[binary] https://github.com/perseas/Pyrseas/issues/243#issuecomment-1478771914
  5. 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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

I did that:

https://github.com/benjamin-kirkbride/Pyrseas/commit/0f7c4784ca177dfb71f82cd08e345eaf90787086