Skip to content

Commit aa25d10

Browse files
tglsfdctglsfdc
committed
Fix up pg_dump's handling of per-attribute compression options.
The approach used in commit bbe0a81 would've been disastrous for portability of dumps. Instead handle non-default compression options in separate ALTER TABLE commands. This reduces chatter for the common case where most columns are compressed the same way, and it makes it possible to restore the dump to a server that lacks any knowledge of per-attribute compression options (so long as you're willing to ignore syntax errors from the ALTER TABLE commands). There's a whole lot left to do to mop up after bbe0a81, but I'm fast-tracking this part because we need to see if it's enough to make the buildfarm's cross-version-upgrade tests happy. Justin Pryzby and Tom Lane Discussion: https://postgr.es/m/20210119190720.GL8560@telsasoft.com
1 parent e835e89 commit aa25d10

File tree

5 files changed

+134
-51
lines changed

5 files changed

+134
-51
lines changed

‎src/bin/pg_dump/pg_backup.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -159,8 +159,8 @@ typedef struct _dumpOptions
159159
int no_publications;
160160
int no_subscriptions;
161161
int no_synchronized_snapshots;
162-
int no_unlogged_table_data;
163162
int no_toast_compression;
163+
int no_unlogged_table_data;
164164
int serializable_deferrable;
165165
int disable_triggers;
166166
int outputNoTablespaces;
@@ -209,6 +209,8 @@ typedef struct Archive
209209

210210
/* other important stuff */
211211
char *searchpath; /* search_path to set during restore */
212+
char *default_toast_compression; /* default TOAST compression to
213+
* set during restore */
212214
char *use_role; /* Issue SET ROLE to this */
213215

214216
/* error handling */

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 33 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,7 @@ static void _selectTableAccessMethod(ArchiveHandle *AH, const char *tableam);
8686
static void processEncodingEntry(ArchiveHandle *AH, TocEntry *te);
8787
static void processStdStringsEntry(ArchiveHandle *AH, TocEntry *te);
8888
static void processSearchPathEntry(ArchiveHandle *AH, TocEntry *te);
89+
static void processToastCompressionEntry(ArchiveHandle *AH, TocEntry *te);
8990
static int _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH);
9091
static RestorePass _tocEntryRestorePass(TocEntry *te);
9192
static bool _tocEntryIsACL(TocEntry *te);
@@ -2696,6 +2697,8 @@ ReadToc(ArchiveHandle *AH)
26962697
processStdStringsEntry(AH, te);
26972698
else if (strcmp(te->desc, "SEARCHPATH") == 0)
26982699
processSearchPathEntry(AH, te);
2700+
else if (strcmp(te->desc, "TOASTCOMPRESSION") == 0)
2701+
processToastCompressionEntry(AH, te);
26992702
}
27002703
}
27012704

@@ -2753,6 +2756,29 @@ processSearchPathEntry(ArchiveHandle *AH, TocEntry *te)
27532756
AH->public.searchpath = pg_strdup(te->defn);
27542757
}
27552758

2759+
static void
2760+
processToastCompressionEntry(ArchiveHandle *AH, TocEntry *te)
2761+
{
2762+
/* te->defn should have the form SET default_toast_compression = 'x'; */
2763+
char *defn = pg_strdup(te->defn);
2764+
char *ptr1;
2765+
char *ptr2 = NULL;
2766+
2767+
ptr1 = strchr(defn, '\'');
2768+
if (ptr1)
2769+
ptr2 = strchr(++ptr1, '\'');
2770+
if (ptr2)
2771+
{
2772+
*ptr2 = '\0';
2773+
AH->public.default_toast_compression = pg_strdup(ptr1);
2774+
}
2775+
else
2776+
fatal("invalid TOASTCOMPRESSION item: %s",
2777+
te->defn);
2778+
2779+
free(defn);
2780+
}
2781+
27562782
static void
27572783
StrictNamesCheck(RestoreOptions *ropt)
27582784
{
@@ -2812,7 +2838,8 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
28122838
/* These items are treated specially */
28132839
if (strcmp(te->desc, "ENCODING") == 0 ||
28142840
strcmp(te->desc, "STDSTRINGS") == 0 ||
2815-
strcmp(te->desc, "SEARCHPATH") == 0)
2841+
strcmp(te->desc, "SEARCHPATH") == 0 ||
2842+
strcmp(te->desc, "TOASTCOMPRESSION") == 0)
28162843
return REQ_SPECIAL;
28172844

