Re: [GENERAL] pg_migrator not setting values of sequences?

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Bruce Momjian wrote:
> >
> > > Something is certainly wrong.  Did we change sequence table format from
> > > 8.3 to 8.4?
> >
> > 8.3 does not have start_value.
>
> Looking at an invalidly-migrated sequence's columns:
>
>     regression=> \d serialtest_f2_foo
>           Sequence "public.serialtest_f2_foo"
>         Column     |  Type   |        Value
>     ---------------+---------+---------------------
>      sequence_name | name    | serialtest_f2_foo
>      last_value    | bigint  | 3
>      start_value   | bigint  | 1
>      increment_by  | bigint  | 9223372036854775807
>      max_value     | bigint  | 1
>      min_value     | bigint  | 1
>      cache_value   | bigint  | 0
>      log_cnt       | bigint  | 25387551686912
>      is_cycled     | boolean | f
>      is_called     | boolean |
>
> Should pg_migrator just pull the misaligned values and do an ALTER
> SEQUENCE/seval() to fix it, or create a script to do that?

I have applied the attached patch to pg_migrator that will properly
handle migrating sequences;  it should apply cleanly to pg_migrator
8.4.1 alpha 1.

What I found during research is that pg_dump --schema-only already
creates the sequence:

    CREATE SEQUENCE check_seq
        START WITH 1
        INCREMENT BY 1
        NO MAXVALUE
        NO MINVALUE
        CACHE 1;

What it does not do is to call setval() to set the sequence value and
'is_called'.  What I did was to _not_ migrate the sequence file, but
rather create a script from the old cluster that uses setval() to set
the sequence values.  This can be safely run by pg_migrator
unconditionally because we are not migrating the sequence files, even in
link mode.

This solves the sequence migration problem, with no changes to pg_dump.

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

  + If your life is a hard drive, Christ can be your backup. +
? tools
? log
? src/pg_migrator
Index: src/info.c
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/info.c,v
retrieving revision 1.18
diff -c -r1.18 info.c
*** src/info.c    14 Jul 2009 02:34:59 -0000    1.18
--- src/info.c    20 Jul 2009 18:55:48 -0000
***************
*** 322,353 ****
      int            i_oid = -1;
      int            i_relfilenode = -1;
      int            i_reltoastrelid = -1;

!     res = executeQueryOrDie(ctx, conn,
!                             "SELECT DISTINCT c.oid, n.nspname, c.relname, "
!                             "    c.relfilenode, c.reltoastrelid, "
!                             "    t.spclocation,n.nspname "
!                             "FROM (pg_catalog.pg_class c JOIN "
!                             "        pg_catalog.pg_namespace n "
!                             "    ON c.relnamespace = n.oid) "
!                             "   LEFT OUTER JOIN pg_catalog.pg_tablespace t "
!                             "    ON c.reltablespace = t.oid "
!                             "WHERE relnamespace NOT IN "
!                             "    ( "
!                             "        SELECT oid "
!                             "        FROM pg_catalog.pg_namespace "
!                             "        WHERE nspname IN "
!                             "            ('pg_catalog', 'information_schema') "
!                             "    ) "
!                             "    AND c.oid >= "
!                             STRINGIFY(FirstNormalObjectId) " "
!                             "    AND "
!                             "    (relkind = 'r' OR relkind = 't' OR "
!                             "     relkind = 'i' OR relkind = 'S') "
!                             "GROUP BY  c.oid, n.nspname, c.relname, c.relfilenode,"
!                             "            c.reltoastrelid, t.spclocation, "
!                             "            n.nspname "
!                             "ORDER BY n.nspname, c.relname;");

      ntups = PQntuples(res);

--- 322,360 ----
      int            i_oid = -1;
      int            i_relfilenode = -1;
      int            i_reltoastrelid = -1;
+     char        query[QUERY_ALLOC];

