Re: pg_migrator issues - Mailing list pgsql-hackers

Bruce Momjian wrote:
> pg_migrator has become more popular recently, so it seems time to look
> at some enhancements that would improve pg_migrator.  None of these are
> required, but rather changes that would be nice to have:
>
> 1)  Right now pg_migrator preserves relfilenodes for TOAST files because
> this is required for proper migration.  Now that we have shown that
> strategically-placed global variables with a server-side function to set
> them is a viable solution, it would be nice to preserve all relfilenodes
> from the old server.  This would simplify pg_migrator by no long
> requiring place-holder relfilenodes or the renaming of TOAST files.  A
> simpler solution would just be to allow TOAST table creation to
> automatically remove placeholder files and create specified relfilenodes
> via global variables.

Attached is a patch that implements #1 above by preserving all
relfilenodes, with pg_dump support.   It uses the same method I used for
preserving pg_type/pg_enum.  I have tested this on the regression
database and it successfully preserved all relfilenodes.

This patch also removes the 'force' parameter in toast functions that
Tom added for 8.4 --- it is no longer needed.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/backend/catalog/heap.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.364
diff -c -c -r1.364 heap.c
*** src/backend/catalog/heap.c    2 Jan 2010 16:57:36 -0000    1.364
--- src/backend/catalog/heap.c    5 Jan 2010 02:37:21 -0000
***************
*** 96,101 ****
--- 96,104 ----
                 char *relname);
  static List *insert_ordered_unique_oid(List *list, Oid datum);

+ Oid binary_upgrade_next_heap_relfilenode = InvalidOid;
+ Oid binary_upgrade_next_toast_relfilenode = InvalidOid;
+

  /* ----------------------------------------------------------------
   *                XXX UGLY HARD CODED BADNESS FOLLOWS XXX
***************
*** 942,956 ****
                       errmsg("only shared relations can be placed in pg_global tablespace")));
      }

!     /*
!      * Allocate an OID for the relation, unless we were told what to use.
!      *
!      * The OID will be the relfilenode as well, so make sure it doesn't
!      * collide with either pg_class OIDs or existing physical files.
!      */
!     if (!OidIsValid(relid))
          relid = GetNewRelFileNode(reltablespace, shared_relation,
                                    pg_class_desc);

      /*
       * Determine the relation's initial permissions.
--- 945,973 ----
                       errmsg("only shared relations can be placed in pg_global tablespace")));
      }

!     if ((relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE) &&
!         OidIsValid(binary_upgrade_next_heap_relfilenode))
!     {
!         relid = binary_upgrade_next_heap_relfilenode;
!         binary_upgrade_next_heap_relfilenode = InvalidOid;
!     }
!     else if (relkind == RELKIND_TOASTVALUE &&
!         OidIsValid(binary_upgrade_next_toast_relfilenode))
!     {
!         relid = binary_upgrade_next_toast_relfilenode;
!         binary_upgrade_next_toast_relfilenode = InvalidOid;
!     }
!     else if (!OidIsValid(relid))
!     {
!         /*
!          * Allocate an OID for the relation, unless we were told what to use.
!          *
!          * The OID will be the relfilenode as well, so make sure it doesn't
!          * collide with either pg_class OIDs or existing physical files.
!          */
          relid = GetNewRelFileNode(reltablespace, shared_relation,
                                    pg_class_desc);
+     }

      /*
       * Determine the relation's initial permissions.
Index: src/backend/catalog/index.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/index.c,v
retrieving revision 1.328
diff -c -c -r1.328 index.c
*** src/backend/catalog/index.c    2 Jan 2010 16:57:36 -0000    1.328
--- src/backend/catalog/index.c    5 Jan 2010 02:37:21 -0000
***************
*** 79,84 ****
--- 79,87 ----
                  tups_inserted;
  } v_i_state;

+ /* For simple relation creation, this is the toast index relfilenode */
+ Oid binary_upgrade_next_index_relfilenode = InvalidOid;
+
  /* non-export function prototypes */
  static TupleDesc ConstructTupleDescriptor(Relation heapRelation,
                           IndexInfo *indexInfo,
***************
*** 640,654 ****
                                              accessMethodObjectId,
                                              classObjectId);

