Add pg_dump support for ALTER obj DEPENDS ON EXTENSION

pg_dump is oblivious to this kind of dependency, so they're lost on
dump/restores (and pg_upgrade).  Have pg_dump emit ALTER lines so that
they're preserved.  Add some pg_dump tests for the whole thing, also.

Reviewed-by: Tom Lane (offlist)
Reviewed-by: Ibrar Ahmed
Reviewed-by: Ahsan Hadi (who also reviewed commit 899a04f5ed61)
Discussion: https://postgr.es/m/20200217225333.GA30974@alvherre.pgsql

diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1cbb9874f321b9adb09fd3d28c4cbbc3ffd8648b..ce1a9506e8496d62c6daaa2cc0cb4bea41726bde 100644 (file)
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -445,6 +445,7 @@ AssignDumpId(DumpableObject *dobj)
    dobj->namespace = NULL;     /* may be set later */
    dobj->dump = DUMP_COMPONENT_ALL;    /* default assumption */
    dobj->ext_member = false;   /* default assumption */
+   dobj->depends_on_ext = false;   /* default assumption */
    dobj->dependencies = NULL;
    dobj->nDeps = 0;
    dobj->allocDeps = 0;

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 5c51c789fb3a8c78231610b45126563fe10811ba..49f0ee886afc17e963cc40521362643f317130ff 100644 (file)
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3447,6 +3447,55 @@ dumpPolicy(Archive *fout, PolicyInfo *polinfo)
    free(qtabname);
 }
 