!     snprintf(query, sizeof(query),
!                 "SELECT DISTINCT c.oid, n.nspname, c.relname, "
!                 "    c.relfilenode, c.reltoastrelid, "
!                 "    t.spclocation,n.nspname "
!                 "FROM (pg_catalog.pg_class c JOIN "
!                 "        pg_catalog.pg_namespace n "
!                 "    ON c.relnamespace = n.oid) "
!                 "   LEFT OUTER JOIN pg_catalog.pg_tablespace t "
!                 "    ON c.reltablespace = t.oid "
!                 "WHERE relnamespace NOT IN "
!                 "    ( "
!                 "        SELECT oid "
!                 "        FROM pg_catalog.pg_namespace "
!                 "        WHERE nspname IN "
!                 "            ('pg_catalog', 'information_schema') "
!                 "    ) "
!                 "    AND c.oid >= "
!                 STRINGIFY(FirstNormalObjectId) " "
!                 "    AND "
!                 "    (relkind = 'r' OR relkind = 't' OR "
!                 "     relkind = 'i'%s)"
!                 "GROUP BY  c.oid, n.nspname, c.relname, c.relfilenode,"
!                 "            c.reltoastrelid, t.spclocation, "
!                 "            n.nspname "
!                 "ORDER BY n.nspname, c.relname;",
!                 /* see the comment at the top of v8_3_adjust_sequences() */
!                 (GET_MAJOR_VERSION(ctx->old.pg_version) == 803 &&
!                  GET_MAJOR_VERSION(ctx->new.pg_version) > 803) ?
!                     "" : " OR relkind = 'S'");
!
!     res = executeQueryOrDie(ctx, conn, query);

      ntups = PQntuples(res);

Index: src/pg_migrator.c
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.c,v
retrieving revision 1.56
diff -c -r1.56 pg_migrator.c
*** src/pg_migrator.c    3 Jul 2009 16:46:49 -0000    1.56
--- src/pg_migrator.c    20 Jul 2009 18:55:48 -0000
***************
*** 37,43 ****
  main(int argc, char **argv)
  {
      migratorContext ctx;
!
      memset(&ctx, 0, sizeof(ctx));

      parseCommandLine(&ctx, argc, argv);
--- 37,44 ----
  main(int argc, char **argv)
  {
      migratorContext ctx;
!     char *sequence_script_file_name = NULL;
!
      memset(&ctx, 0, sizeof(ctx));

      parseCommandLine(&ctx, argc, argv);
***************
*** 78,83 ****
--- 79,92 ----
              v8_3_invalidate_hash_gin_indexes(&ctx, true, CLUSTER_OLD);
              v8_3_invalidate_bpchar_pattern_ops_indexes(&ctx, true, CLUSTER_OLD);
          }
+         else
+             /*
+              *    While we have the old server running, create the script
+              *    to properly restore its sequence values but we report this
+              *    at the end.
+              */
+             sequence_script_file_name =
+                 v8_3_create_sequence_script(&ctx, CLUSTER_OLD);
      }

      /* Looks okay so far.  Prepare the pg_dump output */
***************
*** 245,250 ****
--- 254,273 ----
          GET_MAJOR_VERSION(ctx.new.pg_version) > 803)
      {
          start_postmaster(&ctx, CLUSTER_NEW, true);
+         /* restore proper sequence values using file created from old server */
+         if (strlen(sequence_script_file_name) > 0)
+         {
+             prep_status(&ctx, "Adjusting sequences");
+             exec_prog(&ctx, true,
+                       SYSTEMQUOTE "\"%s/%s\" --set ON_ERROR_STOP=on --port %d "
+                       "-f \"%s\" --dbname template1 >> \"%s\"" SYSTEMQUOTE,
+                       ctx.new.bindir, ctx.new_psql_exe, ctx.new.port,
+                       sequence_script_file_name, ctx.logfile);
+             unlink(sequence_script_file_name);
+             check_ok(&ctx);
+         }
+         pg_free(sequence_script_file_name);
+
          v8_3_rebuild_tsvector_tables(&ctx, false, CLUSTER_NEW);
          v8_3_invalidate_hash_gin_indexes(&ctx, false, CLUSTER_NEW);
          v8_3_invalidate_bpchar_pattern_ops_indexes(&ctx, false, CLUSTER_NEW);
Index: src/pg_migrator.h
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.h,v
retrieving revision 1.63
diff -c -r1.63 pg_migrator.h
*** src/pg_migrator.h    18 Jul 2009 00:14:01 -0000    1.63
--- src/pg_migrator.h    20 Jul 2009 18:55:48 -0000
***************
*** 26,31 ****
--- 26,32 ----

  #define MAX_STRING        1024
  #define LINE_ALLOC        4096
+ #define QUERY_ALLOC        8192

  #define MIGRATOR_API_VERSION    1

***************
*** 390,392 ****
--- 391,396 ----
                              bool check_mode, Cluster whichCluster);
  void        v8_3_invalidate_bpchar_pattern_ops_indexes(migratorContext *ctx,
                              bool check_mode, Cluster whichCluster);