!     /*
!      * Allocate an OID for the index, unless we were told what to use.
!      *
!      * The OID will be the relfilenode as well, so make sure it doesn't
!      * collide with either pg_class OIDs or existing physical files.
!      */
!     if (!OidIsValid(indexRelationId))
          indexRelationId = GetNewRelFileNode(tableSpaceId, shared_relation,
                                              pg_class);

      /*
       * create the index relation's relcache entry and physical disk file. (If
--- 643,664 ----
                                              accessMethodObjectId,
                                              classObjectId);

!     if (OidIsValid(binary_upgrade_next_index_relfilenode))
!     {
!         indexRelationId = binary_upgrade_next_index_relfilenode;
!         binary_upgrade_next_index_relfilenode = InvalidOid;
!     }
!     else if (!OidIsValid(indexRelationId))
!     {
!         /*
!          * Allocate an OID for the index, unless we were told what to use.
!          *
!          * The OID will be the relfilenode as well, so make sure it doesn't
!          * collide with either pg_class OIDs or existing physical files.
!          */
          indexRelationId = GetNewRelFileNode(tableSpaceId, shared_relation,
                                              pg_class);
+     }

      /*
       * create the index relation's relcache entry and physical disk file. (If
Index: src/backend/catalog/toasting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/toasting.c,v
retrieving revision 1.26
diff -c -c -r1.26 toasting.c
*** src/backend/catalog/toasting.c    2 Jan 2010 16:57:36 -0000    1.26
--- src/backend/catalog/toasting.c    5 Jan 2010 02:37:21 -0000
***************
*** 32,53 ****
  #include "utils/syscache.h"

  Oid binary_upgrade_next_pg_type_toast_oid = InvalidOid;

  static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
!                    Datum reloptions, bool force);
  static bool needs_toast_table(Relation rel);


  /*
   * AlterTableCreateToastTable
   *        If the table needs a toast table, and doesn't already have one,
!  *        then create a toast table for it.  (With the force option, make
!  *        a toast table even if it appears unnecessary.)
!  *
!  * The caller can also specify the OID to be used for the toast table.
!  * Usually, toastOid should be InvalidOid to allow a free OID to be assigned.
!  * (This option, as well as the force option, is not used by core Postgres,
!  * but is provided to support pg_migrator.)
   *
   * reloptions for the toast table can be passed, too.  Pass (Datum) 0
   * for default reloptions.
--- 32,48 ----
  #include "utils/syscache.h"

  Oid binary_upgrade_next_pg_type_toast_oid = InvalidOid;
+ extern Oid binary_upgrade_next_toast_relfilenode;

  static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
!                    Datum reloptions);
  static bool needs_toast_table(Relation rel);


  /*
   * AlterTableCreateToastTable
   *        If the table needs a toast table, and doesn't already have one,
!  *        then create a toast table for it.
   *
   * reloptions for the toast table can be passed, too.  Pass (Datum) 0
   * for default reloptions.
***************
*** 57,64 ****
   * to end with CommandCounterIncrement if it makes any changes.
   */
  void
! AlterTableCreateToastTable(Oid relOid, Oid toastOid,
!                            Datum reloptions, bool force)
  {
      Relation    rel;

--- 52,58 ----
   * to end with CommandCounterIncrement if it makes any changes.
   */
  void
! AlterTableCreateToastTable(Oid relOid, Datum reloptions)
  {
      Relation    rel;

***************
*** 70,76 ****
      rel = heap_open(relOid, AccessExclusiveLock);

      /* create_toast_table does all the work */
!     (void) create_toast_table(rel, toastOid, InvalidOid, reloptions, force);

      heap_close(rel, NoLock);
  }
--- 64,70 ----
      rel = heap_open(relOid, AccessExclusiveLock);

      /* create_toast_table does all the work */
!     (void) create_toast_table(rel, InvalidOid, InvalidOid, reloptions);

      heap_close(rel, NoLock);
  }
***************
*** 96,102 ****
                          relName)));

      /* create_toast_table does all the work */
!     if (!create_toast_table(rel, toastOid, toastIndexOid, (Datum) 0, false))
          elog(ERROR, "\"%s\" does not require a toast table",
               relName);

--- 90,96 ----
                          relName)));

      /* create_toast_table does all the work */
!     if (!create_toast_table(rel, toastOid, toastIndexOid, (Datum) 0))
          elog(ERROR, "\"%s\" does not require a toast table",
               relName);

***************
*** 108,119 ****
   * create_toast_table --- internal workhorse
   *
   * rel is already opened and exclusive-locked
