Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2448adf

Browse files
committed
Allow for pg_upgrade of attributes with missing values
Commit16828d5 neglected to do this, so upgraded databases wouldsilently get null instead of the specified default in rows without theattribute defined.A new binary upgrade function is provided to perform this and pg_dump isadjusted to output a call to the function if required in binary upgrademode.Also included is code to drop missing attribute values for droppedcolumns. That way if the type is later dropped the missing value won'thave a dangling reference to the type.Finally the regression tests are adjusted to ensure that there is a rowwith a missing value so that this code is exercised in upgrade testing.Catalog version unfortunately bumped.Regression test changes from Tom Lane.Remainder from me, reviewed by Tom Lane, Andres Freund, Alvaro HerreraDiscussion:https://postgr.es/m/19987.1529420110@sss.pgh.pa.us
1 parent9a994e3 commit2448adf

File tree

9 files changed

+186
-7
lines changed

9 files changed

+186
-7
lines changed

‎src/backend/catalog/heap.c‎

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1613,6 +1613,29 @@ RemoveAttributeById(Oid relid, AttrNumber attnum)
16131613
"........pg.dropped.%d........",attnum);
16141614
namestrcpy(&(attStruct->attname),newattname);
16151615

1616+
/* clear the missing value if any */
1617+
if (attStruct->atthasmissing)
1618+
{
1619+
DatumvaluesAtt[Natts_pg_attribute];
1620+
boolnullsAtt[Natts_pg_attribute];
1621+
boolreplacesAtt[Natts_pg_attribute];
1622+
1623+
/* update the tuple - set atthasmissing and attmissingval */
1624+
MemSet(valuesAtt,0,sizeof(valuesAtt));
1625+
MemSet(nullsAtt, false,sizeof(nullsAtt));
1626+
MemSet(replacesAtt, false,sizeof(replacesAtt));
1627+
1628+
valuesAtt[Anum_pg_attribute_atthasmissing-1]=
1629+
BoolGetDatum(false);
1630+
replacesAtt[Anum_pg_attribute_atthasmissing-1]= true;
1631+
valuesAtt[Anum_pg_attribute_attmissingval-1]= (Datum)0;
1632+
nullsAtt[Anum_pg_attribute_attmissingval-1]= true;
1633+
replacesAtt[Anum_pg_attribute_attmissingval-1]= true;
1634+
1635+
tuple=heap_modify_tuple(tuple,RelationGetDescr(attr_rel),
1636+
valuesAtt,nullsAtt,replacesAtt);
1637+
}
1638+
16161639
CatalogTupleUpdate(attr_rel,&tuple->t_self,tuple);
16171640
}
16181641

@@ -2001,6 +2024,63 @@ RelationClearMissing(Relation rel)
20012024
heap_close(attr_rel,RowExclusiveLock);
20022025
}
20032026