+/*
+ * Given a "create query", append as many ALTER ... DEPENDS ON EXTENSION as
+ * the object needs.
+ */
+static void
+append_depends_on_extension(Archive *fout,
+                           PQExpBuffer create,
+                           DumpableObject *dobj,
+                           const char *catalog,
+                           const char *keyword,
+                           const char *objname)
+{
+   if (dobj->depends_on_ext)
+   {
+       char   *nm;
+       PGresult   *res;
+       PQExpBuffer query;
+       int     ntups;
+       int     i_extname;
+       int     i;
+
+       /* dodge fmtId() non-reentrancy */
+       nm = pg_strdup(objname);
+
+       query = createPQExpBuffer();
+       appendPQExpBuffer(query,
+                         "SELECT e.extname "
+                         "FROM pg_catalog.pg_depend d, pg_catalog.pg_extension e "
+                         "WHERE d.refobjid = e.oid AND classid = '%s'::pg_catalog.regclass "
+                         "AND objid = '%u'::pg_catalog.oid AND deptype = 'x' "
+                         "AND refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass",
+                         catalog,
+                         dobj->catId.oid);
+       res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+       ntups = PQntuples(res);
+       i_extname = PQfnumber(res, "extname");
+       for (i = 0; i < ntups; i++)
+       {
+           appendPQExpBuffer(create, "ALTER %s %s DEPENDS ON EXTENSION %s;\n",
+                             keyword, nm,
+                             fmtId(PQgetvalue(res, i, i_extname)));
+       }
+
+       PQclear(res);
+       pg_free(nm);
+   }
+}
+
+
 static void
 binary_upgrade_set_type_oids_by_type_oid(Archive *fout,
                                         PQExpBuffer upgrade_buffer,
@@ -11847,6 +11896,12 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
 
    appendPQExpBuffer(q, "\n    %s;\n", asPart->data);
 
+   append_depends_on_extension(fout, q, &finfo->dobj,
+                               "pg_catalog.pg_proc", "FUNCTION",
+                               psprintf("%s.%s",
+                                        fmtId(finfo->dobj.namespace->dobj.name),
+                                        funcsig));
+
    if (dopt->binary_upgrade)
        binary_upgrade_extension_member(q, &finfo->dobj,
                                        "FUNCTION", funcsig,
@@ -15515,6 +15570,14 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
        else
            appendPQExpBufferStr(q, ";\n");
 
+       /* Materialized views can depend on extensions */
+       if (tbinfo->relkind == RELKIND_MATVIEW)
+           append_depends_on_extension(fout, q, &tbinfo->dobj,
+                                       "pg_catalog.pg_class",
+                                       tbinfo->relkind == RELKIND_MATVIEW ?
+                                       "MATERIALIZED VIEW" : "INDEX",
+                                       qualrelname);
+
        /*
         * To create binary-compatible heap files, we have to ensure the same
         * physical column order, including dropped columns, as in the
@@ -15962,6 +16025,7 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
    PQExpBuffer q;
    PQExpBuffer delq;
    char       *qindxname;
+   char       *qqindxname;
 
    if (dopt->dataOnly)
        return;
@@ -15970,6 +16034,7 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
    delq = createPQExpBuffer();
 
    qindxname = pg_strdup(fmtId(indxinfo->dobj.name));
+   qqindxname = pg_strdup(fmtQualifiedDumpable(indxinfo));
 
    /*
     * If there's an associated constraint, don't dump the index per se, but
@@ -16003,6 +16068,11 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
                              qindxname);
        }
 
+       /* Indexes can depend on extensions */
+       append_depends_on_extension(fout, q, &indxinfo->dobj,
+                                   "pg_catalog.pg_class",
+                                   "INDEX", qqindxname);
+
        /* If the index defines identity, we need to record that. */
        if (indxinfo->indisreplident)
        {
@@ -16013,8 +16083,7 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
                              qindxname);
        }
 
-       appendPQExpBuffer(delq, "DROP INDEX %s;\n",
-                         fmtQualifiedDumpable(indxinfo));
+       appendPQExpBuffer(delq, "DROP INDEX %s;\n", qqindxname);
 
        if (indxinfo->dobj.dump & DUMP_COMPONENT_DEFINITION)
            ArchiveEntry(fout, indxinfo->dobj.catId, indxinfo->dobj.dumpId,
@@ -16040,6 +16109,7 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
    destroyPQExpBuffer(q);
    destroyPQExpBuffer(delq);
    free(qindxname);
+   free(qqindxname);
 }
 
 /*
@@ -16153,6 +16223,11 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
                              fmtId(indxinfo->dobj.name));
        }
 
+       /* Indexes can depend on extensions */
+       append_depends_on_extension(fout, q, &indxinfo->dobj,
+                                   "pg_catalog.pg_class", "INDEX",
+                                   fmtQualifiedDumpable(indxinfo));
+
        appendPQExpBuffer(delq, "ALTER TABLE ONLY %s ",
                          fmtQualifiedDumpable(tbinfo));
        appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
@@ -16632,6 +16707,7 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo)
    PQExpBuffer query;
    PQExpBuffer delqry;
    PQExpBuffer trigprefix;
+   PQExpBuffer trigidentity;
    char       *qtabname;
    char       *tgargs;
    size_t      lentgargs;
@@ -16649,13 +16725,14 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo)
    query = createPQExpBuffer();
    delqry = createPQExpBuffer();
    trigprefix = createPQExpBuffer();
+   trigidentity = createPQExpBuffer();
 
    qtabname = pg_strdup(fmtId(tbinfo->dobj.name));
 
-   appendPQExpBuffer(delqry, "DROP TRIGGER %s ",
-                     fmtId(tginfo->dobj.name));
-   appendPQExpBuffer(delqry, "ON %s;\n",
-                     fmtQualifiedDumpable(tbinfo));
+   appendPQExpBuffer(trigidentity, "%s ", fmtId(tginfo->dobj.name));
+   appendPQExpBuffer(trigidentity, "ON %s", fmtQualifiedDumpable(tbinfo));
+
+   appendPQExpBuffer(delqry, "DROP TRIGGER %s;\n", trigidentity->data);
 
    if (tginfo->tgdef)
    {
@@ -16782,6 +16859,11 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo)
        appendPQExpBufferStr(query, ");\n");
    }
 
