From 5110a96992e508b220a7a6ab303b0501c4237b4a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 26 Oct 2012 12:12:53 -0400 Subject: [PATCH] In pg_dump, dump SEQUENCE SET items in the data not pre-data section. Represent a sequence's current value as a separate TableDataInfo dumpable object, so that it can be dumped within the data section of the archive rather than in pre-data. This fixes an undesirable inconsistency between the meanings of "--data-only" and "--section=data", and also fixes dumping of sequences that are marked as extension configuration tables, as per a report from Marko Kreen back in July. The main cost is that we do one more SQL query per sequence, but that's probably not very meaningful in most databases. Back-patch to 9.1, since it has the extension configuration issue even though not the --section switch. --- src/bin/pg_dump/pg_dump.c | 345 ++++++++++++++++++++------------------ 1 file changed, 186 insertions(+), 159 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d9eeaa64602..d6e4220ad5a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -182,6 +182,7 @@ static void dumpTable(Archive *fout, TableInfo *tbinfo); static void dumpTableSchema(Archive *fout, TableInfo *tbinfo); static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo); static void dumpSequence(Archive *fout, TableInfo *tbinfo); +static void dumpSequenceData(Archive *fout, TableDataInfo *tdinfo); static void dumpIndex(Archive *fout, IndxInfo *indxinfo); static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo); static void dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo); @@ -1577,33 +1578,41 @@ getTableData(TableInfo *tblinfo, int numTables, bool oids) for (i = 0; i < numTables; i++) { - /* Skip VIEWs (no data to dump) */ - if (tblinfo[i].relkind == RELKIND_VIEW) - continue; - /* Skip SEQUENCEs (handled elsewhere) */ - if (tblinfo[i].relkind == RELKIND_SEQUENCE) - continue; - /* Skip FOREIGN TABLEs (no data to dump) */ - if (tblinfo[i].relkind == RELKIND_FOREIGN_TABLE) - continue; - /* Skip unlogged tables if so requested */ - if (tblinfo[i].relpersistence == RELPERSISTENCE_UNLOGGED - && no_unlogged_table_data) - continue; - - if (tblinfo[i].dobj.dump && tblinfo[i].dataObj == NULL) + if (tblinfo[i].dobj.dump) makeTableDataInfo(&(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 + * table data; the "dump" flag in such objects isn't used. */ static void makeTableDataInfo(TableInfo *tbinfo, bool oids) { TableDataInfo *tdinfo; + /* + * Nothing to do if we already decided to dump the table. This will + * happen for "config" tables. + */ + if (tbinfo->dataObj != NULL) + return; + + /* Skip VIEWs (no data to dump) */ + if (tbinfo->relkind == RELKIND_VIEW) + return; + /* Skip FOREIGN TABLEs (no data to dump) */ + if (tbinfo->relkind == RELKIND_FOREIGN_TABLE) + return; + /* Skip unlogged tables if so requested */ + if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED && + no_unlogged_table_data) + return; + + /* OK, let's dump it */ tdinfo = (TableDataInfo *) malloc(sizeof(TableDataInfo)); tdinfo->dobj.objType = DO_TABLE_DATA; @@ -7092,7 +7101,10 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj) dumpCast(fout, (CastInfo *) dobj); break; case DO_TABLE_DATA: - dumpTableData(fout, (TableDataInfo *) dobj); + if (((TableDataInfo *) dobj)->tdtable->relkind == RELKIND_SEQUENCE) + dumpSequenceData(fout, (TableDataInfo *) dobj); + else + dumpTableData(fout, (TableDataInfo *) dobj); break; case DO_DUMMY_TYPE: /* table rowtypes and array types are never dumped separately */ @@ -11970,13 +11982,13 @@ collectSecLabels(Archive *fout, SecLabelItem **items) static void dumpTable(Archive *fout, TableInfo *tbinfo) { - if (tbinfo->dobj.dump) + if (tbinfo->dobj.dump && !dataOnly) { char *namecopy; if (tbinfo->relkind == RELKIND_SEQUENCE) dumpSequence(fout, tbinfo); - else if (!dataOnly) + else dumpTableSchema(fout, tbinfo); /* Handle the ACL here */ @@ -13117,20 +13129,22 @@ findLastBuiltinOid_V70(void) return last_oid; } +/* + * dumpSequence + * write the declaration (not data) of one user-defined sequence + */ static void dumpSequence(Archive *fout, TableInfo *tbinfo) { PGresult *res; char *startv, - *last, *incby, *maxv = NULL, *minv = NULL, *cache; char bufm[100], bufx[100]; - bool cycled, - called; + bool cycled; PQExpBuffer query = createPQExpBuffer(); PQExpBuffer delqry = createPQExpBuffer(); PQExpBuffer labelq = createPQExpBuffer(); @@ -13141,11 +13155,11 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE); snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE); - if (g_fout->remoteVersion >= 80400) + if (fout->remoteVersion >= 80400) { appendPQExpBuffer(query, "SELECT sequence_name, " - "start_value, last_value, increment_by, " + "start_value, increment_by, " "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL " " WHEN increment_by < 0 AND max_value = -1 THEN NULL " " ELSE max_value " @@ -13154,7 +13168,7 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) " WHEN increment_by < 0 AND min_value = %s THEN NULL " " ELSE min_value " "END AS min_value, " - "cache_value, is_cycled, is_called from %s", + "cache_value, is_cycled FROM %s", bufx, bufm, fmtId(tbinfo->dobj.name)); } @@ -13162,7 +13176,7 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) { appendPQExpBuffer(query, "SELECT sequence_name, " - "0 AS start_value, last_value, increment_by, " + "0 AS start_value, increment_by, " "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL " " WHEN increment_by < 0 AND max_value = -1 THEN NULL " " ELSE max_value " @@ -13171,7 +13185,7 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) " WHEN increment_by < 0 AND min_value = %s THEN NULL " " ELSE min_value " "END AS min_value, " - "cache_value, is_cycled, is_called from %s", + "cache_value, is_cycled FROM %s", bufx, bufm, fmtId(tbinfo->dobj.name)); } @@ -13199,163 +13213,120 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) #endif startv = PQgetvalue(res, 0, 1); - last = PQgetvalue(res, 0, 2); - incby = PQgetvalue(res, 0, 3); + incby = PQgetvalue(res, 0, 2); + if (!PQgetisnull(res, 0, 3)) + maxv = PQgetvalue(res, 0, 3); if (!PQgetisnull(res, 0, 4)) - maxv = PQgetvalue(res, 0, 4); - if (!PQgetisnull(res, 0, 5)) - minv = PQgetvalue(res, 0, 5); - cache = PQgetvalue(res, 0, 6); - cycled = (strcmp(PQgetvalue(res, 0, 7), "t") == 0); - called = (strcmp(PQgetvalue(res, 0, 8), "t") == 0); + minv = PQgetvalue(res, 0, 4); + cache = PQgetvalue(res, 0, 5); + cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0); /* - * The logic we use for restoring sequences is as follows: - * - * Add a CREATE SEQUENCE statement as part of a "schema" dump (use - * last_val for start if called is false, else use min_val for start_val). - * Also, if the sequence is owned by a column, add an ALTER SEQUENCE OWNED - * BY command for it. - * - * Add a 'SETVAL(seq, last_val, iscalled)' as part of a "data" dump. + * DROP must be fully qualified in case same name appears in pg_catalog */ - if (!dataOnly) - { - /* - * DROP must be fully qualified in case same name appears in - * pg_catalog - */ - appendPQExpBuffer(delqry, "DROP SEQUENCE %s.", - fmtId(tbinfo->dobj.namespace->dobj.name)); - appendPQExpBuffer(delqry, "%s;\n", - fmtId(tbinfo->dobj.name)); + appendPQExpBuffer(delqry, "DROP SEQUENCE %s.", + fmtId(tbinfo->dobj.namespace->dobj.name)); + appendPQExpBuffer(delqry, "%s;\n", + fmtId(tbinfo->dobj.name)); - resetPQExpBuffer(query); + resetPQExpBuffer(query); - if (binary_upgrade) - { - binary_upgrade_set_pg_class_oids(query, tbinfo->dobj.catId.oid, false); - binary_upgrade_set_type_oids_by_rel_oid(query, tbinfo->dobj.catId.oid); - } + if (binary_upgrade) + { + binary_upgrade_set_pg_class_oids(query, + tbinfo->dobj.catId.oid, false); + binary_upgrade_set_type_oids_by_rel_oid(query, + tbinfo->dobj.catId.oid); + } - appendPQExpBuffer(query, - "CREATE SEQUENCE %s\n", - fmtId(tbinfo->dobj.name)); + appendPQExpBuffer(query, + "CREATE SEQUENCE %s\n", + fmtId(tbinfo->dobj.name)); - if (g_fout->remoteVersion >= 80400) - appendPQExpBuffer(query, " START WITH %s\n", startv); - else - { - /* - * Versions before 8.4 did not remember the true start value. If - * is_called is false then the sequence has never been incremented - * so we can use last_val. Otherwise punt and let it default. - */ - if (!called) - appendPQExpBuffer(query, " START WITH %s\n", last); - } + if (fout->remoteVersion >= 80400) + appendPQExpBuffer(query, " START WITH %s\n", startv); - appendPQExpBuffer(query, " INCREMENT BY %s\n", incby); + appendPQExpBuffer(query, " INCREMENT BY %s\n", incby); - if (minv) - appendPQExpBuffer(query, " MINVALUE %s\n", minv); - else - appendPQExpBuffer(query, " NO MINVALUE\n"); + if (minv) + appendPQExpBuffer(query, " MINVALUE %s\n", minv); + else + appendPQExpBuffer(query, " NO MINVALUE\n"); - if (maxv) - appendPQExpBuffer(query, " MAXVALUE %s\n", maxv); - else - appendPQExpBuffer(query, " NO MAXVALUE\n"); + if (maxv) + appendPQExpBuffer(query, " MAXVALUE %s\n", maxv); + else + appendPQExpBuffer(query, " NO MAXVALUE\n"); - appendPQExpBuffer(query, - " CACHE %s%s", - cache, (cycled ? "\n CYCLE" : "")); + appendPQExpBuffer(query, + " CACHE %s%s", + cache, (cycled ? "\n CYCLE" : "")); - appendPQExpBuffer(query, ";\n"); + appendPQExpBuffer(query, ";\n"); - appendPQExpBuffer(labelq, "SEQUENCE %s", fmtId(tbinfo->dobj.name)); + appendPQExpBuffer(labelq, "SEQUENCE %s", fmtId(tbinfo->dobj.name)); - /* binary_upgrade: no need to clear TOAST table oid */ + /* binary_upgrade: no need to clear TOAST table oid */ - if (binary_upgrade) - binary_upgrade_extension_member(query, &tbinfo->dobj, - labelq->data); + if (binary_upgrade) + binary_upgrade_extension_member(query, &tbinfo->dobj, + labelq->data); - ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId, - tbinfo->dobj.name, - tbinfo->dobj.namespace->dobj.name, - NULL, - tbinfo->rolname, - false, "SEQUENCE", SECTION_PRE_DATA, - query->data, delqry->data, NULL, - tbinfo->dobj.dependencies, tbinfo->dobj.nDeps, - NULL, NULL); + ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId, + tbinfo->dobj.name, + tbinfo->dobj.namespace->dobj.name, + NULL, + tbinfo->rolname, + false, "SEQUENCE", SECTION_PRE_DATA, + query->data, delqry->data, NULL, + tbinfo->dobj.dependencies, tbinfo->dobj.nDeps, + NULL, NULL); - /* - * If the sequence is owned by a table column, emit the ALTER for it - * as a separate TOC entry immediately following the sequence's own - * entry. It's OK to do this rather than using full sorting logic, - * because the dependency that tells us it's owned will have forced - * the table to be created first. We can't just include the ALTER in - * the TOC entry because it will fail if we haven't reassigned the - * sequence owner to match the table's owner. - * - * We need not schema-qualify the table reference because both - * sequence and table must be in the same schema. - */ - if (OidIsValid(tbinfo->owning_tab)) - { - TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab); + /* + * If the sequence is owned by a table column, emit the ALTER for it as a + * separate TOC entry immediately following the sequence's own entry. + * It's OK to do this rather than using full sorting logic, because the + * dependency that tells us it's owned will have forced the table to be + * created first. We can't just include the ALTER in the TOC entry + * because it will fail if we haven't reassigned the sequence owner to + * match the table's owner. + * + * We need not schema-qualify the table reference because both sequence + * and table must be in the same schema. + */ + if (OidIsValid(tbinfo->owning_tab)) + { + TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab); - if (owning_tab && owning_tab->dobj.dump) - { - resetPQExpBuffer(query); - appendPQExpBuffer(query, "ALTER SEQUENCE %s", - fmtId(tbinfo->dobj.name)); - appendPQExpBuffer(query, " OWNED BY %s", - fmtId(owning_tab->dobj.name)); - appendPQExpBuffer(query, ".%s;\n", + if (owning_tab && owning_tab->dobj.dump) + { + resetPQExpBuffer(query); + appendPQExpBuffer(query, "ALTER SEQUENCE %s", + fmtId(tbinfo->dobj.name)); + appendPQExpBuffer(query, " OWNED BY %s", + fmtId(owning_tab->dobj.name)); + appendPQExpBuffer(query, ".%s;\n", fmtId(owning_tab->attnames[tbinfo->owning_col - 1])); - ArchiveEntry(fout, nilCatalogId, createDumpId(), - tbinfo->dobj.name, - tbinfo->dobj.namespace->dobj.name, - NULL, - tbinfo->rolname, - false, "SEQUENCE OWNED BY", SECTION_PRE_DATA, - query->data, "", NULL, - &(tbinfo->dobj.dumpId), 1, - NULL, NULL); - } + ArchiveEntry(fout, nilCatalogId, createDumpId(), + tbinfo->dobj.name, + tbinfo->dobj.namespace->dobj.name, + NULL, + tbinfo->rolname, + false, "SEQUENCE OWNED BY", SECTION_PRE_DATA, + query->data, "", NULL, + &(tbinfo->dobj.dumpId), 1, + NULL, NULL); } - - /* Dump Sequence Comments and Security Labels */ - dumpComment(fout, labelq->data, - tbinfo->dobj.namespace->dobj.name, tbinfo->rolname, - tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId); - dumpSecLabel(fout, labelq->data, - tbinfo->dobj.namespace->dobj.name, tbinfo->rolname, - tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId); } - if (!schemaOnly) - { - resetPQExpBuffer(query); - appendPQExpBuffer(query, "SELECT pg_catalog.setval("); - appendStringLiteralAH(query, fmtId(tbinfo->dobj.name), fout); - appendPQExpBuffer(query, ", %s, %s);\n", - last, (called ? "true" : "false")); - - ArchiveEntry(fout, nilCatalogId, createDumpId(), - tbinfo->dobj.name, - tbinfo->dobj.namespace->dobj.name, - NULL, - tbinfo->rolname, - false, "SEQUENCE SET", SECTION_PRE_DATA, - query->data, "", NULL, - &(tbinfo->dobj.dumpId), 1, - NULL, NULL); - } + /* Dump Sequence Comments and Security Labels */ + dumpComment(fout, labelq->data, + tbinfo->dobj.namespace->dobj.name, tbinfo->rolname, + tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId); + dumpSecLabel(fout, labelq->data, + tbinfo->dobj.namespace->dobj.name, tbinfo->rolname, + tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId); PQclear(res); @@ -13364,6 +13335,62 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) destroyPQExpBuffer(labelq); } +/* + * dumpSequenceData + * write the data of one user-defined sequence + */ +static void +dumpSequenceData(Archive *fout, TableDataInfo *tdinfo) +{ + TableInfo *tbinfo = tdinfo->tdtable; + PGresult *res; + char *last; + bool called; + PQExpBuffer query = createPQExpBuffer(); + + /* Make sure we are in proper schema */ + selectSourceSchema(tbinfo->dobj.namespace->dobj.name); + + appendPQExpBuffer(query, + "SELECT last_value, is_called FROM %s", + fmtId(tbinfo->dobj.name)); + + res = PQexec(g_conn, query->data); + check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK); + + if (PQntuples(res) != 1) + { + write_msg(NULL, ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)\n", + "query to get data of sequence \"%s\" returned %d rows (expected 1)\n", + PQntuples(res)), + tbinfo->dobj.name, PQntuples(res)); + exit_nicely(); + } + + last = PQgetvalue(res, 0, 0); + called = (strcmp(PQgetvalue(res, 0, 1), "t") == 0); + + resetPQExpBuffer(query); + appendPQExpBuffer(query, "SELECT pg_catalog.setval("); + appendStringLiteralAH(query, fmtId(tbinfo->dobj.name), fout); + appendPQExpBuffer(query, ", %s, %s);\n", + last, (called ? "true" : "false")); + + ArchiveEntry(fout, nilCatalogId, createDumpId(), + tbinfo->dobj.name, + tbinfo->dobj.namespace->dobj.name, + NULL, + tbinfo->rolname, + false, "SEQUENCE SET", SECTION_DATA, + query->data, "", NULL, + &(tbinfo->dobj.dumpId), 1, + NULL, NULL); + + PQclear(res); + + destroyPQExpBuffer(query); +} + static void dumpTrigger(Archive *fout, TriggerInfo *tginfo) { -- 2.39.5