dbtoyaml fails when dealing with extension-owned triggers/functions

[jmafc]

Initially reported by @alexitheodore in issue #226 starting from 2-Nov-2021. A sample traceback is as follows:

  File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/trigger.py", line 156, in to_map
    dct = super(Trigger, self).to_map(db)
  File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/__init__.py", line 361, in to_map
    deps -= self.get_implied_deps(db)
  File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/trigger.py", line 239, in get_implied_deps
    deps.add(db.functions[fschema, fname, self.arguments or ''])
KeyError: (u'cron', 'job_cache_invalidate', '')

[jmafc]

The suggested fix is as follows (to be applied to dbobject/trigger.py):

            WHERE NOT tgisinternal
              AND (nspname != 'pg_catalog' AND nspname != 'information_schema')
              AND t.tgfoid NOT IN (
                  SELECT objid FROM pg_depend WHERE deptype = 'e'
                               AND classid = 'pg_proc'::regclass)

[alexitheodore]

@jmafc

Just tested this and confirmed that it resolved the pg_cron-related bug.

[jmafc]

OK, since (as you stated in the other issue) the "second error persists", there must still be some other query that needs to be fixed to deal properly with extension-related objects. A quick look at function.py shows that the in queryunder classAggregate(lines 494-511) we are already excluding aggregate functions that are associated with extensions. However, thejson_agg_stateffunction, which is the one reported as not found, is one that you *would* want to include in thedbtoyamloutput, since it's user-created. Removing theAND p.oid NOT IN (SELECT ...)` will perhaps fix this particular error, but as a side effect may cause errors elsewhere.

[alexitheodore]

So, I tested that query against my DB and it does appear to be producing a row that corresponds to the function mentioned in the error stack:

KeyError: (u'public', 'json_agg_statef', u'jsonb, jsonb')

I don't know if that means it IS working, or that IS the issue (seems to me that it would be the former). Is there another query that I should check to make sure it produces a corresponding result with the function def?

[jmafc]

I'm not sure what "that query" means, since although I quoted some lines, the full query is actually constructed and returned on line 534. Unless you've learned Python since we started, I wonder what your test query really was. Anyway, if I were to start debugging, I'd add a print(obj.key()) statement in ProcDict._from_catalog, line 699, right after the for obj in self.fetch(). That will actually list the function names being retrieved.

[alexitheodore]

This is the query I ran:

SELECT
    nspname AS schema
,   proname AS name
,   pg_get_function_identity_arguments(p.oid) AS arguments
,   rolname AS owner
,   array_to_string(proacl, ',') AS privileges
,   aggtransfn::regproc AS sfunc
,   aggtranstype::regtype AS stype
-- ,    %s AS sspace
,   aggfinalfn::regproc AS finalfunc
-- ,    %s AS finalfunc_extra
,   agginitval AS initcond
,   aggsortop::regoper AS sortop
-- ,    %s
,   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)
     LEFT JOIN pg_aggregate a ON (p.oid = aggfnoid)
WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema')
  AND prokind = 'f'
  AND p.oid NOT IN (
      SELECT objid FROM pg_depend WHERE deptype = 'e'
                   AND classid = 'pg_proc'::regclass)
ORDER BY nspname, proname
;

The columns returned are inconsequential in this context and the only variable substitution I could find was one AND condition, which I easily found. Based on the look of the code, that would be the most restrictive case too. Not saying I've provided anything useful, just trying to help.

[jmafc]

@alexitheodore Change db74727, which I just submitted, takes care of cleaning up the output of dbtoyaml when the pg_cron extension is installed. Looking back at issue #226, it seems that the json_agg_statef error is unrelated to extensions (since you wrote that had started from a "totally clean slate again"). If that is the case, we should deal with the aggregate issue separately (and then I can close this one). I suspect that the json_agg_statef may be related to or the same as issue #175.