On 9/30/16 12:50 PM, Anastasia Lubennikova wrote: > The patches are good, no complaints. > But again, I have the same question. > I was confused, why do we always dump sequence data, > because I'd overlooked the --sequence-data key. I'd rather leave this > option, > because it's quite non intuitive behaviour... > /* dump sequence data even in schema-only mode */
Here are rebased patches. Regarding your question: The initial patch had a separate option for this behavior, which was then used by pg_upgrade. It was commented that this option is not useful outside of pg_upgrade, so it doesn't need to be exposed as a user-facing option. I agreed with that and removed the option. We can always add the option back easily if someone really wants it, but so far no use case has been presented. So I suggest we proceed with this proposal ignoring whether this option is exposed or not. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From d2b98ba5df815018dac1650134398c1bac7164a4 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <pete...@gmx.net> Date: Tue, 23 Aug 2016 12:00:00 -0400 Subject: [PATCH v3 1/2] pg_dump: Separate table and sequence data object types Instead of handling both sequence data and table data internally as "table data", handle sequences separately under a "sequence set" type. We already handled materialized view data differently, so it makes the code somewhat cleaner to handle each relation kind separately at the top level. This does not change the output format, since there already was a separate "SEQUENCE SET" archive entry type. A noticeable difference is that SEQUENCE SET entries now always appear after TABLE DATA entries. And in parallel mode there is less sorting to do, because the sequence data entries are no longer considered table data. --- src/bin/pg_dump/pg_dump.c | 11 +++++++---- src/bin/pg_dump/pg_dump.h | 1 + src/bin/pg_dump/pg_dump_sort.c | 28 +++++++++++++++++----------- 3 files changed, 25 insertions(+), 15 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 4da297f..3485cab 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -2090,6 +2090,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_SET; else tdinfo->dobj.objType = DO_TABLE_DATA; @@ -8498,11 +8500,11 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj) case DO_TRANSFORM: dumpTransform(fout, (TransformInfo *) dobj); break; + case DO_SEQUENCE_SET: + 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 */ @@ -16226,6 +16228,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs, addObjectDependency(preDataBound, dobj->dumpId); break; case DO_TABLE_DATA: + case DO_SEQUENCE_SET: 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 a60cf95..642c4d5 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_SET, 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 195b84a..5b96334 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -47,14 +47,15 @@ static const int dbObjectTypePriority[] = 11, /* DO_CONVERSION */ 18, /* DO_TABLE */ 20, /* DO_ATTRDEF */ - 27, /* DO_INDEX */ - 28, /* DO_RULE */ - 29, /* DO_TRIGGER */ - 26, /* DO_CONSTRAINT */ - 30, /* DO_FK_CONSTRAINT */ + 28, /* DO_INDEX */ + 29, /* DO_RULE */ + 30, /* DO_TRIGGER */ + 27, /* DO_CONSTRAINT */ + 31, /* DO_FK_CONSTRAINT */ 2, /* DO_PROCLANG */ 10, /* DO_CAST */ 23, /* DO_TABLE_DATA */ + 24, /* DO_SEQUENCE_SET */ 19, /* DO_DUMMY_TYPE */ 12, /* DO_TSPARSER */ 14, /* DO_TSDICT */ @@ -62,15 +63,15 @@ static const int dbObjectTypePriority[] = 15, /* DO_TSCONFIG */ 16, /* DO_FDW */ 17, /* DO_FOREIGN_SERVER */ - 31, /* DO_DEFAULT_ACL */ + 32, /* DO_DEFAULT_ACL */ 3, /* DO_TRANSFORM */ 21, /* DO_BLOB */ - 24, /* DO_BLOB_DATA */ + 25, /* DO_BLOB_DATA */ 22, /* DO_PRE_DATA_BOUNDARY */ - 25, /* DO_POST_DATA_BOUNDARY */ - 32, /* DO_EVENT_TRIGGER */ - 33, /* DO_REFRESH_MATVIEW */ - 34 /* DO_POLICY */ + 26, /* DO_POST_DATA_BOUNDARY */ + 33, /* DO_EVENT_TRIGGER */ + 34, /* DO_REFRESH_MATVIEW */ + 35 /* DO_POLICY */ }; static DumpId preDataBoundId; @@ -1345,6 +1346,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_SET: + snprintf(buf, bufsize, + "SEQUENCE SET %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.10.2
>From bf17e1aface2dbdcbdb72cbdaa45ca5bf8e725f0 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <pete...@gmx.net> Date: Tue, 23 Aug 2016 12:00:00 -0400 Subject: [PATCH v3 2/2] pg_upgrade: Upgrade sequence data via pg_dump Previously, pg_upgrade migrated sequence data like tables by copying the on-disk file. This does not allow any changes in the on-disk format for sequences. It's simpler to just have pg_dump set the new sequence values as it normally does. To do that, create a hidden submode in pg_dump that dumps sequence data even when a schema-only dump is requested, and trigger that submode in binary upgrade mode. (This new submode could easily be exposed as a command-line option, but it has limited use outside of pg_dump and would probably cause some confusion, so we don't do that at this time.) --- src/bin/pg_dump/pg_backup.h | 3 +++ src/bin/pg_dump/pg_backup_archiver.c | 6 +++++- src/bin/pg_dump/pg_dump.c | 19 +++++++++++++++---- src/bin/pg_upgrade/info.c | 2 +- 4 files changed, 24 insertions(+), 6 deletions(-) diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h index 0a28124..cfdfae5 100644 --- a/src/bin/pg_dump/pg_backup.h +++ b/src/bin/pg_dump/pg_backup.h @@ -118,6 +118,7 @@ typedef struct _restoreOptions bool *idWanted; /* array showing which dump IDs to emit */ int enable_row_security; + int sequence_data; /* dump sequence data even in schema-only mode */ } RestoreOptions; typedef struct _dumpOptions @@ -160,6 +161,8 @@ typedef struct _dumpOptions bool outputBlobs; int outputNoOwner; char *outputSuperuser; + + int sequence_data; /* dump sequence data even in schema-only mode */ } DumpOptions; /* diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 0e20985..b938d79 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -171,6 +171,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; } @@ -2855,7 +2856,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 3485cab..ee1f673 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -216,7 +216,7 @@ static void addBoundaryDependencies(DumpableObject **dobjs, int numObjs, DumpableObject *boundaryObjs); static void getDomainConstraints(Archive *fout, TypeInfo *tyinfo); -static void getTableData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, bool oids); +static void getTableData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, bool oids, char relkind); static void makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids); static void buildMatViewRefreshDependencies(Archive *fout); static void getTableDataFKConstraints(void); @@ -546,6 +546,12 @@ main(int argc, char **argv) if (dopt.column_inserts) dopt.dump_inserts = 1; + /* Binary upgrade mode implies dumping sequence data even in schema-only + * mode. This is not exposed as a separate option, but kept separate + * internally for clarity. */ + if (dopt.binary_upgrade) + dopt.sequence_data = 1; + if (dopt.dataOnly && dopt.schemaOnly) { write_msg(NULL, "options -s/--schema-only and -a/--data-only cannot be used together\n"); @@ -722,12 +728,15 @@ main(int argc, char **argv) if (!dopt.schemaOnly) { - getTableData(&dopt, tblinfo, numTables, dopt.oids); + getTableData(&dopt, tblinfo, numTables, dopt.oids, 0); buildMatViewRefreshDependencies(fout); if (dopt.dataOnly) getTableDataFKConstraints(); } + if (dopt.schemaOnly && dopt.sequence_data) + getTableData(&dopt, tblinfo, numTables, dopt.oids, RELKIND_SEQUENCE); + if (dopt.outputBlobs) getBlobs(fout); @@ -806,6 +815,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; @@ -2039,13 +2049,14 @@ refreshMatViewData(Archive *fout, TableDataInfo *tdinfo) * set up dumpable objects representing the contents of tables */ static void -getTableData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, bool oids) +getTableData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, bool oids, char relkind) { int i; for (i = 0; i < numTables; i++) { - if (tblinfo[i].dobj.dump & DUMP_COMPONENT_DATA) + if (tblinfo[i].dobj.dump & DUMP_COMPONENT_DATA && + (!relkind || tblinfo[i].relkind == relkind)) makeTableDataInfo(dopt, &(tblinfo[i]), oids); } } 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.10.2
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers