Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jv2kK-0003HX-Dq for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Jul 2020 18:00:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jv2kI-0006Wu-DD for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Jul 2020 18:00:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jv2kH-0006Wn-W9 for pgsql-hackers@lists.postgresql.org; Mon, 13 Jul 2020 18:00:06 +0000 Received: from mail-il1-x135.google.com ([2607:f8b0:4864:20::135]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jv2kF-0005nv-1S for pgsql-hackers@postgresql.org; Mon, 13 Jul 2020 18:00:05 +0000 Received: by mail-il1-x135.google.com with SMTP id o3so11942000ilo.12 for ; Mon, 13 Jul 2020 11:00:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=vzHXtixDpqXdFkFC5ghdhn0o0FyaQ4fi1a7O9xdUWCQ=; b=HBRVrwIapianTpUM0Dvei/B4Sy5VHhSJdPVWZs/F2QrofOj1pgCa/aVj66ZyTht4GN MrT3tHzsnWdOLxHdoVHhw5wxsFqmVw5Z676UjfXMp8FYepVowXNrZ7HAV8K0zGHLs2Co d0om2BHHOxTVdSeM6PyKnbAKEKBoxJKvpwG9YRmiWTlZcmiF2/YnK956ZyBcad7Aw66E l+cGGwnQ0yQ6jlIXq3JOcCTZX0Qfpjp/j/i/30Kout3lpAsxN/5EBPV1T7jLV1KAJDJl +3MWID0VVVdBzvarfQHJ+1lvkntlomoB4ROqpFOvJOo2dVh9fCaErj9YJ0rAZwgHY3u1 Vj5Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=vzHXtixDpqXdFkFC5ghdhn0o0FyaQ4fi1a7O9xdUWCQ=; b=EpzErQeesM9kGKnBclc975xuwPBDVgOI5FD9tCISBRryCiZQ28p6+gkZPL4SakIJbc ljdSklUA2Xfo6PBfL14aspR2aDNc7EUt7ucl4vVLbIdh13gSi7soA1exudWCJqqVtRMA md2TUB8wFTCQjAdt2CGT8ux7iVWt8/quARRBTQ+tj8sYG9wsmsY1WEXA4R1yRRepxKeS 0RyBVHqp/UnO/KXE/7aYhVnUqzax6eTGrn8371DZ5z3vUCGvdgT1vGRz8C4VXgNHuSiP YtfsDXvzuv/mY/ud047MdsboglQEt+2wEn9nWavSwaibcWSJ44Ux9BFYotu7Scq+DmHH ZrGg== X-Gm-Message-State: AOAM533wGMmDeZ+uVx5BGdhJHMd633KKp3kXReGfZf8kpZullwVyGnqs qudetoqweFwwpeMmnFsNECGodA== X-Google-Smtp-Source: ABdhPJxnigwJvmDzAm6GHDl/QXv1wPgowpkE2IFXFOcZCwKeYJnJLVJiPfM619t92FnQ3aHAgYJ5og== X-Received: by 2002:a92:3311:: with SMTP id a17mr964479ilf.210.1594663200906; Mon, 13 Jul 2020 11:00:00 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id u15sm8019142ior.51.2020.07.13.10.59.58 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 13 Jul 2020 10:59:59 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 0F6798009C4; Mon, 13 Jul 2020 12:59:57 -0500 (CDT) Date: Mon, 13 Jul 2020 12:59:57 -0500 From: Justin Pryzby To: Pavel Stehule Cc: pgsql-hackers@postgresql.org Subject: Re: proposal: possibility to read dumped table's name from file Message-ID: <20200713175957.GI23581@telsasoft.com> References: <20200608212957.GB22473@telsasoft.com> <20200609223042.GF26811@telsasoft.com> <20200701212452.GF4107@telsasoft.com> <20200705203119.GT4107@telsasoft.com> <20200711230650.GY26220@telsasoft.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="H4SyuGOnfnj3aJqJ" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --H4SyuGOnfnj3aJqJ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Mon, Jul 13, 2020 at 08:15:42AM +0200, Pavel Stehule wrote: > > Do you want to add any more documentation ? > > > > done Thanks - I think the documentation was maybe excessive. See attached. -- Justin --H4SyuGOnfnj3aJqJ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0001-proposal-possibility-to-read-dumped-table-s-name-fro.patch" From b6ceedcd7f4395fac822059229cb475aa2805c1e Mon Sep 17 00:00:00 2001 From: Pavel Stehule Date: Mon, 13 Jul 2020 10:20:42 +0200 Subject: [PATCH 1/2] proposal: possibility to read dumped table's name from file --- doc/src/sgml/ref/pg_dump.sgml | 93 +++++++++++++++ src/bin/pg_dump/pg_dump.c | 215 ++++++++++++++++++++++++++++++++++ 2 files changed, 308 insertions(+) diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 7a37fd8045..2f2bfb4dbf 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -755,6 +755,99 @@ PostgreSQL documentation + + + + + This option ensure reading object's filters from specified file. + If you use "-" as filename, then stdin is used as source. This file + has to have following line format: + +(+|-)[tnfd] objectname + + Only one object name can be specified per one line: + ++t mytable1 ++t mytable2 ++f some_foreign_table +-d mytable3 + + With this file the dump ensures dump table mytable1, + mytable2. The data of foreign table + some_foreign_table will be dumped too. And the data + of mytable3 will not be dumped. + + + + The first char + or - specifies + if object name will be used as include or exclude filter. + + + + The second char + t, + n, + f, + d + specifies a object type. + + + + t + + + In inclusive form (+) it does same work like + . In exclusive form (-) + it is same like . + + + + + + n + + + In inclusive form (+) it does same work like + . In exclusive form (-) + it is same like . + + + + + + f + + + In inclusive form (+) it does same work like + . The exclusive form + (-) is not allowed. + + + + + + d + + + The inclusive form (+) is not allowed. + In exclusive form (-) it is same like + . + + + + + + + + The option can be used together with options + , , + , , + and + . + + + + diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index e758b5c50a..fd6b7a174a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -290,6 +290,7 @@ static void appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions, static char *get_synchronized_snapshot(Archive *fout); static void setupDumpWorker(Archive *AHX); static TableInfo *getRootTableInfo(TableInfo *tbinfo); +static void read_patterns_from_file(char *filename, DumpOptions *dopt); int @@ -364,6 +365,7 @@ main(int argc, char **argv) {"enable-row-security", no_argument, &dopt.enable_row_security, 1}, {"exclude-table-data", required_argument, NULL, 4}, {"extra-float-digits", required_argument, NULL, 8}, + {"filter", required_argument, NULL, 12}, {"if-exists", no_argument, &dopt.if_exists, 1}, {"inserts", no_argument, NULL, 9}, {"lock-wait-timeout", required_argument, NULL, 2}, @@ -603,6 +605,10 @@ main(int argc, char **argv) optarg); break; + case 12: /* filter implementation */ + read_patterns_from_file(optarg, &dopt); + break; + default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit_nicely(1); @@ -1022,6 +1028,7 @@ help(const char *progname) " access to)\n")); printf(_(" --exclude-table-data=PATTERN do NOT dump data for the specified table(s)\n")); printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n")); + printf(_(" --filter=FILENAME read object name filter expressions from file\n")); printf(_(" --if-exists use IF EXISTS when dropping objects\n")); printf(_(" --include-foreign-data=PATTERN\n" " include data of foreign tables on foreign\n" @@ -18597,3 +18604,211 @@ appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions, if (!res) pg_log_warning("could not parse reloptions array"); } + +#define FILTER_INITIAL_LINE_SIZE 1024 +#define PG_GETLINE_EXTEND_LINE_SIZE 1024 + +/* + * getline is originaly GNU function, and should not be everywhere still. + * Use own reduced implementation. + */ +static size_t +pg_getline(char **lineptr, size_t *n, FILE *fp) +{ + size_t total_chars = 0; + + while (!feof(fp) && !ferror(fp)) + { + char *str; + size_t chars; + + str = fgets(*lineptr + total_chars, + *n - total_chars, + fp); + + if (ferror(fp)) + return -1; + + if (str) + { + chars = strlen(str); + total_chars += chars; + + if (chars > 0 && str[chars - 1] == '\n') + return total_chars; + + /* check, if there is good enough space for next content */ + if (*n - total_chars < 2) + { + *n += PG_GETLINE_EXTEND_LINE_SIZE; + *lineptr = pg_realloc(*lineptr, *n); + } + } + else + break; + } + + if (ferror(fp)) + return -1; + + return total_chars > 0 ? total_chars : -1; +} + +/* + * Print error message and exit. + */ +static void +exit_invalid_filter_format(FILE *fp, char *filename, char *message, char *line, int lineno) +{ + pg_log_error("invalid format of filter file \"%s\": %s", + filename, + message); + + fprintf(stderr, "%d: %s\n", lineno, line); + + if (fp != stdin) + fclose(fp); + + exit_nicely(-1); +} + +/* + * Read dumped object specification from file + */ +static void +read_patterns_from_file(char *filename, DumpOptions *dopt) +{ + FILE *fp; + char *line; + ssize_t chars; + size_t line_size = FILTER_INITIAL_LINE_SIZE; + int lineno = 0; + + /* use "-" as symbol for stdin */ + if (strcmp(filename, "-") != 0) + { + fp = fopen(filename, "r"); + if (!fp) + fatal("could not open the input file \"%s\": %m", + filename); + } + else + fp = stdin; + + line = pg_malloc(line_size); + + while ((chars = pg_getline(&line, &line_size, fp)) != -1) + { + bool is_include; + char objecttype; + char *objectname; + + lineno += 1; + + if (line[chars - 1] == '\n') + line[chars - 1] = '\0'; + + /* ignore empty rows */ + if (*line == '\0') + continue; + + /* when first char is hash, ignore whole line */ + if (*line == '#') + continue; + + if (chars < 2) + exit_invalid_filter_format(fp, + filename, + "too short line", + line, + lineno); + + if (line[0] == '+') + is_include = true; + else if (line[0] == '-') + is_include = false; + else + exit_invalid_filter_format(fp, + filename, + "invalid option type (use [+-]", + line, + lineno); + + objecttype = line[1]; + objectname = &line[2]; + + /* skip initial spaces */ + while (isspace(*objectname)) + objectname++; + + if (*objectname == '\0') + exit_invalid_filter_format(fp, + filename, + "missing object name", + line, + lineno); + + if (objecttype == 't') + { + if (is_include) + { + simple_string_list_append(&table_include_patterns, + objectname); + dopt->include_everything = false; + } + else + simple_string_list_append(&table_exclude_patterns, + objectname); + } + else if (objecttype == 'n') + { + if (is_include) + { + simple_string_list_append(&schema_include_patterns, + objectname); + dopt->include_everything = false; + } + else + simple_string_list_append(&schema_exclude_patterns, + objectname); + } + else if (objecttype == 'd') + { + if (is_include) + exit_invalid_filter_format(fp, + filename, + "include filter is not supported for this type of object", + line, + lineno); + else + simple_string_list_append(&tabledata_exclude_patterns, + objectname); + } + else if (objecttype == 'f') + { + if (is_include) + simple_string_list_append(&foreign_servers_include_patterns, + objectname); + else + exit_invalid_filter_format(fp, + filename, + "exclude filter is not supported for this type of object", + line, + lineno); + } + else + exit_invalid_filter_format(fp, + filename, + "invalid object type (use [tndf])", + line, + lineno); + } + + if (ferror(fp)) + fatal("could not read from file \"%s\": %m", filename); + + if (fp != stdin) + fclose(fp); + + pg_free(line); +} -- 2.17.0 --H4SyuGOnfnj3aJqJ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0002-fixen.patch" From 56c66987d1b645a14932f5051ecbb983956e8b31 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Mon, 13 Jul 2020 12:57:34 -0500 Subject: [PATCH 2/2] fixen --- doc/src/sgml/ref/pg_dump.sgml | 107 +++++++++------------------------- src/bin/pg_dump/pg_dump.c | 4 +- 2 files changed, 31 insertions(+), 80 deletions(-) diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 2f2bfb4dbf..c9502dca13 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -759,91 +759,42 @@ PostgreSQL documentation - This option ensure reading object's filters from specified file. - If you use "-" as filename, then stdin is used as source. This file - has to have following line format: + Read objects filters from the specified file. + If you use "-" as a filename, the filters are read from stdin. + The lines of this file must have the following format: (+|-)[tnfd] objectname - Only one object name can be specified per one line: + + + + The first character specifies whether the object is to be included + (+) or excluded (-), and the + second character specifies the type of object to be filtered: + t (table), + n (schema), + f (foreign table), + d (table data). + + + + With the following filter file, the dump would include table + mytable1 and data from foreign table + some_foreign_table, but exclude data + from table mytable2. +t mytable1 -+t mytable2 +f some_foreign_table --d mytable3 +-d mytable2 - With this file the dump ensures dump table mytable1, - mytable2. The data of foreign table - some_foreign_table will be dumped too. And the data - of mytable3 will not be dumped. - - - - The first char + or - specifies - if object name will be used as include or exclude filter. - - - - The second char - t, - n, - f, - d - specifies a object type. - - - - t - - - In inclusive form (+) it does same work like - . In exclusive form (-) - it is same like . - - - - - - n - - - In inclusive form (+) it does same work like - . In exclusive form (-) - it is same like . - - - - - - f - - - In inclusive form (+) it does same work like - . The exclusive form - (-) is not allowed. - - - - - - d - - - The inclusive form (+) is not allowed. - In exclusive form (-) it is same like - . - - - - - - - - The option can be used together with options - , , - , , - and - . + + + + The option works just like the other + options to include or exclude tables, schemas, table data, or foreign + tables, and both forms may be combined. Note that there are no options + to exclude a specific foreign table or to include a specific table's + data. diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index fd6b7a174a..9f3da36cd5 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -18609,7 +18609,7 @@ appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions, #define PG_GETLINE_EXTEND_LINE_SIZE 1024 /* - * getline is originaly GNU function, and should not be everywhere still. + * getline is originally GNU function, and should not be everywhere still. * Use own reduced implementation. */ static size_t @@ -18719,7 +18719,7 @@ read_patterns_from_file(char *filename, DumpOptions *dopt) if (chars < 2) exit_invalid_filter_format(fp, filename, - "too short line", + "line too short", line, lineno); -- 2.17.0 --H4SyuGOnfnj3aJqJ--