28182845
/*
@@ -3135,6 +3162,11 @@ _doSetFixedOutputState(ArchiveHandle *AH)
31353162
if (AH->public.searchpath)
31363163
ahprintf(AH, "%s", AH->public.searchpath);
31373164

3165+
/* Select the dump-time default_toast_compression */
3166+
if (AH->public.default_toast_compression)
3167+
ahprintf(AH, "SET default_toast_compression = '%s';\n",
3168+
AH->public.default_toast_compression);
3169+
31383170
/* Make sure function checking is disabled */
31393171
ahprintf(AH, "SET check_function_bodies = false;\n");
31403172

‎src/bin/pg_dump/pg_dump.c

Lines changed: 91 additions & 42 deletions
Original file line numberDiff line numberDiff line change
@@ -270,6 +270,7 @@ static void dumpDatabaseConfig(Archive *AH, PQExpBuffer outbuf,
270270
static void dumpEncoding(Archive *AH);
271271
static void dumpStdStrings(Archive *AH);
272272
static void dumpSearchPath(Archive *AH);
273+
static void dumpToastCompression(Archive *AH);
273274
static void binary_upgrade_set_type_oids_by_type_oid(Archive *fout,
274275
PQExpBuffer upgrade_buffer,
275276
Oid pg_type_oid,
@@ -384,10 +385,10 @@ main(int argc, char **argv)
384385
{"no-comments", no_argument, &dopt.no_comments, 1},
385386
{"no-publications", no_argument, &dopt.no_publications, 1},
386387
{"no-security-labels", no_argument, &dopt.no_security_labels, 1},
387-
{"no-synchronized-snapshots", no_argument, &dopt.no_synchronized_snapshots, 1},
388-
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
389388
{"no-subscriptions", no_argument, &dopt.no_subscriptions, 1},
389+
{"no-synchronized-snapshots", no_argument, &dopt.no_synchronized_snapshots, 1},
390390
{"no-toast-compression", no_argument, &dopt.no_toast_compression, 1},
391+
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
391392
{"no-sync", no_argument, NULL, 7},
392393
{"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
393394
{"rows-per-insert", required_argument, NULL, 10},
@@ -909,10 +910,14 @@ main(int argc, char **argv)
909910
* order.
910911
*/
911912

912-
/* First the special ENCODING, STDSTRINGS, and SEARCHPATH entries. */
913+
/*
914+
* First the special entries for ENCODING, STDSTRINGS, SEARCHPATH and
915+
* TOASTCOMPRESSION.
916+
*/
913917
dumpEncoding(fout);
914918
dumpStdStrings(fout);
915919
dumpSearchPath(fout);
920+
dumpToastCompression(fout);
916921

917922
/* The database items are always next, unless we don't want them at all */
918923
if (dopt.outputCreateDB)
@@ -1048,9 +1053,9 @@ help(const char *progname)
10481053
printf(_(" --no-publications do not dump publications\n"));
10491054
printf(_(" --no-security-labels do not dump security label assignments\n"));
10501055
printf(_(" --no-subscriptions do not dump subscriptions\n"));
1051-
printf(_(" --no-toast-compression do not dump toast compression methods\n"));
10521056
printf(_(" --no-synchronized-snapshots do not use synchronized snapshots in parallel jobs\n"));
10531057
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
1058+
printf(_(" --no-toast-compression do not dump toast compression methods\n"));
10541059
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
10551060
printf(_(" --on-conflict-do-nothing add ON CONFLICT DO NOTHING to INSERT commands\n"));
10561061
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
@@ -3321,6 +3326,49 @@ dumpSearchPath(Archive *AH)
33213326
destroyPQExpBuffer(path);
33223327
}
33233328

3329+
/*
3330+
* dumpToastCompression: save the dump-time default TOAST compression in the
3331+
* archive
3332+
*/
3333+
static void
3334+
dumpToastCompression(Archive *AH)
3335+
{
3336+
const char *toast_compression;
3337+
PQExpBuffer qry;
3338+
PGresult *res;
3339+
3340+
if (AH->remoteVersion < 140000 || AH->dopt->no_toast_compression)
3341+
{
3342+
/* server doesn't support compression, or we don't care */
3343+
return;
3344+
}
3345+
3346+
res = ExecuteSqlQueryForSingleRow(AH, "SHOW default_toast_compression");
3347+
toast_compression = PQgetvalue(res, 0, 0);
3348+
3349+
qry = createPQExpBuffer();
3350+
appendPQExpBufferStr(qry, "SET default_toast_compression = ");
3351+
appendStringLiteralAH(qry, toast_compression, AH);
3352+
appendPQExpBufferStr(qry, ";\n");
3353+
3354+
pg_log_info("saving default_toast_compression = %s", toast_compression);
3355+
3356+
ArchiveEntry(AH, nilCatalogId, createDumpId(),
3357+
ARCHIVE_OPTS(.tag = "TOASTCOMPRESSION",
3358+
.description = "TOASTCOMPRESSION",
3359+
.section = SECTION_PRE_DATA,
3360+
.createStmt = qry->data));
3361+
3362+
/*
3363+
* Also save it in AH->default_toast_compression, in case we're doing
3364+
* plain text dump.
3365+
*/
3366+
AH->default_toast_compression = pg_strdup(toast_compression);
3367+
3368+
PQclear(res);
3369+
destroyPQExpBuffer(qry);
3370+
}
3371+
33243372

33253373
/*
33263374
* getBlobs:
@@ -8619,7 +8667,6 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
86198667
{
86208668
DumpOptions *dopt = fout->dopt;
86218669
PQExpBuffer q = createPQExpBuffer();
8622-
bool createWithCompression;
86238670

86248671
for (int i = 0; i < numTables; i++)
86258672
{
@@ -8686,6 +8733,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
86868733
appendPQExpBufferStr(q,
86878734
"0 AS attcollation,\n");
86888735

8736+
if (fout->remoteVersion >= 140000)
8737+
appendPQExpBuffer(q,
8738+
"a.attcompression AS attcompression,\n");
8739+
else
8740+
appendPQExpBuffer(q,
8741+
"'' AS attcompression,\n");
8742+
86898743
if (fout->remoteVersion >= 90200)
86908744
appendPQExpBufferStr(q,
86918745
"pg_catalog.array_to_string(ARRAY("
@@ -8705,15 +8759,6 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
87058759
appendPQExpBufferStr(q,
87068760
"'' AS attidentity,\n");
87078761

8708-
createWithCompression = (fout->remoteVersion >= 140000);
8709-
8710-
if (createWithCompression)
8711-
appendPQExpBuffer(q,
8712-
"a.attcompression AS attcompression,\n");
8713-
else
8714-
appendPQExpBuffer(q,
8715-
"NULL AS attcompression,\n");
8716-
87178762
if (fout->remoteVersion >= 110000)
87188763
appendPQExpBufferStr(q,
87198764
"CASE WHEN a.atthasmissing AND NOT a.attisdropped "
@@ -8757,9 +8802,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
87578802
tbinfo->attislocal = (bool *) pg_malloc(ntups * sizeof(bool));
87588803
tbinfo->attoptions = (char **) pg_malloc(ntups * sizeof(char *));
87598804
tbinfo->attcollation = (Oid *) pg_malloc(ntups * sizeof(Oid));
8805+
tbinfo->attcompression = (char *) pg_malloc(ntups * sizeof(char));
87608806
tbinfo->attfdwoptions = (char **) pg_malloc(ntups * sizeof(char *));
87618807
tbinfo->attmissingval = (char **) pg_malloc(ntups * sizeof(char *));
8762-
tbinfo->attcompression = (char *) pg_malloc(ntups * sizeof(char *));
87638808
tbinfo->notnull = (bool *) pg_malloc(ntups * sizeof(bool));
87648809
tbinfo->inhNotNull = (bool *) pg_malloc(ntups * sizeof(bool));
87658810
tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(ntups * sizeof(AttrDefInfo *));
@@ -8786,9 +8831,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
87868831
tbinfo->notnull[j] = (PQgetvalue(res, j, PQfnumber(res, "attnotnull"))[0] == 't');
87878832
tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, j, PQfnumber(res, "attoptions")));
87888833
tbinfo->attcollation[j] = atooid(PQgetvalue(res, j, PQfnumber(res, "attcollation")));
8834+
tbinfo->attcompression[j] = *(PQgetvalue(res, j, PQfnumber(res, "attcompression")));
87898835
tbinfo->attfdwoptions[j] = pg_strdup(PQgetvalue(res, j, PQfnumber(res, "attfdwoptions")));
87908836
tbinfo->attmissingval[j] = pg_strdup(PQgetvalue(res, j, PQfnumber(res, "attmissingval")));
8791-
tbinfo->attcompression[j] = *(PQgetvalue(res, j, PQfnumber(res, "attcompression")));
87928837
tbinfo->attrdefs[j] = NULL; /* fix below */
87938838
if (PQgetvalue(res, j, PQfnumber(res, "atthasdef"))[0] == 't')
87948839
hasdefaults = true;
@@ -15905,31 +15950,6 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
1590515950
tbinfo->atttypnames[j]);
1590615951
}
1590715952