!  * toastOid and toastIndexOid are normally InvalidOid, but
!  * either or both can be nonzero to specify caller-assigned OIDs
   */
  static bool
! create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
!                    Datum reloptions, bool force)
  {
      Oid            relOid = RelationGetRelid(rel);
      HeapTuple    reltup;
--- 102,112 ----
   * create_toast_table --- internal workhorse
   *
   * rel is already opened and exclusive-locked
!  * toastOid and toastIndexOid are normally InvalidOid, but during
!  * bootstrap they can be nonzero to specify hand-assigned OIDs
   */
  static bool
! create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptions)
  {
      Oid            relOid = RelationGetRelid(rel);
      HeapTuple    reltup;
***************
*** 152,163 ****

      /*
       * Check to see whether the table actually needs a TOAST table.
!      *
!      * Caller can optionally override this check.  (Note: at present no
!      * callers in core Postgres do so, but this option is needed by
!      * pg_migrator.)
       */
!     if (!force && !needs_toast_table(rel))
          return false;

      /*
--- 145,154 ----

      /*
       * Check to see whether the table actually needs a TOAST table.
!      * If the relfilenode is specified, force toast file creation.
       */
!     if (!needs_toast_table(rel) &&
!         !OidIsValid(binary_upgrade_next_toast_relfilenode))
          return false;

      /*
Index: src/backend/commands/cluster.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/cluster.c,v
retrieving revision 1.189
diff -c -c -r1.189 cluster.c
*** src/backend/commands/cluster.c    2 Jan 2010 16:57:37 -0000    1.189
--- src/backend/commands/cluster.c    5 Jan 2010 02:37:21 -0000
***************
*** 743,749 ****
          if (isNull)
              reloptions = (Datum) 0;
      }
!     AlterTableCreateToastTable(OIDNewHeap, InvalidOid, reloptions, false);

      if (OidIsValid(toastid))
          ReleaseSysCache(tuple);
--- 743,749 ----
          if (isNull)
              reloptions = (Datum) 0;
      }
!     AlterTableCreateToastTable(OIDNewHeap, reloptions);

      if (OidIsValid(toastid))
          ReleaseSysCache(tuple);
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.313
diff -c -c -r1.313 tablecmds.c
*** src/backend/commands/tablecmds.c    2 Jan 2010 16:57:37 -0000    1.313
--- src/backend/commands/tablecmds.c    5 Jan 2010 02:37:21 -0000
***************
*** 2614,2621 ****
              (tab->subcmds[AT_PASS_ADD_COL] ||
               tab->subcmds[AT_PASS_ALTER_TYPE] ||
               tab->subcmds[AT_PASS_COL_ATTRS]))
!             AlterTableCreateToastTable(tab->relid, InvalidOid,
!                                        (Datum) 0, false);
      }
  }

--- 2614,2620 ----
              (tab->subcmds[AT_PASS_ADD_COL] ||
               tab->subcmds[AT_PASS_ALTER_TYPE] ||
               tab->subcmds[AT_PASS_COL_ATTRS]))
!             AlterTableCreateToastTable(tab->relid, (Datum) 0);
      }
  }

Index: src/backend/executor/execMain.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.339
diff -c -c -r1.339 execMain.c
*** src/backend/executor/execMain.c    2 Jan 2010 16:57:40 -0000    1.339
--- src/backend/executor/execMain.c    5 Jan 2010 02:37:22 -0000
***************
*** 2194,2200 ****

      (void) heap_reloptions(RELKIND_TOASTVALUE, reloptions, true);

!     AlterTableCreateToastTable(intoRelationId, InvalidOid, reloptions, false);

      /*
       * And open the constructed table for writing.
--- 2194,2200 ----

      (void) heap_reloptions(RELKIND_TOASTVALUE, reloptions, true);

!     AlterTableCreateToastTable(intoRelationId, reloptions);

      /*
       * And open the constructed table for writing.
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.326
diff -c -c -r1.326 utility.c
*** src/backend/tcop/utility.c    2 Jan 2010 16:57:53 -0000    1.326
--- src/backend/tcop/utility.c    5 Jan 2010 02:37:22 -0000
***************
*** 491,504 ****
                                                              "toast",
                                                              validnsps,
                                                              true, false);
!                         (void) heap_reloptions(RELKIND_TOASTVALUE,
!                                                toast_options,
                                                 true);

!                         AlterTableCreateToastTable(relOid,
!                                                    InvalidOid,
!                                                    toast_options,
!                                                    false);
                      }
                      else
                      {
--- 491,500 ----
                                                              "toast",
                                                              validnsps,
                                                              true, false);
!                         (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options,
                                                 true);

!                         AlterTableCreateToastTable(relOid, toast_options);
                      }
                      else
                      {
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.564
diff -c -c -r1.564 pg_dump.c
*** src/bin/pg_dump/pg_dump.c    2 Jan 2010 16:57:59 -0000    1.564
--- src/bin/pg_dump/pg_dump.c    5 Jan 2010 02:37:22 -0000
***************
*** 200,206 ****
                      PQExpBuffer upgrade_buffer, Oid pg_type_oid);
  static bool binary_upgrade_set_type_oids_by_rel_oid(
                      PQExpBuffer upgrade_buffer, Oid pg_rel_oid);
! static void binary_upgrade_clear_pg_type_toast_oid(PQExpBuffer upgrade_buffer);
  static const char *getAttrName(int attrnum, TableInfo *tblInfo);
  static const char *fmtCopyColumnList(const TableInfo *ti);
  static void do_sql_command(PGconn *conn, const char *query);
--- 200,207 ----
                      PQExpBuffer upgrade_buffer, Oid pg_type_oid);
  static bool binary_upgrade_set_type_oids_by_rel_oid(
                      PQExpBuffer upgrade_buffer, Oid pg_rel_oid);
! static void binary_upgrade_set_relfilenodes(PQExpBuffer upgrade_buffer,
!                     Oid pg_class_oid, bool is_index);
  static const char *getAttrName(int attrnum, TableInfo *tblInfo);
  static const char *fmtCopyColumnList(const TableInfo *ti);
  static void do_sql_command(PGconn *conn, const char *query);
***************
*** 2289,2309 ****
  }

  static void
! binary_upgrade_clear_pg_type_toast_oid(PQExpBuffer upgrade_buffer)
  {
!     /*
!      *    One complexity is that while the heap might now have a TOAST table,
!      *    the TOAST table might have been created long after creation when
!      *    the table was loaded with wide data.  For that reason, we clear
!      *    binary_upgrade_set_next_pg_type_toast_oid so it is not reused
!      *    by a later table.  Logically any later creation that needs a TOAST
!      *    table should have its own TOAST pg_type oid, but we are cautious.
!      */
!     appendPQExpBuffer(upgrade_buffer,
!         "\n-- For binary upgrade, clear toast oid because it might not have been needed\n");
      appendPQExpBuffer(upgrade_buffer,
!         "SELECT binary_upgrade.set_next_pg_type_oid('%u'::pg_catalog.oid);\n\n",
!         InvalidOid);
  }

  /*
--- 2290,2367 ----
  }

  static void
! binary_upgrade_set_relfilenodes(PQExpBuffer upgrade_buffer, Oid pg_class_oid,
!                                 bool is_index)
  {
!     PQExpBuffer upgrade_query = createPQExpBuffer();
!     int            ntups;
!     PGresult   *upgrade_res;
!     Oid            pg_class_relfilenode;
!     Oid            pg_class_reltoastrelid;
!     Oid            pg_class_reltoastidxid;
!
!     appendPQExpBuffer(upgrade_query,
!                       "SELECT c.relfilenode, c.reltoastrelid, t.reltoastidxid "
!                       "FROM pg_catalog.pg_class c LEFT JOIN "
!                       "pg_catalog.pg_class t ON (c.reltoastrelid = t.oid) "
!                       "WHERE c.oid = '%u'::pg_catalog.oid;",
!                       pg_class_oid);
!
!     upgrade_res = PQexec(g_conn, upgrade_query->data);
!     check_sql_result(upgrade_res, g_conn, upgrade_query->data, PGRES_TUPLES_OK);
!
!     /* Expecting a single result only */
!     ntups = PQntuples(upgrade_res);
!     if (ntups != 1)
!     {
!         write_msg(NULL, ngettext("query returned %d row instead of one: %s\n",
!                                "query returned %d rows instead of one: %s\n",
!                                  ntups),
!                   ntups, upgrade_query->data);
!         exit_nicely();
!     }
!
!     pg_class_relfilenode = atooid(PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "relfilenode")));
!     pg_class_reltoastrelid = atooid(PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "reltoastrelid")));
!     pg_class_reltoastidxid = atooid(PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "reltoastidxid")));
!
      appendPQExpBuffer(upgrade_buffer,
!                         "\n-- For binary upgrade, must preserve relfilenodes\n");
!
!     if (!is_index)
!         appendPQExpBuffer(upgrade_buffer,
!             "SELECT binary_upgrade.set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
!             pg_class_relfilenode);
!     else
!         appendPQExpBuffer(upgrade_buffer,
!             "SELECT binary_upgrade.set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
!             pg_class_relfilenode);
!
!     if (OidIsValid(pg_class_reltoastrelid))
!     {
!         /*
!          *  One complexity is that the table definition might not require
!          *    the creation of a TOAST table, and the TOAST table might have
!          *    been created long after table creation, when the table was
!          *    loaded with wide data.  By setting the TOAST relfilenode we
!          *    force creation of the TOAST heap and TOAST index by the
!          *    backend so we can cleanly migrate the files during binary
!          *    migration.
!          */
!
!         appendPQExpBuffer(upgrade_buffer,
!             "SELECT binary_upgrade.set_next_toast_relfilenode('%u'::pg_catalog.oid);\n",
!             pg_class_reltoastrelid);
!
!         /* every toast table has an index */
!         appendPQExpBuffer(upgrade_buffer,
!             "SELECT binary_upgrade.set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
!             pg_class_reltoastidxid);
!     }
!     appendPQExpBuffer(upgrade_buffer, "\n");
!
!     PQclear(upgrade_res);
!     destroyPQExpBuffer(upgrade_query);
  }

  /*
***************
*** 10480,10485 ****
--- 10538,10546 ----
          appendPQExpBuffer(delq, "%s;\n",
                            fmtId(tbinfo->dobj.name));

+         if (binary_upgrade)
+             binary_upgrade_set_relfilenodes(q, tbinfo->dobj.catId.oid, false);
+
          appendPQExpBuffer(q, "CREATE TABLE %s (",
                            fmtId(tbinfo->dobj.name));
          actual_atts = 0;
***************
*** 10781,10789 ****
          }
      }

-     if (binary_upgrade && toast_set)
-         binary_upgrade_clear_pg_type_toast_oid(q);
-
      ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
                   tbinfo->dobj.name,
                   tbinfo->dobj.namespace->dobj.name,
--- 10842,10847 ----
***************
*** 10926,10931 ****
--- 10984,10992 ----
       */
      if (indxinfo->indexconstraint == 0)
      {
+         if (binary_upgrade)
+             binary_upgrade_set_relfilenodes(q, indxinfo->dobj.catId.oid, true);
+
          /* Plain secondary index */
          appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);