+ char         *v8_3_create_sequence_script(migratorContext *ctx,
+                             Cluster whichCluster);
+
Index: src/version.c
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/version.c,v
retrieving revision 1.22
diff -c -r1.22 version.c
*** src/version.c    2 Jul 2009 23:22:53 -0000    1.22
--- src/version.c    20 Jul 2009 18:55:48 -0000
***************
*** 417,423 ****
                      "| when executed by psql by the database super-user, will rebuild\n"
                      "| all tables with tsvector columns.\n\n",
                      output_path);
-
      }
      else
          check_ok(ctx);
--- 417,422 ----
***************
*** 528,534 ****
                      "| when executed by psql by the database super-user, will recreate\n"
                      "| all invalid indexes; until then, none of these indexes will be used.\n\n",
                      output_path);
-
      }
      else
          check_ok(ctx);
--- 527,532 ----
***************
*** 657,659 ****
--- 655,756 ----
      else
          check_ok(ctx);
  }
+
+
+ /*
+  * v8_3_create_sequence_script()
+  *
+  *    8.4 added the column "start_value" to all sequences.  For this reason,
+  *    we don't transfer sequence files but instead use the CREATE SEQUENCE
+  *    command from the schema dump, and use setval() to restore the sequence
+  *    value and 'is_called' from the old database.  This is safe to run
+  *    by pg_migrator because sequence files are not transfered from the old
+  *    server, even in link mode.
+  */
+ char *
+ v8_3_create_sequence_script(migratorContext *ctx, Cluster whichCluster)
+ {
+     ClusterInfo    *active_cluster = (whichCluster == CLUSTER_OLD) ?
+                     &ctx->old : &ctx->new;
+     int            dbnum;
+     FILE        *script = NULL;
+     bool        found = false;
+     char        *output_path = pg_malloc(ctx, MAXPGPATH);
+
+     snprintf(output_path, MAXPGPATH, "%s/adjust_sequences.txt", ctx->home_dir);
+
+     prep_status(ctx, "Creating script to adjust sequences");
+
+     for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
+     {
+         PGresult   *res;
+          bool        db_used = false;
+         int            ntups;
+         int            rowno;
+         int            i_nspname, i_relname;
+         DbInfo       *active_db = &active_cluster->dbarr.dbs[dbnum];
+         PGconn       *conn = connectToServer(ctx, active_db->db_name, whichCluster);
+
+         /* Find any sequences */
+         res = executeQueryOrDie(ctx, conn,
+                                 "SELECT n.nspname, c.relname "
+                                 "FROM    pg_catalog.pg_class c, "
+                                 "        pg_catalog.pg_namespace n "
+                                 "WHERE    c.relkind = 'S' AND "
+                                 "        c.relnamespace = n.oid AND "
+                                 "        n.nspname != 'pg_catalog' AND "
+                                 "        n.nspname != 'information_schema'");
+
+         ntups = PQntuples(res);
+         i_nspname = PQfnumber(res, "nspname");
+         i_relname = PQfnumber(res, "relname");
+         for (rowno = 0; rowno < ntups; rowno++)
+         {
+             PGresult   *seq_res;
+             int            i_last_value, i_is_called;
+             const char *nspname = PQgetvalue(res, rowno, i_nspname);
+             const char *relname = PQgetvalue(res, rowno, i_relname);
+
+             found = true;
+
+             if (script == NULL && (script = fopen(output_path, "w")) == NULL)
+                     pg_log(ctx, PG_FATAL, "Could not create necessary file:  %s\n", output_path);
+             if (!db_used)
+             {
+                 fprintf(script, "\\connect %s\n\n",
+                         quote_identifier(ctx, active_db->db_name));
+                 db_used = true;
+             }
+
+             /* Find the desired sequence */
+             seq_res = executeQueryOrDie(ctx, conn,
+                                     "SELECT s.last_value, s.is_called "
+                                     "FROM    %s.%s s",
+                                     quote_identifier(ctx, nspname),
+                                     quote_identifier(ctx, relname));
+
+             assert(PQntuples(seq_res) == 1);
+             i_last_value = PQfnumber(seq_res, "last_value");
+             i_is_called = PQfnumber(seq_res, "is_called");
+
+             fprintf(script, "SELECT setval('%s.%s', %s, '%s');\n",
+                     quote_identifier(ctx, nspname), quote_identifier(ctx, relname),
+                     PQgetvalue(seq_res, 0, i_last_value), PQgetvalue(seq_res, 0, i_is_called));
+             PQclear(seq_res);
+         }
+         if (db_used)
+             fprintf(script, "\n");
+
+         PQclear(res);
+
+         PQfinish(conn);
+     }
+     if (found)
+         fclose(script);
+     else    /* mark script as unused */
+         output_path[0] = '\0';
+
+     check_ok(ctx);
+
+     return output_path;
+ }