15908-
/*
15909-
* Attribute compression
15910-
*/
15911-
if (!dopt->no_toast_compression &&
15912-
tbinfo->attcompression != NULL)
15913-
{
15914-
char *cmname;
15915-
15916-
switch (tbinfo->attcompression[j])
15917-
{
15918-
case 'p':
15919-
cmname = "pglz";
15920-
break;
15921-
case 'l':
15922-
cmname = "lz4";
15923-
break;
15924-
default:
15925-
cmname = NULL;
15926-
break;
15927-
}
15928-
15929-
if (cmname != NULL)
15930-
appendPQExpBuffer(q, " COMPRESSION %s", cmname);
15931-
}
15932-
1593315953
if (print_default)
1593415954
{
1593515955
if (tbinfo->attgenerated[j] == ATTRIBUTE_GENERATED_STORED)
@@ -16348,7 +16368,36 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
1634816368
qualrelname,
1634916369
fmtId(tbinfo->attnames[j]),
1635016370
tbinfo->attfdwoptions[j]);
16351-
}
16371+
16372+
/*
16373+
* Dump per-column compression, if different from default.
16374+
*/
16375+
if (!dopt->no_toast_compression)
16376+
{
16377+
const char *cmname;
16378+
16379+
switch (tbinfo->attcompression[j])
16380+
{
16381+
case 'p':
16382+
cmname = "pglz";
16383+
break;
16384+
case 'l':
16385+
cmname = "lz4";
16386+
break;
16387+
default:
16388+
cmname = NULL;
16389+
break;
16390+
}
16391+
16392+
if (cmname != NULL &&
16393+
(fout->default_toast_compression == NULL ||
16394+
strcmp(cmname, fout->default_toast_compression) != 0))
16395+
appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET COMPRESSION %s;\n",
16396+
foreign, qualrelname,
16397+
fmtId(tbinfo->attnames[j]),
16398+
cmname);
16399+
}
16400+
} /* end loop over columns */
1635216401