***************
*** 11006,11011 ****
--- 11067,11075 ----
              exit_nicely();
          }

+         if (binary_upgrade && !coninfo->condef)
+             binary_upgrade_set_relfilenodes(q, indxinfo->dobj.catId.oid, true);
+
          appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
                            fmtId(tbinfo->dobj.name));
          appendPQExpBuffer(q, "    ADD CONSTRAINT %s ",
***************
*** 11416,11422 ****
--- 11480,11489 ----
          resetPQExpBuffer(query);

          if (binary_upgrade)
+         {
+             binary_upgrade_set_relfilenodes(query, tbinfo->dobj.catId.oid, false);
              binary_upgrade_set_type_oids_by_rel_oid(query, tbinfo->dobj.catId.oid);
+         }

          appendPQExpBuffer(query,
                            "CREATE SEQUENCE %s\n",
Index: src/include/catalog/toasting.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/toasting.h,v
retrieving revision 1.11
diff -c -c -r1.11 toasting.h
*** src/include/catalog/toasting.h    2 Jan 2010 16:58:02 -0000    1.11
--- src/include/catalog/toasting.h    5 Jan 2010 02:37:22 -0000
***************
*** 17,24 ****
  /*
   * toasting.c prototypes
   */
! extern void AlterTableCreateToastTable(Oid relOid, Oid toastOid,
!                            Datum reloptions, bool force);
  extern void BootstrapToastTable(char *relName,
                      Oid toastOid, Oid toastIndexOid);

--- 17,23 ----
  /*
   * toasting.c prototypes
   */
! extern void AlterTableCreateToastTable(Oid relOid, Datum reloptions);
  extern void BootstrapToastTable(char *relName,
                      Oid toastOid, Oid toastIndexOid);