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 <br...@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;
+ }
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