1635316402
if (ftoptions)
1635416403
free(ftoptions);

‎src/bin/pg_dump/pg_dump.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -316,6 +316,7 @@ typedef struct _tableInfo
316316
bool *attislocal; /* true if attr has local definition */
317317
char **attoptions; /* per-attribute options */
318318
Oid *attcollation; /* per-attribute collation selection */
319+
char *attcompression; /* per-attribute compression method */
319320
char **attfdwoptions; /* per-attribute fdw options */
320321
char **attmissingval; /* per attribute missing value */
321322
bool *notnull; /* NOT NULL constraints on attributes */
@@ -326,7 +327,6 @@ typedef struct _tableInfo
326327
char *partbound; /* partition bound definition */
327328
bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */
328329
char *amname; /* relation access method */
329-
char *attcompression; /* per-attribute current compression method */
330330

331331
/*
332332
* Stuff computed only for dumpable tables.

‎src/bin/pg_dump/t/002_pg_dump.pl

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2284,9 +2284,9 @@
22842284
regexp => qr/^
22852285
\QCREATE TABLE dump_test.test_table (\E\n
22862286
\s+\Qcol1 integer NOT NULL,\E\n
2287-
\s+\Qcol2 text COMPRESSION\E\D*,\n
2288-
\s+\Qcol3 text COMPRESSION\E\D*,\n
2289-
\s+\Qcol4 text COMPRESSION\E\D*,\n
2287+
\s+\Qcol2 text,\E\n
2288+
\s+\Qcol3 text,\E\n
2289+
\s+\Qcol4 text,\E\n
22902290
\s+\QCONSTRAINT test_table_col1_check CHECK ((col1 <= 1000))\E\n
22912291
\Q)\E\n
22922292
\QWITH (autovacuum_enabled='false', fillfactor='80');\E\n/xm,
@@ -2326,7 +2326,7 @@
23262326
regexp => qr/^
23272327
\QCREATE TABLE dump_test.test_second_table (\E
23282328
\n\s+\Qcol1 integer,\E
2329-
\n\s+\Qcol2 text COMPRESSION\E\D*
2329+
\n\s+\Qcol2 text\E
23302330
\n\);
23312331
/xm,
23322332
like =>
@@ -2441,7 +2441,7 @@
24412441
\n\s+\Qcol1 integer,\E
24422442
\n\s+\Qcol2 boolean,\E
24432443
\n\s+\Qcol3 boolean,\E
2444-
\n\s+\Qcol4 bit(5) COMPRESSION\E\D*,
2444+
\n\s+\Qcol4 bit(5),\E
24452445
\n\s+\Qcol5 double precision\E
24462446
\n\);
24472447
/xm,
@@ -2459,7 +2459,7 @@
24592459
regexp => qr/^
24602460
\QCREATE TABLE dump_test.test_table_identity (\E\n
24612461
\s+\Qcol1 integer NOT NULL,\E\n
2462-
\s+\Qcol2 text COMPRESSION\E\D*\n
2462+
\s+\Qcol2 text\E\n
24632463
\);
24642464
.*
24652465
\QALTER TABLE dump_test.test_table_identity ALTER COLUMN col1 ADD GENERATED ALWAYS AS IDENTITY (\E\n

0 commit comments

Comments
 (0)