On 06/21/2018 01:53 PM, Tom Lane wrote:
Andrew Dunstan <andrew.duns...@2ndquadrant.com> writes:
On 06/21/2018 01:44 PM, Tom Lane wrote:
So I'm thinking that the attidentity code is just wrong, and you should
change that too while you're at it.
That should be backpatched if changed, no? I don't think we'd want this
to get out of sync between the branches. It would make later
backpatching more difficult for one thing.
If you feel like it. But if there's attmissingval code right next to it
as of v11, then backpatches wouldn't apply cleanly anyway due to lack of
context match, so I doubt there's really much gain to be had.
I left that for a separate exercise. I think this does things the way
you want. I must admit to being a bit surprised, I was expecting you to
have more to say about the upgrade function than the pg_dump changes :-)
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/backend/utils/adt/pg_upgrade_support.c b/src/backend/utils/adt/pg_upgrade_support.c
index 0c54b02..7a2f785 100644
--- a/src/backend/utils/adt/pg_upgrade_support.c
+++ b/src/backend/utils/adt/pg_upgrade_support.c
@@ -11,13 +11,20 @@
#include "postgres.h"
+#include "access/heapam.h"
+#include "access/htup_details.h"
#include "catalog/binary_upgrade.h"
+#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_type.h"
#include "commands/extension.h"
#include "miscadmin.h"
#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
#define CHECK_IS_BINARY_UPGRADE \
@@ -192,3 +199,63 @@ binary_upgrade_set_record_init_privs(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+
+Datum
+binary_upgrade_set_missing_value(PG_FUNCTION_ARGS)
+{
+ Oid table_id = PG_GETARG_OID(0);
+ text *attname = PG_GETARG_TEXT_P(1);
+ text *value = PG_GETARG_TEXT_P(2);
+ Datum valuesAtt[Natts_pg_attribute];
+ bool nullsAtt[Natts_pg_attribute];
+ bool replacesAtt[Natts_pg_attribute];
+ Datum missingval;
+ Form_pg_attribute attStruct;
+ Relation attrrel,
+ tablerel;
+ HeapTuple atttup,
+ newtup;
+ char *cattname = text_to_cstring(attname);
+ char *cvalue = text_to_cstring(value);
+
+ CHECK_IS_BINARY_UPGRADE;
+
+ /* lock the table the attribute belongs to */
+ tablerel = heap_open(table_id, AccessExclusiveLock);
+
+ /* Lock the attribute row and get the data */
+ attrrel = heap_open(AttributeRelationId, RowExclusiveLock);
+ atttup = SearchSysCacheAttName(table_id, cattname);
+ if (!HeapTupleIsValid(atttup))
+ elog(ERROR, "cache lookup failed for attribute %s of relation %u",
+ cattname, table_id);
+ attStruct = (Form_pg_attribute) GETSTRUCT(atttup);
+
+ /* get an array value from the value string */
+ missingval = OidFunctionCall3(
+ F_ARRAY_IN,
+ CStringGetDatum(cvalue),
+ ObjectIdGetDatum(attStruct->atttypid),
+ Int32GetDatum(attStruct->atttypmod));
+
+ /* update the tuple - set atthasmissing and attmissingval */
+ MemSet(valuesAtt, 0, sizeof(valuesAtt));
+ MemSet(nullsAtt, false, sizeof(nullsAtt));
+ MemSet(replacesAtt, false, sizeof(replacesAtt));
+
+ valuesAtt[Anum_pg_attribute_atthasmissing - 1] = BoolGetDatum(true);
+ replacesAtt[Anum_pg_attribute_atthasmissing - 1] = true;
+ valuesAtt[Anum_pg_attribute_attmissingval - 1] = missingval;
+ replacesAtt[Anum_pg_attribute_attmissingval - 1] = true;
+
+ newtup = heap_modify_tuple(atttup, RelationGetDescr(attrrel),
+ valuesAtt, nullsAtt, replacesAtt);
+ CatalogTupleUpdate(attrrel, &newtup->t_self, newtup);
+
+ /* clean up */
+ ReleaseSysCache(atttup);
+ heap_close(attrrel, RowExclusiveLock);
+ heap_close(tablerel, AccessExclusiveLock);
+
+ PG_RETURN_VOID();
+}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ea2f022..40d1eb0 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8103,6 +8103,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
int i_attoptions;
int i_attcollation;
int i_attfdwoptions;
+ int i_attmissingval;
PGresult *res;
int ntups;
bool hasdefaults;
@@ -8132,7 +8133,34 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
resetPQExpBuffer(q);
- if (fout->remoteVersion >= 100000)
+ if (fout->remoteVersion >= 110000)
+ {
+ /* atthasmissing and attmissingval are new in 11 */
+ appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, "
+ "a.attstattarget, a.attstorage, t.typstorage, "
+ "a.attnotnull, a.atthasdef, a.attisdropped, "
+ "a.attlen, a.attalign, a.attislocal, "
+ "pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
+ "array_to_string(a.attoptions, ', ') AS attoptions, "
+ "CASE WHEN a.attcollation <> t.typcollation "
+ "THEN a.attcollation ELSE 0 END AS attcollation, "
+ "a.attidentity, "
+ "pg_catalog.array_to_string(ARRAY("
+ "SELECT pg_catalog.quote_ident(option_name) || "
+ "' ' || pg_catalog.quote_literal(option_value) "
+ "FROM pg_catalog.pg_options_to_table(attfdwoptions) "
+ "ORDER BY option_name"
+ "), E',\n ') AS attfdwoptions ,"
+ "CASE WHEN a.atthasmissing THEN a.attmissingval "
+ "ELSE null END AS attmissingval "
+ "FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
+ "ON a.atttypid = t.oid "
+ "WHERE a.attrelid = '%u'::pg_catalog.oid "
+ "AND a.attnum > 0::pg_catalog.int2 "
+ "ORDER BY a.attnum",
+ tbinfo->dobj.catId.oid);
+ }
+ else if (fout->remoteVersion >= 100000)
{
/*
* attidentity is new in version 10.
@@ -8151,7 +8179,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
"' ' || pg_catalog.quote_literal(option_value) "
"FROM pg_catalog.pg_options_to_table(attfdwoptions) "
"ORDER BY option_name"
- "), E',\n ') AS attfdwoptions "
+ "), E',\n ') AS attfdwoptions ,"
+ "NULL as attmissingval "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8177,7 +8206,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
"' ' || pg_catalog.quote_literal(option_value) "
"FROM pg_catalog.pg_options_to_table(attfdwoptions) "
"ORDER BY option_name"
- "), E',\n ') AS attfdwoptions "
+ "), E',\n ') AS attfdwoptions, "
+ "NULL as attmissingval "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8201,7 +8231,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
"array_to_string(a.attoptions, ', ') AS attoptions, "
"CASE WHEN a.attcollation <> t.typcollation "
"THEN a.attcollation ELSE 0 END AS attcollation, "
- "NULL AS attfdwoptions "
+ "NULL AS attfdwoptions, "
+ "NULL as attmissingval "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8219,7 +8250,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
"array_to_string(a.attoptions, ', ') AS attoptions, "
"0 AS attcollation, "
- "NULL AS attfdwoptions "
+ "NULL AS attfdwoptions, "
+ "NULL as attmissingval "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8236,7 +8268,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
"a.attlen, a.attalign, a.attislocal, "
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
"'' AS attoptions, 0 AS attcollation, "
- "NULL AS attfdwoptions "
+ "NULL AS attfdwoptions, "
+ "NULL as attmissingval "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8266,6 +8299,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
i_attoptions = PQfnumber(res, "attoptions");
i_attcollation = PQfnumber(res, "attcollation");
i_attfdwoptions = PQfnumber(res, "attfdwoptions");
+ i_attmissingval = PQfnumber(res, "attmissingval");
tbinfo->numatts = ntups;
tbinfo->attnames = (char **) pg_malloc(ntups * sizeof(char *));
@@ -8282,6 +8316,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attoptions = (char **) pg_malloc(ntups * sizeof(char *));
tbinfo->attcollation = (Oid *) pg_malloc(ntups * sizeof(Oid));
tbinfo->attfdwoptions = (char **) pg_malloc(ntups * sizeof(char *));
+ tbinfo->attmissingval = (char **) pg_malloc(ntups * sizeof(char *));
tbinfo->notnull = (bool *) pg_malloc(ntups * sizeof(bool));
tbinfo->inhNotNull = (bool *) pg_malloc(ntups * sizeof(bool));
tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(ntups * sizeof(AttrDefInfo *));
@@ -8309,6 +8344,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, j, i_attoptions));
tbinfo->attcollation[j] = atooid(PQgetvalue(res, j, i_attcollation));
tbinfo->attfdwoptions[j] = pg_strdup(PQgetvalue(res, j, i_attfdwoptions));
+ tbinfo->attmissingval[j] = pg_strdup(PQgetvalue(res, j, i_attmissingval));
tbinfo->attrdefs[j] = NULL; /* fix below */
if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
hasdefaults = true;
@@ -15659,6 +15695,29 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferStr(q, ";\n");
/*
+ * in binary upgrade mode, update the catalog with any missing values
+ * that might be present.
+ */
+ if (dopt->binary_upgrade)
+ {
+ for (j = 0; j < tbinfo->numatts; j++)
+ {
+ if (tbinfo->attmissingval[j][0] != '\0')
+ {
+ appendPQExpBufferStr(q, "\n-- set missing value.\n");
+ appendPQExpBufferStr(q,
+ "SELECT pg_catalog.binary_upgrade_set_missing_value(");
+ appendStringLiteralAH(q, qualrelname, fout);
+ appendPQExpBufferStr(q, "::pg_catalog.regclass,");
+ appendStringLiteralAH(q, tbinfo->attnames[j], fout);
+ appendPQExpBufferStr(q, ",");
+ appendStringLiteralAH(q, tbinfo->attmissingval[j], fout);
+ appendPQExpBufferStr(q, ");\n\n");
+ }
+ }
+ }
+
+ /*
* To create binary-compatible heap files, we have to ensure the same
* physical column order, including dropped columns, as in the
* original. Therefore, we create dropped columns above and drop them
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index e96c662..b904379 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -316,6 +316,7 @@ typedef struct _tableInfo
char **attoptions; /* per-attribute options */
Oid *attcollation; /* per-attribute collation selection */
char **attfdwoptions; /* per-attribute fdw options */
+ char **attmissingval; /* per attribute missing value */
bool *notnull; /* NOT NULL constraints on attributes */
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 66c6c22..9834e38 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10037,6 +10037,10 @@
proname => 'binary_upgrade_set_record_init_privs', provolatile => 'v',
proparallel => 'r', prorettype => 'void', proargtypes => 'bool',
prosrc => 'binary_upgrade_set_record_init_privs' },
+{ oid => '4101', descr => 'for use by pg_upgrade',
+ proname => 'binary_upgrade_set_missing_value', provolatile => 'v',
+ proparallel => 'r', prorettype => 'void', proargtypes => 'oid text text',
+ prosrc => 'binary_upgrade_set_missing_value' },
# replication/origin.h
{ oid => '6003', descr => 'create a replication origin',
