I was toying with a couple of ideas that would involve changing the
storage of sequences. (Say, for the sake of discussion, removing the
problematic/useless sequence_name field.) This would cause problems for
pg_upgrade, because pg_upgrade copies the "heap" storage of sequences
like it does for normal tables, and we have no facilities for effecting
any changes during that.
There was a previous discussion in the early days of pg_migrator, which
resulted in the current behavior:
https://www.postgresql.org/message-id/flat/20090713220112.GF7933%40klana.box
This also alluded to what I think was the last change in the sequence
storage format (10a3471bed7b57fb986a5be8afdee5f0dda419de) between
versions 8.3 and 8.4. How did pg_upgrade handle that?
I think the other solution mentioned in that thread would also work:
Have pg_upgrade treat sequences more like system catalogs, whose format
changes between major releases, and transferred them via the
dump/restore route. So instead of copying the disk files, issue a
setval call, and the sequence should be all set up.
Am I missing anything?
Attached is a rough patch set that would implement that.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 0c8f9bb630f48e83dc4dbe36e742db8e20f6b523 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Tue, 23 Aug 2016 12:00:00 -0400
Subject: [PATCH 1/3] pg_dump: Separate table data and sequence data object
types
---
src/bin/pg_dump/pg_dump.c | 11 +++++++----
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/pg_dump_sort.c | 7 +++++++
3 files changed, 15 insertions(+), 4 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a5c2d09..160bc41 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2133,6 +2133,8 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
if (tbinfo->relkind == RELKIND_MATVIEW)
tdinfo->dobj.objType = DO_REFRESH_MATVIEW;
+ else if (tbinfo->relkind == RELKIND_SEQUENCE)
+ tdinfo->dobj.objType = DO_SEQUENCE_DATA;
else
tdinfo->dobj.objType = DO_TABLE_DATA;
@@ -9382,11 +9384,11 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
case DO_TRANSFORM:
dumpTransform(fout, (TransformInfo *) dobj);
break;
+ case DO_SEQUENCE_DATA:
+ dumpSequenceData(fout, (TableDataInfo *) dobj);
+ break;
case DO_TABLE_DATA:
- if (((TableDataInfo *) dobj)->tdtable->relkind == RELKIND_SEQUENCE)
- dumpSequenceData(fout, (TableDataInfo *) dobj);
- else
- dumpTableData(fout, (TableDataInfo *) dobj);
+ dumpTableData(fout, (TableDataInfo *) dobj);
break;
case DO_DUMMY_TYPE:
/* table rowtypes and array types are never dumped separately */
@@ -17482,6 +17484,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
addObjectDependency(preDataBound, dobj->dumpId);
break;
case DO_TABLE_DATA:
+ case DO_SEQUENCE_DATA:
case DO_BLOB_DATA:
/* Data objects: must come between the boundaries */
addObjectDependency(dobj, preDataBound->dumpId);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 2bfa2d9..6cc78d1 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -63,6 +63,7 @@ typedef enum
DO_PROCLANG,
DO_CAST,
DO_TABLE_DATA,
+ DO_SEQUENCE_DATA,
DO_DUMMY_TYPE,
DO_TSPARSER,
DO_TSDICT,
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index d87f08d..9ca3d2b 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -60,6 +60,7 @@ static const int oldObjectTypePriority[] =
2, /* DO_PROCLANG */
2, /* DO_CAST */
11, /* DO_TABLE_DATA */
+ 11, /* DO_SEQUENCE_DATA */
7, /* DO_DUMMY_TYPE */
4, /* DO_TSPARSER */
4, /* DO_TSDICT */
@@ -111,6 +112,7 @@ static const int newObjectTypePriority[] =
2, /* DO_PROCLANG */
10, /* DO_CAST */
23, /* DO_TABLE_DATA */
+ 23, /* DO_SEQUENCE_DATA */
19, /* DO_DUMMY_TYPE */
12, /* DO_TSPARSER */
14, /* DO_TSDICT */
@@ -1433,6 +1435,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
"TABLE DATA %s (ID %d OID %u)",
obj->name, obj->dumpId, obj->catId.oid);
return;
+ case DO_SEQUENCE_DATA:
+ snprintf(buf, bufsize,
+ "SEQUENCE DATA %s (ID %d OID %u)",
+ obj->name, obj->dumpId, obj->catId.oid);
+ return;
case DO_DUMMY_TYPE:
snprintf(buf, bufsize,
"DUMMY TYPE %s (ID %d OID %u)",
--
2.9.3
From 26325789ef3cb0e898d94f06b395ae4c64e3b2e9 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Tue, 23 Aug 2016 12:00:00 -0400
Subject: [PATCH 2/3] pg_dump: Add --sequence-data option
---
src/bin/pg_dump/pg_backup.h | 2 ++
src/bin/pg_dump/pg_backup_archiver.c | 6 +++++-
src/bin/pg_dump/pg_dump.c | 22 ++++++++++++++++++++++
3 files changed, 29 insertions(+), 1 deletion(-)
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 4afa92f..2fdd364 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -117,6 +117,7 @@ typedef struct _restoreOptions
bool *idWanted; /* array showing which dump IDs to emit */
int enable_row_security;
+ int sequence_data;
} RestoreOptions;
typedef struct _dumpOptions
@@ -150,6 +151,7 @@ typedef struct _dumpOptions
int outputNoTablespaces;
int use_setsessauth;
int enable_row_security;
+ int sequence_data;
/* default, if no "inclusion" switches appear, is to dump everything */
bool include_everything;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 05bdbdb..d8bee2e 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -166,6 +166,7 @@ dumpOptionsFromRestoreOptions(RestoreOptions *ropt)
dopt->lockWaitTimeout = ropt->lockWaitTimeout;
dopt->include_everything = ropt->include_everything;
dopt->enable_row_security = ropt->enable_row_security;
+ dopt->sequence_data = ropt->sequence_data;
return dopt;
}
@@ -2826,7 +2827,10 @@ _tocEntryRequired(TocEntry *te, teSection curSection, RestoreOptions *ropt)
/* Mask it if we only want schema */
if (ropt->schemaOnly)
- res = res & REQ_SCHEMA;
+ {
+ if (!(ropt->sequence_data && strcmp(te->desc, "SEQUENCE SET") == 0))
+ res = res & REQ_SCHEMA;
+ }
/* Mask it if we only want data */
if (ropt->dataOnly)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 160bc41..7ff957a 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -220,6 +220,7 @@ static void addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
static void getDomainConstraints(Archive *fout, TypeInfo *tyinfo);
static void getTableData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, bool oids);
+static void getSequenceData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, bool oids);
static void makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids);
static void buildMatViewRefreshDependencies(Archive *fout);
static void getTableDataFKConstraints(void);
@@ -342,6 +343,7 @@ main(int argc, char **argv)
{"quote-all-identifiers", no_argument, "e_all_identifiers, 1},
{"role", required_argument, NULL, 3},
{"section", required_argument, NULL, 5},
+ {"sequence-data", no_argument, &dopt.sequence_data, 1},
{"serializable-deferrable", no_argument, &dopt.serializable_deferrable, 1},
{"snapshot", required_argument, NULL, 6},
{"strict-names", no_argument, &strict_names, 1},
@@ -752,6 +754,9 @@ main(int argc, char **argv)
getTableDataFKConstraints();
}
+ if (dopt.schemaOnly && dopt.sequence_data)
+ getSequenceData(&dopt, tblinfo, numTables, dopt.oids);
+
if (dopt.outputBlobs)
getBlobs(fout);
@@ -835,6 +840,7 @@ main(int argc, char **argv)
ropt->lockWaitTimeout = dopt.lockWaitTimeout;
ropt->include_everything = dopt.include_everything;
ropt->enable_row_security = dopt.enable_row_security;
+ ropt->sequence_data = dopt.sequence_data;
if (compressLevel == -1)
ropt->compression = 0;
@@ -2094,6 +2100,22 @@ getTableData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, bool oids)
}
/*
+ * getSequenceData -
+ * set up dumpable objects representing the contents of sequences
+ */
+static void
+getSequenceData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, bool oids)
+{
+ int i;
+
+ for (i = 0; i < numTables; i++)
+ {
+ if (tblinfo[i].dobj.dump & DUMP_COMPONENT_DATA && tblinfo[i].relkind == RELKIND_SEQUENCE)
+ makeTableDataInfo(dopt, &(tblinfo[i]), oids);
+ }
+}
+
+/*
* Make a dumpable object for the data of this specific table
*
* Note: we make a TableDataInfo if and only if we are going to dump the
--
2.9.3
From 6a1f0500dee915e791eb36db502ff6e6bc5ca750 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Tue, 23 Aug 2016 12:00:00 -0400
Subject: [PATCH 3/3] pg_upgrade: Skip copying sequence files
Have pg_dump/pg_restore do it.
---
src/bin/pg_upgrade/dump.c | 2 +-
src/bin/pg_upgrade/info.c | 2 +-
2 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/bin/pg_upgrade/dump.c b/src/bin/pg_upgrade/dump.c
index 81fb725..97f7d66 100644
--- a/src/bin/pg_upgrade/dump.c
+++ b/src/bin/pg_upgrade/dump.c
@@ -62,7 +62,7 @@ generate_old_dump(void)
snprintf(log_file_name, sizeof(log_file_name), DB_DUMP_LOG_FILE_MASK, old_db->db_oid);
parallel_exec_prog(log_file_name, NULL,
- "\"%s/pg_dump\" %s --schema-only --quote-all-identifiers "
+ "\"%s/pg_dump\" %s --schema-only --sequence-data --quote-all-identifiers "
"--binary-upgrade --format=custom %s --file=\"%s\" %s",
new_cluster.bindir, cluster_conn_opts(&old_cluster),
log_opts.verbose ? "--verbose" : "",
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index 1200c7f..6ea5720 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -444,7 +444,7 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo)
" SELECT c.oid, 0::oid, 0::oid "
" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n "
" ON c.relnamespace = n.oid "
- " WHERE relkind IN ('r', 'm', 'S') AND "
+ " WHERE relkind IN ('r', 'm') AND "
/* exclude possible orphaned temp tables */
" ((n.nspname !~ '^pg_temp_' AND "
" n.nspname !~ '^pg_toast_temp_' AND "
--
2.9.3
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
