Hi
2015-03-15 16:09 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us>:
> Pavel Stehule <pavel.steh...@gmail.com> writes:
> > other variant, I hope better than previous. We can introduce new long
> > option "--strict". With this active option, every pattern specified by -t
> > option have to have identifies exactly only one table. It can be used for
> > any other "should to exists" patterns - schemas. Initial implementation
> in
> > attachment.
>
> I think this design is seriously broken. If I have '-t foo*' the code
> should not prevent that from matching multiple tables. What would the use
> case for such a restriction be?
>
> What would make sense to me is one or both of these ideas:
>
> * require a match for a wildcard-free -t switch
>
> * require at least one (not "exactly one") match for a wildcarded -t
> switch.
>
attached initial implementation
Regards
Pavel
>
> Neither of those is what you wrote, though.
>
> If we implemented the second one of these, it would have to be controlled
> by a new switch, because there are plausible use cases for wildcards that
> sometimes don't match anything (not to mention backwards compatibility).
> There might be a reasonable argument for the first one being the
> default behavior, though; I'm not sure if we could get away with that
> from a compatibility perspective.
>
> regards, tom lane
>
commit c1c7d9671a751bda1918d479b81c38c538701ec1
Author: Pavel Stehule <pavel.steh...@gooddata.com>
Date: Mon Mar 23 17:06:39 2015 +0100
initial
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index d7506e1..47ae6b8 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -983,7 +983,8 @@ bool
processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
- const char *altnamevar, const char *visibilityrule)
+ const char *altnamevar, const char *visibilityrule,
+ bool *with_wildcards)
{
PQExpBufferData schemabuf;
PQExpBufferData namebuf;
@@ -997,6 +998,10 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
(appendPQExpBufferStr(buf, have_where ? " AND " : "WHERE "), \
have_where = true, added_clause = true)
+#define SET_WITH_WILDCARDS(b) if (with_wildcards) *with_wildcards = b;
+
+ SET_WITH_WILDCARDS(false);
+
if (pattern == NULL)
{
/* Default: select all visible objects */
@@ -1055,11 +1060,15 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
{
appendPQExpBufferStr(&namebuf, ".*");
cp++;
+
+ SET_WITH_WILDCARDS(true);
}
else if (!inquotes && ch == '?')
{
appendPQExpBufferChar(&namebuf, '.');
cp++;
+
+ SET_WITH_WILDCARDS(true);
}
else if (!inquotes && ch == '.')
{
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
index b176746..7fb7b62 100644
--- a/src/bin/pg_dump/dumputils.h
+++ b/src/bin/pg_dump/dumputils.h
@@ -94,7 +94,8 @@ extern bool processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
- const char *altnamevar, const char *visibilityrule);
+ const char *altnamevar, const char *visibilityrule,
+ bool *with_wildcards);
extern void buildShSecLabelQuery(PGconn *conn, const char *catalog_name,
uint32 objectId, PQExpBuffer sql);
extern void emitShSecLabels(PGconn *conn, PGresult *res,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f24fefa..dd1d813 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -114,6 +114,7 @@ static SimpleStringList table_exclude_patterns = {NULL, NULL};
static SimpleOidList table_exclude_oids = {NULL, NULL};
static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
+static SimpleStringList optional_tables = {NULL, NULL};
char g_opaque_type[10]; /* name for the opaque type */
@@ -132,7 +133,7 @@ static ArchiveFormat parseArchiveFormat(const char *format, ArchiveMode *mode);
static void expand_schema_name_patterns(Archive *fout,
SimpleStringList *patterns,
SimpleOidList *oids);
-static void expand_table_name_patterns(Archive *fout,
+static void expand_table_name_patterns(Archive *fout, bool check_patterns,
SimpleStringList *patterns,
SimpleOidList *oids);
static NamespaceInfo *findNamespace(Archive *fout, Oid nsoid, Oid objoid);
@@ -332,6 +333,7 @@ main(int argc, char **argv)
{"section", required_argument, NULL, 5},
{"serializable-deferrable", no_argument, &dopt.serializable_deferrable, 1},
{"snapshot", required_argument, NULL, 6},
+ {"table-if-exists", required_argument, NULL, 7},
{"use-set-session-authorization", no_argument, &dopt.use_setsessauth, 1},
{"no-security-labels", no_argument, &dopt.no_security_labels, 1},
{"no-synchronized-snapshots", no_argument, &dopt.no_synchronized_snapshots, 1},
@@ -515,6 +517,10 @@ main(int argc, char **argv)
dumpsnapshot = pg_strdup(optarg);
break;
+ case 7: /* optional table dump */
+ simple_string_list_append(&optional_tables, optarg);
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit_nicely(1);
@@ -697,17 +703,24 @@ main(int argc, char **argv)
/* non-matching exclusion patterns aren't an error */
/* Expand table selection patterns into OID lists */
- if (table_include_patterns.head != NULL)
+ if (table_include_patterns.head != NULL || optional_tables.head != NULL)
{
- expand_table_name_patterns(fout, &table_include_patterns,
+ expand_table_name_patterns(fout, true, &table_include_patterns,
+ &table_include_oids);
+
+ expand_table_name_patterns(fout, true, &table_include_patterns,
+ &table_include_oids);
+
+ expand_table_name_patterns(fout, false, &optional_tables,
&table_include_oids);
+
if (table_include_oids.head == NULL)
exit_horribly(NULL, "No matching tables were found\n");
}
- expand_table_name_patterns(fout, &table_exclude_patterns,
+ expand_table_name_patterns(fout, false, &table_exclude_patterns,
&table_exclude_oids);
- expand_table_name_patterns(fout, &tabledata_exclude_patterns,
+ expand_table_name_patterns(fout, false, &tabledata_exclude_patterns,
&tabledata_exclude_oids);
/* non-matching exclusion patterns aren't an error */
@@ -903,6 +916,7 @@ help(const char *progname)
printf(_(" --section=SECTION dump named section (pre-data, data, or post-data)\n"));
printf(_(" --serializable-deferrable wait until the dump can run without anomalies\n"));
printf(_(" --snapshot=SNAPSHOT use given synchronous snapshot for the dump\n"));
+ printf(_(" --table-if-exists=TABLE optional dump table\n"));
printf(_(" --use-set-session-authorization\n"
" use SET SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
@@ -1159,7 +1173,7 @@ expand_schema_name_patterns(Archive *fout,
appendPQExpBuffer(query,
"SELECT oid FROM pg_catalog.pg_namespace n\n");
processSQLNamePattern(GetConnection(fout), query, cell->val, false,
- false, NULL, "n.nspname", NULL, NULL);
+ false, NULL, "n.nspname", NULL, NULL, NULL);
}
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -1176,20 +1190,36 @@ expand_schema_name_patterns(Archive *fout,
/*
* Find the OIDs of all tables matching the given list of patterns,
* and append them to the given OID list.
+ *
+ * When check_patterns is true, then ensure any pattern without wildcards has to specify one or
+ * more tables.
*/
static void
-expand_table_name_patterns(Archive *fout,
+expand_table_name_patterns(Archive *fout, bool check_patterns,
SimpleStringList *patterns, SimpleOidList *oids)
{
PQExpBuffer query;
+ PQExpBuffer query_nonempty_check;
+ PQExpBuffer filter;
PGresult *res;
SimpleStringListCell *cell;
int i;
+#define SEARCH_TABLE_OID_QUERY_WITH_FILTER \
+ "SELECT c.oid" \
+ "\nFROM pg_catalog.pg_class c" \
+ "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace" \
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n" \
+ "%s", \
+ RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW, \
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE
+
if (patterns->head == NULL)
return; /* nothing to do */
query = createPQExpBuffer();
+ query_nonempty_check = createPQExpBuffer();
+ filter = createPQExpBuffer();
/*
* We use UNION ALL rather than UNION; this might sometimes result in
@@ -1198,28 +1228,56 @@ expand_table_name_patterns(Archive *fout,
for (cell = patterns->head; cell; cell = cell->next)
{
- if (cell != patterns->head)
- appendPQExpBufferStr(query, "UNION ALL\n");
- appendPQExpBuffer(query,
- "SELECT c.oid"
- "\nFROM pg_catalog.pg_class c"
- "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
- RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
- RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
- processSQLNamePattern(GetConnection(fout), query, cell->val, true,
+ bool with_wildcards;
+
+ processSQLNamePattern(GetConnection(fout), filter, cell->val, true,
false, "n.nspname", "c.relname", NULL,
- "pg_catalog.pg_table_is_visible(c.oid)");
- }
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ &with_wildcards);
- res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ if (check_patterns && !with_wildcards)
+ {
+ appendPQExpBuffer(query_nonempty_check,
+ SEARCH_TABLE_OID_QUERY_WITH_FILTER,
+ filter->data);
- for (i = 0; i < PQntuples(res); i++)
+ res = ExecuteSqlQuery(fout, query_nonempty_check->data, PGRES_TUPLES_OK);
+ if (PQntuples(res) == 0)
+ exit_horribly(NULL, "No matching table were found for \"%s\"\n", cell->val);
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
+ }
+
+ PQclear(res);
+ resetPQExpBuffer(query_nonempty_check);
+ }
+ else
+ {
+ if (cell != patterns->head)
+ appendPQExpBufferStr(query, "UNION ALL\n");
+ appendPQExpBuffer(query,
+ SEARCH_TABLE_OID_QUERY_WITH_FILTER,
+ filter->data);
+ }
+
+ resetPQExpBuffer(filter);
+ }
+
+ if (*query->data != '\0')
{
- simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
+ }
+
+ PQclear(res);
}
- PQclear(res);
+ destroyPQExpBuffer(filter);
destroyPQExpBuffer(query);
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
