2015-03-23 17:11 GMT+01:00 Pavel Stehule <[email protected]>:
> Hi
>
> 2015-03-15 16:09 GMT+01:00 Tom Lane <[email protected]>:
>
>> Pavel Stehule <[email protected]> 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
>
updated version - same mechanism should be used for schema
Regards
Pavel
>
> 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
>>
>
>
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
new file mode 100644
index d7506e1..47ae6b8
*** a/src/bin/pg_dump/dumputils.c
--- b/src/bin/pg_dump/dumputils.c
*************** bool
*** 983,989 ****
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)
{
PQExpBufferData schemabuf;
PQExpBufferData namebuf;
--- 983,990 ----
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,
! bool *with_wildcards)
{
PQExpBufferData schemabuf;
PQExpBufferData namebuf;
*************** processSQLNamePattern(PGconn *conn, PQEx
*** 997,1002 ****
--- 998,1007 ----
(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 */
*************** processSQLNamePattern(PGconn *conn, PQEx
*** 1055,1065 ****
--- 1060,1074 ----
{
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
new file mode 100644
index b176746..7fb7b62
*** a/src/bin/pg_dump/dumputils.h
--- b/src/bin/pg_dump/dumputils.h
*************** extern bool processSQLNamePattern(PGconn
*** 94,100 ****
const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
! const char *altnamevar, const char *visibilityrule);
extern void buildShSecLabelQuery(PGconn *conn, const char *catalog_name,
uint32 objectId, PQExpBuffer sql);
extern void emitShSecLabels(PGconn *conn, PGresult *res,
--- 94,101 ----
const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
! 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
new file mode 100644
index f24fefa..ff1e6a0
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** static SimpleStringList schema_include_p
*** 107,112 ****
--- 107,113 ----
static SimpleOidList schema_include_oids = {NULL, NULL};
static SimpleStringList schema_exclude_patterns = {NULL, NULL};
static SimpleOidList schema_exclude_oids = {NULL, NULL};
+ static SimpleStringList schema_optional_patterns = {NULL, NULL};
static SimpleStringList table_include_patterns = {NULL, NULL};
static SimpleOidList table_include_oids = {NULL, NULL};
*************** static SimpleStringList table_exclude_pa
*** 114,119 ****
--- 115,121 ----
static SimpleOidList table_exclude_oids = {NULL, NULL};
static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
+ static SimpleStringList table_optional_patterns = {NULL, NULL};
char g_opaque_type[10]; /* name for the opaque type */
*************** static void setup_connection(Archive *AH
*** 129,138 ****
const char *dumpencoding, const char *dumpsnapshot,
char *use_role);
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,
SimpleStringList *patterns,
SimpleOidList *oids);
static NamespaceInfo *findNamespace(Archive *fout, Oid nsoid, Oid objoid);
--- 131,140 ----
const char *dumpencoding, const char *dumpsnapshot,
char *use_role);
static ArchiveFormat parseArchiveFormat(const char *format, ArchiveMode *mode);
! static void expand_schema_name_patterns(Archive *fout, bool check_patterns,
SimpleStringList *patterns,
SimpleOidList *oids);
! static void expand_table_name_patterns(Archive *fout, bool check_patterns,
SimpleStringList *patterns,
SimpleOidList *oids);
static NamespaceInfo *findNamespace(Archive *fout, Oid nsoid, Oid objoid);
*************** main(int argc, char **argv)
*** 329,337 ****
--- 331,341 ----
{"no-tablespaces", no_argument, &dopt.outputNoTablespaces, 1},
{"quote-all-identifiers", no_argument, "e_all_identifiers, 1},
{"role", required_argument, NULL, 3},
+ {"schema-if-exists", required_argument, NULL, 7},
{"section", required_argument, NULL, 5},
{"serializable-deferrable", no_argument, &dopt.serializable_deferrable, 1},
{"snapshot", required_argument, NULL, 6},
+ {"table-if-exists", required_argument, NULL, 8},
{"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},
*************** main(int argc, char **argv)
*** 515,520 ****
--- 519,532 ----
dumpsnapshot = pg_strdup(optarg);
break;
+ case 7: /* optional schema dump */
+ simple_string_list_append(&schema_optional_patterns, optarg);
+ break;
+
+ case 8: /* optional table dump */
+ simple_string_list_append(&table_optional_patterns, optarg);
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit_nicely(1);
*************** main(int argc, char **argv)
*** 685,713 ****
}
/* Expand schema selection patterns into OID lists */
! if (schema_include_patterns.head != NULL)
{
! expand_schema_name_patterns(fout, &schema_include_patterns,
&schema_include_oids);
if (schema_include_oids.head == NULL)
exit_horribly(NULL, "No matching schemas were found\n");
}
! expand_schema_name_patterns(fout, &schema_exclude_patterns,
&schema_exclude_oids);
/* non-matching exclusion patterns aren't an error */
/* Expand table selection patterns into OID lists */
! if (table_include_patterns.head != NULL)
{
! expand_table_name_patterns(fout, &table_include_patterns,
&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,
&table_exclude_oids);
! expand_table_name_patterns(fout, &tabledata_exclude_patterns,
&tabledata_exclude_oids);
/* non-matching exclusion patterns aren't an error */
--- 697,733 ----
}
/* Expand schema selection patterns into OID lists */
! if (schema_include_patterns.head != NULL || schema_optional_patterns.head != NULL)
{
! expand_schema_name_patterns(fout, true, &schema_include_patterns,
! &schema_include_oids);
!
! expand_schema_name_patterns(fout, false, &schema_optional_patterns,
&schema_include_oids);
+
if (schema_include_oids.head == NULL)
exit_horribly(NULL, "No matching schemas were found\n");
}
! expand_schema_name_patterns(fout, false, &schema_exclude_patterns,
&schema_exclude_oids);
/* non-matching exclusion patterns aren't an error */
/* Expand table selection patterns into OID lists */
! if (table_include_patterns.head != NULL || table_optional_patterns.head != NULL)
{
! expand_table_name_patterns(fout, true, &table_include_patterns,
! &table_include_oids);
!
! expand_table_name_patterns(fout, false, &table_optional_patterns,
&table_include_oids);
+
if (table_include_oids.head == NULL)
exit_horribly(NULL, "No matching tables were found\n");
}
! expand_table_name_patterns(fout, false, &table_exclude_patterns,
&table_exclude_oids);
! expand_table_name_patterns(fout, false, &tabledata_exclude_patterns,
&tabledata_exclude_oids);
/* non-matching exclusion patterns aren't an error */
*************** help(const char *progname)
*** 900,908 ****
--- 920,930 ----
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
+ printf(_(" --schema-if-exists=SCHEMA optional dump of schema\n"));
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 of table\n"));
printf(_(" --use-set-session-authorization\n"
" use SET SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
*************** parseArchiveFormat(const char *format, A
*** 1130,1144 ****
* and append them to the given OID list.
*/
static void
! expand_schema_name_patterns(Archive *fout,
SimpleStringList *patterns,
SimpleOidList *oids)
{
PQExpBuffer query;
PGresult *res;
SimpleStringListCell *cell;
int i;
if (patterns->head == NULL)
return; /* nothing to do */
--- 1152,1171 ----
* and append them to the given OID list.
*/
static void
! expand_schema_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_SCHEMA_OID_QUERY_WITH_FILTER \
+ "SELECT oid FROM pg_catalog.pg_namespace n\n%s"
+
if (patterns->head == NULL)
return; /* nothing to do */
*************** expand_schema_name_patterns(Archive *fou
*** 1146,1151 ****
--- 1173,1180 ----
exit_horribly(NULL, "server version must be at least 7.3 to use schema selection switches\n");
query = createPQExpBuffer();
+ query_nonempty_check = createPQExpBuffer();
+ filter = createPQExpBuffer();
/*
* We use UNION ALL rather than UNION; this might sometimes result in
*************** expand_schema_name_patterns(Archive *fou
*** 1154,1195 ****
for (cell = patterns->head; cell; cell = cell->next)
{
! if (cell != patterns->head)
! appendPQExpBufferStr(query, "UNION ALL\n");
! appendPQExpBuffer(query,
! "SELECT oid FROM pg_catalog.pg_namespace n\n");
! processSQLNamePattern(GetConnection(fout), query, cell->val, false,
! false, NULL, "n.nspname", NULL, NULL);
! }
! 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);
destroyPQExpBuffer(query);
}
/*
* Find the OIDs of all tables matching the given list of patterns,
* and append them to the given OID list.
*/
static void
! expand_table_name_patterns(Archive *fout,
SimpleStringList *patterns, SimpleOidList *oids)
{
PQExpBuffer query;
PGresult *res;
SimpleStringListCell *cell;
int i;
if (patterns->head == NULL)
return; /* nothing to do */
query = createPQExpBuffer();
/*
* We use UNION ALL rather than UNION; this might sometimes result in
--- 1183,1274 ----
for (cell = patterns->head; cell; cell = cell->next)
{
! bool with_wildcards;
! processSQLNamePattern(GetConnection(fout), filter, cell->val, false,
! false, NULL, "n.nspname", NULL, NULL,
! &with_wildcards);
! if (check_patterns && !with_wildcards)
! {
! appendPQExpBuffer(query_nonempty_check,
! SEARCH_SCHEMA_OID_QUERY_WITH_FILTER,
! filter->data);
!
! res = ExecuteSqlQuery(fout, query_nonempty_check->data, PGRES_TUPLES_OK);
! if (PQntuples(res) == 0)
! exit_horribly(NULL, "No matching schema 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_SCHEMA_OID_QUERY_WITH_FILTER,
! filter->data);
! }
!
! resetPQExpBuffer(filter);
! }
!
! if (*query->data != '\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);
}
! destroyPQExpBuffer(filter);
! destroyPQExpBuffer(query_nonempty_check);
destroyPQExpBuffer(query);
}
/*
* 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, 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
*************** expand_table_name_patterns(Archive *fout
*** 1198,1225 ****
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,
false, "n.nspname", "c.relname", NULL,
! "pg_catalog.pg_table_is_visible(c.oid)");
! }
! 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);
destroyPQExpBuffer(query);
}
--- 1277,1333 ----
for (cell = patterns->head; cell; cell = cell->next)
{
! bool with_wildcards;
!
! processSQLNamePattern(GetConnection(fout), filter, cell->val, true,
false, "n.nspname", "c.relname", NULL,
! "pg_catalog.pg_table_is_visible(c.oid)",
! &with_wildcards);
! if (check_patterns && !with_wildcards)
! {
! appendPQExpBuffer(query_nonempty_check,
! SEARCH_TABLE_OID_QUERY_WITH_FILTER,
! filter->data);
! 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')
{
! 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);
}
! destroyPQExpBuffer(filter);
! destroyPQExpBuffer(query_nonempty_check);
destroyPQExpBuffer(query);
}
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