2027+
/*
2028+
* SetAttrMissing
2029+
*
2030+
* Set the missing value of a single attribute. This should only be used by
2031+
* binary upgrade. Takes an AccessExclusive lock on the relation owning the
2032+
* attribute.
2033+
*/
2034+
void
2035+
SetAttrMissing(Oidrelid,char*attname,char*value)
2036+
{
2037+
DatumvaluesAtt[Natts_pg_attribute];
2038+
boolnullsAtt[Natts_pg_attribute];
2039+
boolreplacesAtt[Natts_pg_attribute];
2040+
Datummissingval;
2041+
Form_pg_attributeattStruct;
2042+
Relationattrrel,
2043+
tablerel;
2044+
HeapTupleatttup,
2045+
newtup;
2046+
2047+
/* lock the table the attribute belongs to */
2048+
tablerel=heap_open(relid,AccessExclusiveLock);
2049+
2050+
/* Lock the attribute row and get the data */
2051+
attrrel=heap_open(AttributeRelationId,RowExclusiveLock);
2052+
atttup=SearchSysCacheAttName(relid,attname);
2053+
if (!HeapTupleIsValid(atttup))
2054+
elog(ERROR,"cache lookup failed for attribute %s of relation %u",
2055+
attname,relid);
2056+
attStruct= (Form_pg_attribute)GETSTRUCT(atttup);
2057+
2058+
/* get an array value from the value string */
2059+
missingval=OidFunctionCall3(F_ARRAY_IN,
2060+
CStringGetDatum(value),
2061+
ObjectIdGetDatum(attStruct->atttypid),
2062+
Int32GetDatum(attStruct->atttypmod));
2063+
2064+
/* update the tuple - set atthasmissing and attmissingval */
2065+
MemSet(valuesAtt,0,sizeof(valuesAtt));
2066+
MemSet(nullsAtt, false,sizeof(nullsAtt));
2067+
MemSet(replacesAtt, false,sizeof(replacesAtt));
2068+
2069+
valuesAtt[Anum_pg_attribute_atthasmissing-1]=BoolGetDatum(true);
2070+
replacesAtt[Anum_pg_attribute_atthasmissing-1]= true;
2071+
valuesAtt[Anum_pg_attribute_attmissingval-1]=missingval;
2072+
replacesAtt[Anum_pg_attribute_attmissingval-1]= true;
2073+
2074+
newtup=heap_modify_tuple(atttup,RelationGetDescr(attrrel),
2075+
valuesAtt,nullsAtt,replacesAtt);
2076+
CatalogTupleUpdate(attrrel,&newtup->t_self,newtup);
2077+
2078+
/* clean up */
2079+
ReleaseSysCache(atttup);
2080+
heap_close(attrrel,RowExclusiveLock);
2081+
heap_close(tablerel,AccessExclusiveLock);
2082+
}
2083+
20042084
/*
20052085
* Store a default expression for column attnum of relation rel.
20062086
*

‎src/backend/utils/adt/pg_upgrade_support.c‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@
1212
#include"postgres.h"
1313

1414
#include"catalog/binary_upgrade.h"
15+
#include"catalog/heap.h"
1516
#include"catalog/namespace.h"
1617
#include"catalog/pg_type.h"
1718
#include"commands/extension.h"
@@ -192,3 +193,18 @@ binary_upgrade_set_record_init_privs(PG_FUNCTION_ARGS)
192193

193194
PG_RETURN_VOID();
194195
}
196+
197+
Datum
198+
binary_upgrade_set_missing_value(PG_FUNCTION_ARGS)
199+
{
200+
Oidtable_id=PG_GETARG_OID(0);
201+
text*attname=PG_GETARG_TEXT_P(1);
202+
text*value=PG_GETARG_TEXT_P(2);
203+
char*cattname=text_to_cstring(attname);
204+
char*cvalue=text_to_cstring(value);
205+
206+
CHECK_IS_BINARY_UPGRADE;
207+
SetAttrMissing(table_id,cattname,cvalue);
208+
209+
PG_RETURN_VOID();
210+
}

‎src/bin/pg_dump/pg_dump.c‎

Lines changed: 65 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8103,6 +8103,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
81038103
inti_attoptions;
81048104
inti_attcollation;
81058105
inti_attfdwoptions;
8106+
inti_attmissingval;
81068107
PGresult *res;
81078108
intntups;
81088109
boolhasdefaults;
@@ -8132,7 +8133,34 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
81328133

81338134
resetPQExpBuffer(q);
81348135

8135-
if (fout->remoteVersion >= 100000)
8136+
if (fout->remoteVersion >= 110000)
8137+
{
8138+
/* atthasmissing and attmissingval are new in 11 */
8139+
appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, "
8140+
"a.attstattarget, a.attstorage, t.typstorage, "
8141+
"a.attnotnull, a.atthasdef, a.attisdropped, "
8142+
"a.attlen, a.attalign, a.attislocal, "
8143+
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
8144+
"array_to_string(a.attoptions, ', ') AS attoptions, "
8145+
"CASE WHEN a.attcollation <> t.typcollation "
8146+
"THEN a.attcollation ELSE 0 END AS attcollation, "
8147+
"a.attidentity, "
8148+
"pg_catalog.array_to_string(ARRAY("
8149+
"SELECT pg_catalog.quote_ident(option_name) || "
8150+
"' ' || pg_catalog.quote_literal(option_value) "
8151+
"FROM pg_catalog.pg_options_to_table(attfdwoptions) "
8152+
"ORDER BY option_name"
8153+
"), E',\n ') AS attfdwoptions ,"
8154+
"CASE WHEN a.atthasmissing AND NOT a.attisdropped "
8155+
"THEN a.attmissingval ELSE null END AS attmissingval "
8156+
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
8157+
"ON a.atttypid = t.oid "
8158+
"WHERE a.attrelid = '%u'::pg_catalog.oid "
8159+
"AND a.attnum > 0::pg_catalog.int2 "
8160+
"ORDER BY a.attnum",
8161+
tbinfo->dobj.catId.oid);
8162+
}
8163+
else if (fout->remoteVersion >= 100000)
81368164
{
81378165
/*
81388166
* attidentity is new in version 10.
@@ -8151,7 +8179,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
81518179
"' ' || pg_catalog.quote_literal(option_value) "
81528180
"FROM pg_catalog.pg_options_to_table(attfdwoptions) "
81538181
"ORDER BY option_name"
8154-
"), E',\n ') AS attfdwoptions "
8182+
"), E',\n ') AS attfdwoptions ,"
8183+
"NULL as attmissingval "
81558184
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
81568185
"ON a.atttypid = t.oid "
81578186
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8177,7 +8206,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
81778206
"' ' || pg_catalog.quote_literal(option_value) "
81788207
"FROM pg_catalog.pg_options_to_table(attfdwoptions) "
81798208
"ORDER BY option_name"
8180-
"), E',\n ') AS attfdwoptions "
8209+
"), E',\n ') AS attfdwoptions, "
8210+
"NULL as attmissingval "
81818211
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
81828212
"ON a.atttypid = t.oid "
81838213
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8201,7 +8231,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
82018231
"array_to_string(a.attoptions, ', ') AS attoptions, "
82028232
"CASE WHEN a.attcollation <> t.typcollation "
82038233
"THEN a.attcollation ELSE 0 END AS attcollation, "
8204-
"NULL AS attfdwoptions "
8234+
"NULL AS attfdwoptions, "
8235+
"NULL as attmissingval "
82058236
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
82068237
"ON a.atttypid = t.oid "
82078238
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8219,7 +8250,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
82198250
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
82208251
"array_to_string(a.attoptions, ', ') AS attoptions, "
82218252
"0 AS attcollation, "
8222-
"NULL AS attfdwoptions "
8253+
"NULL AS attfdwoptions, "
8254+
"NULL as attmissingval "
82238255
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
82248256
"ON a.atttypid = t.oid "
82258257
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8236,7 +8268,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
82368268
"a.attlen, a.attalign, a.attislocal, "
82378269
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
82388270
"'' AS attoptions, 0 AS attcollation, "
8239-
"NULL AS attfdwoptions "
8271+
"NULL AS attfdwoptions, "
8272+
"NULL as attmissingval "
82408273
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
82418274
"ON a.atttypid = t.oid "
82428275
"WHERE a.attrelid = '%u'::pg_catalog.oid "
@@ -8266,6 +8299,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
82668299
i_attoptions = PQfnumber(res, "attoptions");
82678300
i_attcollation = PQfnumber(res, "attcollation");
82688301
i_attfdwoptions = PQfnumber(res, "attfdwoptions");
8302+
i_attmissingval = PQfnumber(res, "attmissingval");
82698303

82708304
tbinfo->numatts = ntups;
82718305
tbinfo->attnames = (char **) pg_malloc(ntups * sizeof(char *));
@@ -8282,6 +8316,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
82828316
tbinfo->attoptions = (char **) pg_malloc(ntups * sizeof(char *));
82838317
tbinfo->attcollation = (Oid *) pg_malloc(ntups * sizeof(Oid));
82848318
tbinfo->attfdwoptions = (char **) pg_malloc(ntups * sizeof(char *));
8319+
tbinfo->attmissingval = (char **) pg_malloc(ntups * sizeof(char *));
82858320
tbinfo->notnull = (bool *) pg_malloc(ntups * sizeof(bool));
82868321
tbinfo->inhNotNull = (bool *) pg_malloc(ntups * sizeof(bool));
82878322
tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(ntups * sizeof(AttrDefInfo *));
@@ -8309,6 +8344,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
83098344
tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, j, i_attoptions));
83108345
tbinfo->attcollation[j] = atooid(PQgetvalue(res, j, i_attcollation));
83118346
tbinfo->attfdwoptions[j] = pg_strdup(PQgetvalue(res, j, i_attfdwoptions));
8347+
tbinfo->attmissingval[j] = pg_strdup(PQgetvalue(res, j, i_attmissingval));
83128348
tbinfo->attrdefs[j] = NULL; /* fix below */
83138349
if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
83148350
hasdefaults = true;
@@ -15658,6 +15694,29 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1565815694
else
1565915695
appendPQExpBufferStr(q, ";\n");
1566015696

15697+
/*
15698+
* in binary upgrade mode, update the catalog with any missing values
15699+
* that might be present.
15700+
*/
15701+
if (dopt->binary_upgrade)
15702+
{
15703+
for (j = 0; j < tbinfo->numatts; j++)
15704+
{
15705+
if (tbinfo->attmissingval[j][0] != '\0')
15706+
{
15707+
appendPQExpBufferStr(q, "\n-- set missing value.\n");
15708+
appendPQExpBufferStr(q,
15709+
"SELECT pg_catalog.binary_upgrade_set_missing_value(");
15710+
appendStringLiteralAH(q, qualrelname, fout);
15711+
appendPQExpBufferStr(q, "::pg_catalog.regclass,");
15712+
appendStringLiteralAH(q, tbinfo->attnames[j], fout);
15713+
appendPQExpBufferStr(q, ",");
15714+
appendStringLiteralAH(q, tbinfo->attmissingval[j], fout);
15715+
appendPQExpBufferStr(q, ");\n\n");
15716+
}
15717+
}
15718+
}
15719+
1566115720
/*
1566215721
* To create binary-compatible heap files, we have to ensure the same
1566315722
* physical column order, including dropped columns, as in the

‎src/bin/pg_dump/pg_dump.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -316,6 +316,7 @@ typedef struct _tableInfo
316316
char**attoptions;/* per-attribute options */
317317
Oid*attcollation;/* per-attribute collation selection */
318318
char**attfdwoptions;/* per-attribute fdw options */
319+
char**attmissingval;/* per attribute missing value */
319320
bool*notnull;/* NOT NULL constraints on attributes */
320321
bool*inhNotNull;/* true if NOT NULL is inherited */
321322
struct_attrDefInfo**attrdefs;/* DEFAULT expressions */

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201804191
56+
#defineCATALOG_VERSION_NO201806221
5757

5858
#endif

‎src/include/catalog/heap.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -105,6 +105,7 @@ extern List *AddRelationNewConstraints(Relation rel,
105105
boolis_internal);
106106

107107
externvoidRelationClearMissing(Relationrel);
108+
externvoidSetAttrMissing(Oidrelid,char*attname,char*value);
108109

109110
externOidStoreAttrDefault(Relationrel,AttrNumberattnum,
110111
Node*expr,boolis_internal,

‎src/include/catalog/pg_proc.dat‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10037,6 +10037,10 @@
1003710037
proname => 'binary_upgrade_set_record_init_privs', provolatile => 'v',
1003810038
proparallel => 'r', prorettype => 'void', proargtypes => 'bool',
1003910039
prosrc => 'binary_upgrade_set_record_init_privs' },
10040+
{ oid => '4101', descr => 'for use by pg_upgrade',
10041+
proname => 'binary_upgrade_set_missing_value', provolatile => 'v',
10042+
proparallel => 'r', prorettype => 'void', proargtypes => 'oid text text',
10043+
prosrc => 'binary_upgrade_set_missing_value' },
1004010044

1004110045
# replication/origin.h
1004210046
{ oid => '6003', descr => 'create a replication origin',

‎src/test/regress/expected/fast_default.out‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -548,3 +548,14 @@ DROP TABLE has_volatile;
548548
DROP EVENT TRIGGER has_volatile_rewrite;
549549
DROP FUNCTION log_rewrite;
550550
DROP SCHEMA fast_default;
551+
-- Leave a table with an active fast default in place, for pg_upgrade testing
552+
set search_path = public;
553+
create table has_fast_default(f1 int);
554+
insert into has_fast_default values(1);
555+
alter table has_fast_default add column f2 int default 42;
556+
table has_fast_default;
557+
f1 | f2
558+
----+----
559+
1 | 42
560+
(1 row)
561+

‎src/test/regress/sql/fast_default.sql‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -369,3 +369,10 @@ DROP TABLE has_volatile;
369369
DROP EVENT TRIGGER has_volatile_rewrite;
370370
DROPFUNCTION log_rewrite;
371371
DROPSCHEMA fast_default;
372+
373+
-- Leave a table with an active fast default in place, for pg_upgrade testing
374+
set search_path= public;
375+
createtablehas_fast_default(f1int);
376+
insert into has_fast_defaultvalues(1);
377+
altertable has_fast_default add column f2int default42;
378+
table has_fast_default;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp