From f23438aff8df88092c8c36cecec3f8980eb51db2 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Fri, 19 Mar 2021 23:43:32 -0500 Subject: [PATCH 9/9] WIP: pg_dump: use ALTER SET COMPRESSION rather than compression clauses within CREATE TABLE().. This allows dumps with non-default compression to be restored to a cluster before v14 compression support, or v14 --without-lz4. Note that allows pg_restore |psql, but pg_restore -d will still fail, since the entire CREATE TABLE..ALTER TABLE is execute within a simple query, and a single transaction. Discussion: XXX: the list archives are inaccessible... 20210308172915.GF29832@telsasoft.com 4193854.1616199943@sss.pgh.pa.us 20210320000625.GP11765@telsasoft.com --- src/bin/pg_dump/pg_backup_archiver.c | 24 +++++++++--- src/bin/pg_dump/pg_dump.c | 58 +++++++++++++++------------- src/bin/pg_dump/t/002_pg_dump.pl | 12 +++--- 3 files changed, 56 insertions(+), 38 deletions(-) diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index d7fcbc5410..b8a4f5933a 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -2759,11 +2759,22 @@ processSearchPathEntry(ArchiveHandle *AH, TocEntry *te) static void processToastCompressionEntry(ArchiveHandle *AH, TocEntry *te) { - /* - * te->defn should contain a command to set default_toast_compression. - * We just copy it verbatim for use later. - */ - AH->public.default_toast_compression = pg_strdup(te->defn); + /* te->defn should have the form SET default_toast_compression = 'x'; */ + char *defn = pg_strdup(te->defn); + char *ptr1, + *ptr2 = NULL; + + ptr1 = strchr(defn, '\''); + if (ptr1) + ptr2 = strchr(++ptr1, '\''); + if (ptr2) + { + *ptr2 = '\0'; + AH->public.default_toast_compression = pg_strdup(ptr1); + } + else + fatal("invalid TOAST_COMPRESSION item: %s", + te->defn); } static void @@ -3151,7 +3162,8 @@ _doSetFixedOutputState(ArchiveHandle *AH) /* Select the dump-time default_toast_compression */ if (AH->public.default_toast_compression) - ahprintf(AH, "%s", AH->public.default_toast_compression); + ahprintf(AH, "SET default_toast_compression = '%s';\n", + AH->public.default_toast_compression); /* Make sure function checking is disabled */ ahprintf(AH, "SET check_function_bodies = false;\n"); diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 12d1ebf466..5e4baa6e5e 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -3296,9 +3296,11 @@ dumpToastCompression(Archive *AH) /* * Also save it in AH->default_toast_compression, in case we're doing plain - * text dump + * text dump. The compression name is saved, not the create statement, to + * allow easily comparing it with each column's compression clause. + * XXX: maybe instead should just add a 2nd field to AH for the raw compression. */ - AH->default_toast_compression = pg_strdup(qry->data); + AH->default_toast_compression = pg_strdup(toast_compression); PQclear(res); destroyPQExpBuffer(qry); @@ -15949,30 +15951,6 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) tbinfo->atttypnames[j]); } - /* - * Attribute compression - */ - if (!dopt->no_toast_compression) - { - char *cmname; - - switch (tbinfo->attcompression[j]) - { - case 'p': - cmname = "pglz"; - break; - case 'l': - cmname = "lz4"; - break; - default: - cmname = NULL; - break; - } - - if (cmname != NULL) - appendPQExpBuffer(q, " COMPRESSION %s", cmname); - } - if (print_default) { if (tbinfo->attgenerated[j] == ATTRIBUTE_GENERATED_STORED) @@ -16391,6 +16369,34 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) qualrelname, fmtId(tbinfo->attnames[j]), tbinfo->attfdwoptions[j]); + + /* + * Dump per-column compression + */ + if (!dopt->no_toast_compression) + { + char *cmname; + + switch (tbinfo->attcompression[j]) + { + case 'p': + cmname = "pglz"; + break; + case 'l': + cmname = "lz4"; + break; + default: + cmname = NULL; + break; + } + + if (cmname != NULL && + strcmp(cmname, fout->default_toast_compression) != 0) + appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET COMPRESSION %s;\n", + foreign, qualrelname, + fmtId(tbinfo->attnames[j]), + cmname); + } } if (ftoptions) diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index bc91bb12ac..e7885fdd51 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2284,9 +2284,9 @@ my %tests = ( regexp => qr/^ \QCREATE TABLE dump_test.test_table (\E\n \s+\Qcol1 integer NOT NULL,\E\n - \s+\Qcol2 text COMPRESSION\E\D*,\n - \s+\Qcol3 text COMPRESSION\E\D*,\n - \s+\Qcol4 text COMPRESSION\E\D*,\n + \s+\Qcol2 text,\E\n + \s+\Qcol3 text,\E\n + \s+\Qcol4 text,\E\n \s+\QCONSTRAINT test_table_col1_check CHECK ((col1 <= 1000))\E\n \Q)\E\n \QWITH (autovacuum_enabled='false', fillfactor='80');\E\n/xm, @@ -2326,7 +2326,7 @@ my %tests = ( regexp => qr/^ \QCREATE TABLE dump_test.test_second_table (\E \n\s+\Qcol1 integer,\E - \n\s+\Qcol2 text COMPRESSION\E\D* + \n\s+\Qcol2 text\E \n\); /xm, like => @@ -2441,7 +2441,7 @@ my %tests = ( \n\s+\Qcol1 integer,\E \n\s+\Qcol2 boolean,\E \n\s+\Qcol3 boolean,\E - \n\s+\Qcol4 bit(5) COMPRESSION\E\D*, + \n\s+\Qcol4 bit(5),\E*, \n\s+\Qcol5 double precision\E \n\); /xm, @@ -2459,7 +2459,7 @@ my %tests = ( regexp => qr/^ \QCREATE TABLE dump_test.test_table_identity (\E\n \s+\Qcol1 integer NOT NULL,\E\n - \s+\Qcol2 text COMPRESSION\E\D*\n + \s+\Qcol2 text\E\n \); .* \QALTER TABLE dump_test.test_table_identity ALTER COLUMN col1 ADD GENERATED ALWAYS AS IDENTITY (\E\n -- 2.17.0