diff options
Diffstat (limited to 'src/bin/pg_dump/pg_dump.c')
| -rw-r--r-- | src/bin/pg_dump/pg_dump.c | 305 |
1 files changed, 275 insertions, 30 deletions
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index b7b822da624..a8c141b689d 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -343,6 +343,10 @@ static void getTableData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, c static void makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo); static void buildMatViewRefreshDependencies(Archive *fout); static void getTableDataFKConstraints(void); +static void determineNotNullFlags(Archive *fout, PGresult *res, int r, + TableInfo *tbinfo, int j, + int i_notnull_name, int i_notnull_noinherit, + int i_notnull_islocal); static char *format_function_arguments(const FuncInfo *finfo, const char *funcargs, bool is_agg); static char *format_function_signature(Archive *fout, @@ -8751,7 +8755,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) int i_attlen; int i_attalign; int i_attislocal; - int i_attnotnull; + int i_notnull_name; + int i_notnull_noinherit; + int i_notnull_islocal; int i_attoptions; int i_attcollation; int i_attcompression; @@ -8761,13 +8767,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) /* * We want to perform just one query against pg_attribute, and then just - * one against pg_attrdef (for DEFAULTs) and one against pg_constraint - * (for CHECK constraints). However, we mustn't try to select every row - * of those catalogs and then sort it out on the client side, because some - * of the server-side functions we need would be unsafe to apply to tables - * we don't have lock on. Hence, we build an array of the OIDs of tables - * we care about (and now have lock on!), and use a WHERE clause to - * constrain which rows are selected. + * one against pg_attrdef (for DEFAULTs) and two against pg_constraint + * (for CHECK constraints and for NOT NULL constraints). However, we + * mustn't try to select every row of those catalogs and then sort it out + * on the client side, because some of the server-side functions we need + * would be unsafe to apply to tables we don't have lock on. Hence, we + * build an array of the OIDs of tables we care about (and now have lock + * on!), and use a WHERE clause to constrain which rows are selected. */ appendPQExpBufferChar(tbloids, '{'); appendPQExpBufferChar(checkoids, '{'); @@ -8814,7 +8820,6 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) "a.attstattarget,\n" "a.attstorage,\n" "t.typstorage,\n" - "a.attnotnull,\n" "a.atthasdef,\n" "a.attisdropped,\n" "a.attlen,\n" @@ -8831,6 +8836,30 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) "ORDER BY option_name" "), E',\n ') AS attfdwoptions,\n"); + /* + * Find out any NOT NULL markings for each column. In 18 and up we read + * pg_constraint to obtain the constraint name. notnull_noinherit is set + * according to the NO INHERIT property. For versions prior to 18, we + * store an empty string as the name when a constraint is marked as + * attnotnull (this cues dumpTableSchema to print the NOT NULL clause + * without a name); also, such cases are never NO INHERIT. + * + * We track in notnull_islocal whether the constraint was defined directly + * in this table or via an ancestor, for binary upgrade. flagInhAttrs + * might modify this later for servers older than 18; it's also in charge + * of determining the correct inhcount. + */ + if (fout->remoteVersion >= 180000) + appendPQExpBufferStr(q, + "co.conname AS notnull_name,\n" + "co.connoinherit AS notnull_noinherit,\n" + "co.conislocal AS notnull_islocal,\n"); + else + appendPQExpBufferStr(q, + "CASE WHEN a.attnotnull THEN '' ELSE NULL END AS notnull_name,\n" + "false AS notnull_noinherit,\n" + "a.attislocal AS notnull_islocal,\n"); + if (fout->remoteVersion >= 140000) appendPQExpBufferStr(q, "a.attcompression AS attcompression,\n"); @@ -8865,11 +8894,25 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) "FROM unnest('%s'::pg_catalog.oid[]) AS src(tbloid)\n" "JOIN pg_catalog.pg_attribute a ON (src.tbloid = a.attrelid) " "LEFT JOIN pg_catalog.pg_type t " - "ON (a.atttypid = t.oid)\n" - "WHERE a.attnum > 0::pg_catalog.int2\n" - "ORDER BY a.attrelid, a.attnum", + "ON (a.atttypid = t.oid)\n", tbloids->data); + /* + * In versions 18 and up, we need pg_constraint for explicit NOT NULL + * entries. Also, we need to know if the NOT NULL for each column is + * backing a primary key. + */ + if (fout->remoteVersion >= 180000) + appendPQExpBufferStr(q, + " LEFT JOIN pg_catalog.pg_constraint co ON " + "(a.attrelid = co.conrelid\n" + " AND co.contype = 'n' AND " + "co.conkey = array[a.attnum])\n"); + + appendPQExpBufferStr(q, + "WHERE a.attnum > 0::pg_catalog.int2\n" + "ORDER BY a.attrelid, a.attnum"); + res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK); ntups = PQntuples(res); @@ -8887,7 +8930,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) i_attlen = PQfnumber(res, "attlen"); i_attalign = PQfnumber(res, "attalign"); i_attislocal = PQfnumber(res, "attislocal"); - i_attnotnull = PQfnumber(res, "attnotnull"); + i_notnull_name = PQfnumber(res, "notnull_name"); + i_notnull_noinherit = PQfnumber(res, "notnull_noinherit"); + i_notnull_islocal = PQfnumber(res, "notnull_islocal"); i_attoptions = PQfnumber(res, "attoptions"); i_attcollation = PQfnumber(res, "attcollation"); i_attcompression = PQfnumber(res, "attcompression"); @@ -8952,8 +8997,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attcompression = (char *) pg_malloc(numatts * sizeof(char)); tbinfo->attfdwoptions = (char **) pg_malloc(numatts * sizeof(char *)); tbinfo->attmissingval = (char **) pg_malloc(numatts * sizeof(char *)); - tbinfo->notnull = (bool *) pg_malloc(numatts * sizeof(bool)); - tbinfo->inhNotNull = (bool *) pg_malloc(numatts * sizeof(bool)); + tbinfo->notnull_constrs = (char **) pg_malloc(numatts * sizeof(char *)); + tbinfo->notnull_noinh = (bool *) pg_malloc(numatts * sizeof(bool)); + tbinfo->notnull_islocal = (bool *) pg_malloc(numatts * sizeof(bool)); tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(numatts * sizeof(AttrDefInfo *)); hasdefaults = false; @@ -8977,7 +9023,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attlen[j] = atoi(PQgetvalue(res, r, i_attlen)); tbinfo->attalign[j] = *(PQgetvalue(res, r, i_attalign)); tbinfo->attislocal[j] = (PQgetvalue(res, r, i_attislocal)[0] == 't'); - tbinfo->notnull[j] = (PQgetvalue(res, r, i_attnotnull)[0] == 't'); + + /* Handle not-null constraint name and flags */ + determineNotNullFlags(fout, res, r, + tbinfo, j, + i_notnull_name, i_notnull_noinherit, + i_notnull_islocal); + tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, r, i_attoptions)); tbinfo->attcollation[j] = atooid(PQgetvalue(res, r, i_attcollation)); tbinfo->attcompression[j] = *(PQgetvalue(res, r, i_attcompression)); @@ -8986,8 +9038,6 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attrdefs[j] = NULL; /* fix below */ if (PQgetvalue(res, r, i_atthasdef)[0] == 't') hasdefaults = true; - /* these flags will be set in flagInhAttrs() */ - tbinfo->inhNotNull[j] = false; } if (hasdefaults) @@ -9269,6 +9319,110 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) } /* + * Based on the getTableAttrs query's row corresponding to one column, set + * the name and flags to handle a not-null constraint for that column in + * the tbinfo struct. + * + * Result row 'r' is for tbinfo's attribute 'j'. + * + * There are three possibilities: + * 1) the column has no not-null constraints. In that case, ->notnull_constrs + * (the constraint name) remains NULL. + * 2) The column has a constraint with no name (this is the case when + * constraints come from pre-18 servers). In this case, ->notnull_constrs + * is set to the empty string; dumpTableSchema will print just "NOT NULL". + * 3) The column has a constraint with a known name; in that case + * notnull_constrs carries that name and dumpTableSchema will print + * "CONSTRAINT the_name NOT NULL". However, if the name is the default + * (table_column_not_null), there's no need to print that name in the dump, + * so notnull_constrs is set to the empty string and it behaves as the case + * above. + * + * In a child table that inherits from a parent already containing NOT NULL + * constraints and the columns in the child don't have their own NOT NULL + * declarations, we suppress printing constraints in the child: the + * constraints are acquired at the point where the child is attached to the + * parent. This is tracked in ->notnull_inh (which is set in flagInhAttrs for + * servers pre-18). + * + * Any of these constraints might have the NO INHERIT bit. If so we set + * ->notnull_noinh and NO INHERIT will be printed by dumpTableSchema. + * + * In case 3 above, the name comparison is a bit of a hack; it actually fails + * to do the right thing in all but the trivial case. However, the downside + * of getting it wrong is simply that the name is printed rather than + * suppressed, so it's not a big deal. + */ +static void +determineNotNullFlags(Archive *fout, PGresult *res, int r, + TableInfo *tbinfo, int j, + int i_notnull_name, int i_notnull_noinherit, + int i_notnull_islocal) +{ + DumpOptions *dopt = fout->dopt; + + /* + * notnull_noinh is straight from the query result. notnull_islocal also, + * though flagInhAttrs may change that one later in versions < 18. + */ + tbinfo->notnull_noinh[j] = PQgetvalue(res, r, i_notnull_noinherit)[0] == 't'; + tbinfo->notnull_islocal[j] = PQgetvalue(res, r, i_notnull_islocal)[0] == 't'; + + /* + * Determine a constraint name to use. If the column is not marked not- + * null, we set NULL which cues ... to do nothing. An empty string says + * to print an unnamed NOT NULL, and anything else is a constraint name to + * use. + */ + if (fout->remoteVersion < 180000) + { + /* + * < 18 doesn't have not-null names, so an unnamed constraint is + * sufficient. + */ + if (PQgetisnull(res, r, i_notnull_name)) + tbinfo->notnull_constrs[j] = NULL; + else + tbinfo->notnull_constrs[j] = ""; + } + else + { + if (PQgetisnull(res, r, i_notnull_name)) + tbinfo->notnull_constrs[j] = NULL; + else + { + /* + * In binary upgrade of inheritance child tables, must have a + * constraint name that we can UPDATE later. + */ + if (dopt->binary_upgrade && + !tbinfo->ispartition && + !tbinfo->notnull_islocal) + { + tbinfo->notnull_constrs[j] = + pstrdup(PQgetvalue(res, r, i_notnull_name)); + } + else + { + char *default_name; + + /* XXX should match ChooseConstraintName better */ + default_name = psprintf("%s_%s_not_null", tbinfo->dobj.name, + tbinfo->attnames[j]); + if (strcmp(default_name, + PQgetvalue(res, r, i_notnull_name)) == 0) + tbinfo->notnull_constrs[j] = ""; + else + { + tbinfo->notnull_constrs[j] = + pstrdup(PQgetvalue(res, r, i_notnull_name)); + } + } + } + } +} + +/* * Test whether a column should be printed as part of table's CREATE TABLE. * Column number is zero-based. * @@ -15970,13 +16124,14 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) !tbinfo->attrdefs[j]->separate); /* - * Not Null constraint --- suppress if inherited, except - * if partition, or in binary-upgrade case where that - * won't work. + * Not Null constraint --- print it if it is locally + * defined, or if binary upgrade. (In the latter case, we + * reset conislocal below.) */ - print_notnull = (tbinfo->notnull[j] && - (!tbinfo->inhNotNull[j] || - tbinfo->ispartition || dopt->binary_upgrade)); + print_notnull = (tbinfo->notnull_constrs[j] != NULL && + (tbinfo->notnull_islocal[j] || + dopt->binary_upgrade || + tbinfo->ispartition)); /* * Skip column if fully defined by reloftype, except in @@ -16032,9 +16187,22 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) tbinfo->attrdefs[j]->adef_expr); } + print_notnull = (tbinfo->notnull_constrs[j] != NULL && + (tbinfo->notnull_islocal[j] || + dopt->binary_upgrade || + tbinfo->ispartition)); if (print_notnull) - appendPQExpBufferStr(q, " NOT NULL"); + { + if (tbinfo->notnull_constrs[j][0] == '\0') + appendPQExpBufferStr(q, " NOT NULL"); + else + appendPQExpBuffer(q, " CONSTRAINT %s NOT NULL", + fmtId(tbinfo->notnull_constrs[j])); + + if (tbinfo->notnull_noinh[j]) + appendPQExpBufferStr(q, " NO INHERIT"); + } /* Add collation if not default for the type */ if (OidIsValid(tbinfo->attcollation[j])) @@ -16212,6 +16380,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) tbinfo->relkind == RELKIND_PARTITIONED_TABLE)) { bool firstitem; + bool firstitem_extra; /* * Drop any dropped columns. Merge the pg_attribute manipulations @@ -16290,6 +16459,71 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) appendPQExpBufferStr(q, ");\n"); /* + * Fix up not-null constraints that come from inheritance. As + * above, do the pg_constraint manipulations in a single SQL + * command. (Actually, two in special cases, if we're doing an + * upgrade from < 18). + */ + firstitem = true; + firstitem_extra = true; + resetPQExpBuffer(extra); + for (j = 0; j < tbinfo->numatts; j++) + { + /* + * If a not-null constraint comes from inheritance, reset + * conislocal. The inhcount is fixed by ALTER TABLE INHERIT, + * below. Special hack: in versions < 18, columns with no + * local definition need their constraint to be matched by + * column number in conkeys instead of by contraint name, + * because the latter is not available. (We distinguish the + * case because the constraint name is the empty string.) + */ + if (tbinfo->notnull_constrs[j] != NULL && + !tbinfo->notnull_islocal[j]) + { + if (tbinfo->notnull_constrs[j][0] != '\0') + { + if (firstitem) + { + appendPQExpBufferStr(q, "UPDATE pg_catalog.pg_constraint\n" + "SET conislocal = false\n" + "WHERE contype = 'n' AND conrelid = "); + appendStringLiteralAH(q, qualrelname, fout); + appendPQExpBufferStr(q, "::pg_catalog.regclass AND\n" + "conname IN ("); + firstitem = false; + } + else + appendPQExpBufferStr(q, ", "); + appendStringLiteralAH(q, tbinfo->notnull_constrs[j], fout); + } + else + { + if (firstitem_extra) + { + appendPQExpBufferStr(extra, "UPDATE pg_catalog.pg_constraint\n" + "SET conislocal = false\n" + "WHERE contype = 'n' AND conrelid = "); + appendStringLiteralAH(extra, qualrelname, fout); + appendPQExpBufferStr(extra, "::pg_catalog.regclass AND\n" + "conkey IN ("); + firstitem_extra = false; + } + else + appendPQExpBufferStr(extra, ", "); + appendPQExpBuffer(extra, "'{%d}'", j + 1); + } + } + } + if (!firstitem) + appendPQExpBufferStr(q, ");\n"); + if (!firstitem_extra) + appendPQExpBufferStr(extra, ");\n"); + + if (extra->len > 0) + appendBinaryPQExpBuffer(q, extra->data, extra->len); + + /* * Add inherited CHECK constraints, if any. * * For partitions, they were already dumped, and conislocal @@ -16428,11 +16662,22 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) * we have to mark it separately. */ if (!shouldPrintColumn(dopt, tbinfo, j) && - tbinfo->notnull[j] && !tbinfo->inhNotNull[j]) - appendPQExpBuffer(q, - "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET NOT NULL;\n", - foreign, qualrelname, - fmtId(tbinfo->attnames[j])); + tbinfo->notnull_constrs[j] != NULL && + (tbinfo->notnull_islocal[j] && !tbinfo->ispartition && !dopt->binary_upgrade)) + { + /* No constraint name desired? */ + if (tbinfo->notnull_constrs[j][0] == '\0') + appendPQExpBuffer(q, + "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET NOT NULL;\n", + foreign, qualrelname, + fmtId(tbinfo->attnames[j])); + else + appendPQExpBuffer(q, + "ALTER %sTABLE ONLY %s ADD CONSTRAINT %s NOT NULL %s;\n", + foreign, qualrelname, + tbinfo->notnull_constrs[j], + fmtId(tbinfo->attnames[j])); + } /* * Dump per-column statistics information. We only issue an ALTER |