+   /* Triggers can depend on extensions */
+   append_depends_on_extension(fout, query, &tginfo->dobj,
+                               "pg_catalog.pg_trigger", "TRIGGER",
+                               trigidentity->data);
+
    if (tginfo->tgenabled != 't' && tginfo->tgenabled != 'O')
    {
        appendPQExpBuffer(query, "\nALTER TABLE %s ",
@@ -16831,6 +16913,7 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo)
    destroyPQExpBuffer(query);
    destroyPQExpBuffer(delqry);
    destroyPQExpBuffer(trigprefix);
+   destroyPQExpBuffer(trigidentity);
    free(qtabname);
 }
 
@@ -17501,6 +17584,15 @@ getDependencies(Archive *fout)
            continue;
        }
 
+       /*
+        * For 'x' dependencies, mark the object for later; we still add the
+        * normal dependency, for possible ordering purposes.  Currently
+        * pg_dump_sort.c knows to put extensions ahead of all object types
+        * that could possibly depend on them, but this is safer.
+        */
+       if (deptype == 'x')
+           dobj->depends_on_ext = true;
+
        /*
         * Ordinarily, table rowtypes have implicit dependencies on their
         * tables.  However, for a composite type the implicit dependency goes

diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 7777171feeb444a15495ecb37d698dbf5b7ac2dc..cdf5502653a5fde3de3582c56aeae1bc9d727e01 100644 (file)
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -130,6 +130,7 @@ typedef struct _dumpableObject
    DumpComponents dump;        /* bitmask of components to dump */
    DumpComponents dump_contains;       /* as above, but for contained objects */
    bool        ext_member;     /* true if object is member of extension */
+   bool        depends_on_ext; /* true if object depends on an extension */
    DumpId     *dependencies;   /* dumpIds of objects this one depends on */
    int         nDeps;          /* number of valid dependencies */
    int         allocDeps;      /* allocated size of dependencies[] */

diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index 85fe851d2f03d4bc9dfee874d14b1aa083b09a75..415f6c9339f7ae996f4ca7f6de46398b3b9ecd76 100644 (file)
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -732,6 +732,52 @@ my %tests = (
            section_pre_data   => 1,
            section_post_data  => 1, }, },
 
+   'ALTER INDEX pkey DEPENDS ON extension' => {
+       create_order => 11,
+       create_sql =>
+'CREATE TABLE regress_pg_dump_schema.extdependtab (col1 integer primary key, col2 int);
+       CREATE INDEX ON regress_pg_dump_schema.extdependtab (col2);
+       ALTER INDEX regress_pg_dump_schema.extdependtab_col2_idx DEPENDS ON EXTENSION test_pg_dump;
+       ALTER INDEX regress_pg_dump_schema.extdependtab_pkey DEPENDS ON EXTENSION test_pg_dump;',
+       regexp => qr/^
+       \QALTER INDEX regress_pg_dump_schema.extdependtab_pkey DEPENDS ON EXTENSION test_pg_dump;\E\n
+       /xms,
+       like => {
+           binary_upgrade    => 1,
+           clean_if_exists   => 1,
+           createdb          => 1,
+           defaults          => 1,
+           no_owner          => 1,
+           no_privs          => 1,
+           section_post_data => 1,
+           schema_only       => 1, },
+       unlike => {
+           column_inserts     => 1,
+           data_only          => 1,
+           pg_dumpall_globals => 1,
+           section_data       => 1,
+           section_pre_data   => 1, }, },
+
+   'ALTER INDEX idx DEPENDS ON extension' => {
+       regexp => qr/^
+           \QALTER INDEX regress_pg_dump_schema.extdependtab_col2_idx DEPENDS ON EXTENSION test_pg_dump;\E\n
+           /xms,
+       like => {
+           binary_upgrade    => 1,
+           clean_if_exists   => 1,
+           createdb          => 1,
+           defaults          => 1,
+           no_owner          => 1,
+           no_privs          => 1,
+           section_post_data => 1,
+           schema_only       => 1, },
+       unlike => {
+           column_inserts     => 1,
+           data_only          => 1,
+           pg_dumpall_globals => 1,
+           section_data       => 1,
+           section_pre_data   => 1, }, },
+
    # Objects not included in extension, part of schema created by extension
    'CREATE TABLE regress_pg_dump_schema.external_tab' => {
        create_order => 4,