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