per-column FDW options, v5
Started by Shigeru Hanadaover 14 years ago17 messages
Here is a rebased version of per-column FDW options patch. I've
proposed this patch in last CF, but it was marked as returned with
feedback. So I would like to propose in next CF 2011-09. I already
moved CF item into new topic "SQL/MED" of CF 2011-09.
I didn't change the patch except rebasing against head, so this patch
includes changes below:
* New attribute attfdwoptions of pg_attribute hold per-column FDW
options, so this patch needs bumping catversion.
* CREATE/ALTER FOREIGN TABLE statement accept OPTIONS (key 'value')
syntax for per-column FDW options.
* New information_schema.column_options view shows per-column FDW
options of accessible foreign tables.
* \d+ shows per-column options, only for foreign tables.
* pg_dump support dumping per-column FDW options as ALTER FOREIGN TABLE
statement.
IIUC, one of the reasons why this patch hasn't been accepted is that we
don't have a consensus about storage for per-column FDW options. I
think that separating attributes would be better for several reasons:
* FDW options might conflict reloptions/attoptions.
* FDW options and reloptions have different syntax, so pg_dump would
need to switch the format depending on target table's relkind.
I also attached a rebased version of force_not_null patch, which adds
force_not_null option support to file_fdw. This is a use case of
per-column FDW option.
Regards,
--
Shigeru Hanada
* 英語 - 自動検出
* 英語
* 日本語
* 英語
* 日本語
<javascript:void(0);>
Attachments:
per_column_option_v5.patchtext/plain; name=per_column_option_v5.patchDownload
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 5e5f8a7..7c49cd0 100644
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***************
*** 1157,1162 ****
--- 1157,1171 ----
</entry>
</row>
+ <row>
+ <entry><structfield>attfdwoptions</structfield></entry>
+ <entry><type>text[]</type></entry>
+ <entry></entry>
+ <entry>
+ Attribute-level generic options, as <quote>keyword=value</> strings
+ </entry>
+ </row>
+
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index ed4f157..0f0cbfa 100644
*** a/doc/src/sgml/information_schema.sgml
--- b/doc/src/sgml/information_schema.sgml
***************
*** 1018,1023 ****
--- 1018,1086 ----
</table>
</sect1>
+ <sect1 id="infoschema-column-options">
+ <title><literal>column_options</literal></title>
+
+ <para>
+ The view <literal>column_options</literal> contains all the
+ options defined for foreign table columns in the current database. Only
+ those foreign table columns are shown that the current user has access to
+ (by way of being the owner or having some privilege).
+ </para>
+
+ <table>
+ <title><literal>column_options</literal> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>table_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the database that contains the foreign table (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>table_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the schema that contains the foreign table</entry>
+ </row>
+
+ <row>
+ <entry><literal>table_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the foreign table</entry>
+ </row>
+
+ <row>
+ <entry><literal>column_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the column</entry>
+ </row>
+
+ <row>
+ <entry><literal>option_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of an option</entry>
+ </row>
+
+ <row>
+ <entry><literal>option_value</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Value of the option</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="infoschema-column-privileges">
<title><literal>column_privileges</literal></title>
diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml
index a45df02..95ae02a 100644
*** a/doc/src/sgml/ref/alter_foreign_table.sgml
--- b/doc/src/sgml/ref/alter_foreign_table.sgml
*************** ALTER FOREIGN TABLE <replaceable class="
*** 36,41 ****
--- 36,42 ----
DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">type</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ])
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ])
</synopsis>
*************** ALTER FOREIGN TABLE <replaceable class="
*** 125,136 ****
<term><literal>OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ] )</literal></term>
<listitem>
<para>
! Change options for the foreign table.
<literal>ADD</>, <literal>SET</>, and <literal>DROP</>
specify the action to be performed. <literal>ADD</> is assumed
! if no operation is explicitly specified. Option names must be
! unique; names and values are also validated using the foreign
! data wrapper library.
</para>
</listitem>
</varlistentry>
--- 126,137 ----
<term><literal>OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ] )</literal></term>
<listitem>
<para>
! Change options for the foreign table or the column of the foreign table.
<literal>ADD</>, <literal>SET</>, and <literal>DROP</>
specify the action to be performed. <literal>ADD</> is assumed
! if no operation is explicitly specified. Option names must be unique
! in each associated object; names and values are also validated using the
! foreign data wrapper library.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index ad91072..8863386 100644
*** a/doc/src/sgml/ref/create_foreign_table.sgml
--- b/doc/src/sgml/ref/create_foreign_table.sgml
***************
*** 19,25 ****
<refsynopsisdiv>
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable> ( [
! { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ NULL | NOT NULL ] }
[, ... ]
] )
SERVER <replaceable class="parameter">server_name</replaceable>
--- 19,25 ----
<refsynopsisdiv>
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable> ( [
! { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ] [ NULL | NOT NULL ] }
[, ... ]
] )
SERVER <replaceable class="parameter">server_name</replaceable>
*************** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <
*** 138,147 ****
<term><literal>OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ...] )</literal></term>
<listitem>
<para>
! Options to be associated with the new foreign table.
The allowed option names and values are specific to each foreign
data wrapper and are validated using the foreign-data wrapper's
! validator function. Option names must be unique.
</para>
</listitem>
</varlistentry>
--- 138,149 ----
<term><literal>OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ...] )</literal></term>
<listitem>
<para>
! Options to be associated with the new foreign table or the column of
! the foreign table.
The allowed option names and values are specific to each foreign
data wrapper and are validated using the foreign-data wrapper's
! validator function. Option names must be unique in each associated
! object.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 54fc3ee..9e310a1 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** testdb=>
*** 891,896 ****
--- 891,903 ----
below.)
</para>
+ <para>
+ In addition to common information, <literal>\d</> shows
+ relation-kind-specific information for each column:
+ column values for sequences, indexed expression for indexes and
+ per-column generic options for foreign tables.
+ </para>
+
<para>
The command form <literal>\d+</literal> is identical, except that
more information is displayed: any comments associated with the
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 4656dba..9e931df 100644
*** a/src/backend/access/common/tupdesc.c
--- b/src/backend/access/common/tupdesc.c
*************** equalTupleDescs(TupleDesc tupdesc1, Tupl
*** 363,369 ****
return false;
if (attr1->attcollation != attr2->attcollation)
return false;
! /* attacl and attoptions are not even present... */
}
if (tupdesc1->constr != NULL)
--- 363,369 ----
return false;
if (attr1->attcollation != attr2->attcollation)
return false;
! /* attacl, attoptions and attfdwoptions are not even present... */
}
if (tupdesc1->constr != NULL)
*************** TupleDescInitEntry(TupleDesc desc,
*** 483,489 ****
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
! /* attacl and attoptions are not present in tupledescs */
tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(oidtypeid));
if (!HeapTupleIsValid(tuple))
--- 483,489 ----
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
! /* attacl, attoptions and attfdwoptions are not present in tupledescs */
tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(oidtypeid));
if (!HeapTupleIsValid(tuple))
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 0aeaf5b..d91af52 100644
*** a/src/backend/catalog/genbki.pl
--- b/src/backend/catalog/genbki.pl
*************** sub emit_pgattr_row
*** 369,375 ****
attislocal => 't',
attinhcount => '0',
attacl => '_null_',
! attoptions => '_null_'
);
return {%PGATTR_DEFAULTS, %row};
}
--- 369,376 ----
attislocal => 't',
attinhcount => '0',
attacl => '_null_',
! attoptions => '_null_',
! attfdwoptions => '_null_'
);
return {%PGATTR_DEFAULTS, %row};
}
*************** sub emit_schemapg_row
*** 400,405 ****
--- 401,407 ----
# Only the fixed-size portions of the descriptors are ever used.
delete $row->{attacl};
delete $row->{attoptions};
+ delete $row->{attfdwoptions};
# Expand booleans from 'f'/'t' to 'false'/'true'.
# Some values might be other macros (eg FLOAT4PASSBYVAL), don't change.
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 4399493..7ec6581 100644
*** a/src/backend/catalog/heap.c
--- b/src/backend/catalog/heap.c
*************** static List *insert_ordered_unique_oid(L
*** 126,132 ****
*/
/*
! * The initializers below do not include the attoptions or attacl fields,
* but that's OK - we're never going to reference anything beyond the
* fixed-size portion of the structure anyway.
*/
--- 126,132 ----
*/
/*
! * The initializers below do not include trailing variable length fields,
* but that's OK - we're never going to reference anything beyond the
* fixed-size portion of the structure anyway.
*/
*************** InsertPgAttributeTuple(Relation pg_attri
*** 620,625 ****
--- 620,626 ----
/* start out with empty permissions and empty options */
nulls[Anum_pg_attribute_attacl - 1] = true;
nulls[Anum_pg_attribute_attoptions - 1] = true;
+ nulls[Anum_pg_attribute_attfdwoptions - 1] = true;
tup = heap_form_tuple(RelationGetDescr(pg_attribute_rel), values, nulls);
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 80cd091..47c48bf 100644
*** a/src/backend/catalog/information_schema.sql
--- b/src/backend/catalog/information_schema.sql
*************** GRANT SELECT ON element_types TO PUBLIC;
*** 2534,2539 ****
--- 2534,2572 ----
-- SQL/MED views; these use section numbers from part 9 of the standard.
+ /* Base view for foreign table columns */
+ CREATE VIEW _pg_foreign_table_columns AS
+ SELECT n.nspname,
+ c.relname,
+ a.attname,
+ a.attfdwoptions
+ FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
+ pg_attribute a
+ WHERE u.oid = c.relowner
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
+ AND n.oid = c.relnamespace
+ AND c.oid = t.ftrelid
+ AND c.relkind = 'f'
+ AND a.attrelid = c.oid
+ AND a.attnum > 0;
+
+ /*
+ * 24.2
+ * COLUMN_OPTIONS view
+ */
+ CREATE VIEW column_options AS
+ SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
+ c.nspname AS table_schema,
+ c.relname AS table_name,
+ c.attname AS column_name,
+ CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
+ CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
+ FROM _pg_foreign_table_columns c;
+
+ GRANT SELECT ON column_options TO PUBLIC;
+
+
/* Base view for foreign-data wrappers */
CREATE VIEW _pg_foreign_data_wrappers AS
SELECT w.oid,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 82bb756..7d8e613 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*************** static void ATPrepAlterColumnType(List *
*** 346,351 ****
--- 346,352 ----
static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno);
static void ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
AlterTableCmd *cmd, LOCKMODE lockmode);
+ static void ATExecAlterColumnGenericOptions(Relation rel, const char *colName, List *options, LOCKMODE lockmode);
static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode);
static void ATPostAlterTypeParse(Oid oldId, char *cmd,
List **wqueue, LOCKMODE lockmode, bool rewrite);
*************** AlterTableGetLockLevel(List *cmds)
*** 2648,2653 ****
--- 2649,2655 ----
case AT_DropNotNull: /* may change some SQL plans */
case AT_SetNotNull:
case AT_GenericOptions:
+ case AT_AlterColumnGenericOptions:
cmd_lockmode = AccessExclusiveLock;
break;
*************** ATPrepCmd(List **wqueue, Relation rel, A
*** 2925,2930 ****
--- 2927,2938 ----
ATPrepAlterColumnType(wqueue, tab, rel, recurse, recursing, cmd, lockmode);
pass = AT_PASS_ALTER_TYPE;
break;
+ case AT_AlterColumnGenericOptions:
+ ATSimplePermissions(rel, ATT_FOREIGN_TABLE);
+ /* This command never recurses */
+ /* No command-specific prep needed */
+ pass = AT_PASS_MISC;
+ break;
case AT_ChangeOwner: /* ALTER OWNER */
/* This command never recurses */
/* No command-specific prep needed */
*************** ATExecCmd(List **wqueue, AlteredTableInf
*** 3169,3174 ****
--- 3177,3185 ----
case AT_AlterColumnType: /* ALTER COLUMN TYPE */
ATExecAlterColumnType(tab, rel, cmd, lockmode);
break;
+ case AT_AlterColumnGenericOptions: /* ALTER COLUMN OPTIONS */
+ ATExecAlterColumnGenericOptions(rel, cmd->name, (List *) cmd->def, lockmode);
+ break;
case AT_ChangeOwner: /* ALTER OWNER */
ATExecChangeOwner(RelationGetRelid(rel),
get_role_oid(cmd->name, false),
*************** ATExecAlterColumnType(AlteredTableInfo *
*** 7397,7402 ****
--- 7408,7507 ----
heap_freetuple(heapTup);
}
+ static void
+ ATExecAlterColumnGenericOptions(Relation rel,
+ const char *colName,
+ List *options,
+ LOCKMODE lockmode)
+ {
+ Relation ftrel;
+ Relation attrel;
+ ForeignServer *server;
+ ForeignDataWrapper *fdw;
+ HeapTuple tuple;
+ HeapTuple newtuple;
+ bool isnull;
+ Datum repl_val[Natts_pg_attribute];
+ bool repl_null[Natts_pg_attribute];
+ bool repl_repl[Natts_pg_attribute];
+ Datum datum;
+ Form_pg_foreign_table fttableform;
+ Form_pg_attribute atttableform;
+
+ if (options == NIL)
+ return;
+
+ /* First, determine FDW validator associated to the foreign table. */
+ ftrel = heap_open(ForeignTableRelationId, AccessShareLock);
+ tuple = SearchSysCache1(FOREIGNTABLEREL, rel->rd_id);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("foreign table \"%s\" does not exist",
+ RelationGetRelationName(rel))));
+ fttableform = (Form_pg_foreign_table) GETSTRUCT(tuple);
+ server = GetForeignServer(fttableform->ftserver);
+ fdw = GetForeignDataWrapper(server->fdwid);
+
+ heap_close(ftrel, AccessShareLock);
+ ReleaseSysCache(tuple);
+
+ attrel = heap_open(AttributeRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+
+ /* Prevent them from altering a system attribute */
+ atttableform = (Form_pg_attribute) GETSTRUCT(tuple);
+ if (atttableform->attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"", colName)));
+
+
+ /* Initialize buffers for new tuple values */
+ memset(repl_val, 0, sizeof(repl_val));
+ memset(repl_null, false, sizeof(repl_null));
+ memset(repl_repl, false, sizeof(repl_repl));
+
+ /* Extract the current options */
+ datum = SysCacheGetAttr(ATTNAME,
+ tuple,
+ Anum_pg_attribute_attfdwoptions,
+ &isnull);
+ if (isnull)
+ datum = PointerGetDatum(NULL);
+
+ /* Transform the options */
+ datum = transformGenericOptions(AttributeRelationId,
+ datum,
+ options,
+ fdw->fdwvalidator);
+
+ if (PointerIsValid(DatumGetPointer(datum)))
+ repl_val[Anum_pg_attribute_attfdwoptions - 1] = datum;
+ else
+ repl_null[Anum_pg_attribute_attfdwoptions - 1] = true;
+
+ repl_repl[Anum_pg_attribute_attfdwoptions - 1] = true;
+
+ /* Everything looks good - update the tuple */
+
+ newtuple = heap_modify_tuple(tuple, RelationGetDescr(attrel),
+ repl_val, repl_null, repl_repl);
+ ReleaseSysCache(tuple);
+
+ simple_heap_update(attrel, &newtuple->t_self, newtuple);
+ CatalogUpdateIndexes(attrel, newtuple);
+
+ heap_close(attrel, RowExclusiveLock);
+
+ heap_freetuple(newtuple);
+ }
+
/*
* Cleanup after we've finished all the ALTER TYPE operations for a
* particular relation. We have to drop and recreate all the indexes
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 7a51456..d0704ed 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*************** _copyColumnDef(ColumnDef *from)
*** 2312,2317 ****
--- 2312,2318 ----
COPY_NODE_FIELD(collClause);
COPY_SCALAR_FIELD(collOid);
COPY_NODE_FIELD(constraints);
+ COPY_NODE_FIELD(fdwoptions);
return newnode;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index b5be09a..417aeb8 100644
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*************** _outColumnDef(StringInfo str, ColumnDef
*** 2102,2107 ****
--- 2102,2108 ----
WRITE_NODE_FIELD(collClause);
WRITE_OID_FIELD(collOid);
WRITE_NODE_FIELD(constraints);
+ WRITE_NODE_FIELD(fdwoptions);
}
static void
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ac094aa..e9f3896 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** alter_table_cmd:
*** 1769,1774 ****
--- 1769,1783 ----
def->raw_default = $8;
$$ = (Node *)n;
}
+ /* ALTER FOREIGN TABLE <name> ALTER [COLUMN] <colname> OPTIONS */
+ | ALTER opt_column ColId alter_generic_options
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_AlterColumnGenericOptions;
+ n->name = $3;
+ n->def = (Node *) $4;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> ADD CONSTRAINT ... */
| ADD_P TableConstraint
{
*************** TypedTableElement:
*** 2497,2503 ****
| TableConstraint { $$ = $1; }
;
! columnDef: ColId Typename ColQualList
{
ColumnDef *n = makeNode(ColumnDef);
n->colname = $1;
--- 2506,2512 ----
| TableConstraint { $$ = $1; }
;
! columnDef: ColId Typename create_generic_options ColQualList
{
ColumnDef *n = makeNode(ColumnDef);
n->colname = $1;
*************** columnDef: ColId Typename ColQualList
*** 2510,2516 ****
n->raw_default = NULL;
n->cooked_default = NULL;
n->collOid = InvalidOid;
! SplitColQualList($3, &n->constraints, &n->collClause,
yyscanner);
$$ = (Node *)n;
}
--- 2519,2526 ----
n->raw_default = NULL;
n->cooked_default = NULL;
n->collOid = InvalidOid;
! n->fdwoptions = $3;
! SplitColQualList($4, &n->constraints, &n->collClause,
yyscanner);
$$ = (Node *)n;
}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 1be2ac6..782f5bc 100644
*** a/src/backend/parser/parse_utilcmd.c
--- b/src/backend/parser/parse_utilcmd.c
*************** transformColumnDefinition(CreateStmtCont
*** 559,564 ****
--- 559,589 ----
break;
}
}
+
+ /*
+ * Generate ALTER FOREIGN TABLE ALTER COLUMN statement which adds
+ * per-column generic options for this column.
+ */
+ if (column->fdwoptions != NIL)
+ {
+ AlterTableStmt *stmt;
+ AlterTableCmd *cmd;
+
+ cmd = makeNode(AlterTableCmd);
+ cmd->subtype = AT_AlterColumnGenericOptions;
+ cmd->name = column->colname;
+ cmd->def = (Node *) column->fdwoptions;
+ cmd->behavior = DROP_RESTRICT;
+ cmd->missing_ok = false;
+
+ stmt = makeNode(AlterTableStmt);
+ stmt->relation = cxt->relation;
+ stmt->cmds = NIL;
+ stmt->relkind = OBJECT_FOREIGN_TABLE;
+ stmt->cmds = lappend(stmt->cmds, cmd);
+
+ cxt->alist = lappend(cxt->alist, stmt);
+ }
}
/*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f2ee57c..61b3492 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5574,5579 ****
--- 5574,5580 ----
int i_attislocal;
int i_attoptions;
int i_attcollation;
+ int i_attfdwoptions;
PGresult *res;
int ntups;
bool hasdefaults;
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5611,5617 ****
resetPQExpBuffer(q);
! if (g_fout->remoteVersion >= 90100)
{
/*
* attcollation is new in 9.1. Since we only want to dump COLLATE
--- 5612,5642 ----
resetPQExpBuffer(q);
! if (g_fout->remoteVersion >= 90200)
! {
! /*
! * attfdwoptions is new in 9.2.
! */
! appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, "
! "a.attstattarget, a.attstorage, t.typstorage, "
! "a.attnotnull, a.atthasdef, a.attisdropped, "
! "a.attlen, a.attalign, a.attislocal, "
! "pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
! "array_to_string(a.attoptions, ', ') AS attoptions, "
! "CASE WHEN a.attcollation <> t.typcollation "
! "THEN a.attcollation ELSE 0 END AS attcollation, "
! "array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
! " FROM pg_options_to_table(attfdwoptions)), ', ') "
! " AS attfdwoptions "
! "FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
! "ON a.atttypid = t.oid "
! "WHERE a.attrelid = '%u'::pg_catalog.oid "
! "AND a.attnum > 0::pg_catalog.int2 "
! "ORDER BY a.attrelid, a.attnum",
! tbinfo->dobj.catId.oid);
! }
! else if (g_fout->remoteVersion >= 90100)
{
/*
* attcollation is new in 9.1. Since we only want to dump COLLATE
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5626,5632 ****
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
"array_to_string(a.attoptions, ', ') AS attoptions, "
"CASE WHEN a.attcollation <> t.typcollation "
! "THEN a.attcollation ELSE 0 END AS attcollation "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
--- 5651,5658 ----
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
"array_to_string(a.attoptions, ', ') AS attoptions, "
"CASE WHEN a.attcollation <> t.typcollation "
! "THEN a.attcollation ELSE 0 END AS attcollation, "
! "NULL AS attfdwoptions "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5634,5639 ****
--- 5660,5666 ----
"ORDER BY a.attrelid, a.attnum",
tbinfo->dobj.catId.oid);
}
+
else if (g_fout->remoteVersion >= 90000)
{
/* attoptions is new in 9.0 */
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5643,5649 ****
"a.attlen, a.attalign, a.attislocal, "
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
"array_to_string(a.attoptions, ', ') AS attoptions, "
! "0 AS attcollation "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
--- 5670,5677 ----
"a.attlen, a.attalign, a.attislocal, "
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
"array_to_string(a.attoptions, ', ') AS attoptions, "
! "0 AS attcollation, "
! "NULL AS attfdwoptions "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5659,5665 ****
"a.attnotnull, a.atthasdef, a.attisdropped, "
"a.attlen, a.attalign, a.attislocal, "
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
! "'' AS attoptions, 0 AS attcollation "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
--- 5687,5694 ----
"a.attnotnull, a.atthasdef, a.attisdropped, "
"a.attlen, a.attalign, a.attislocal, "
"pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
! "'' AS attoptions, 0 AS attcollation, "
! "NULL AS attfdwoptions "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::pg_catalog.oid "
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5680,5686 ****
"false AS attisdropped, a.attlen, "
"a.attalign, false AS attislocal, "
"format_type(t.oid,a.atttypmod) AS atttypname, "
! "'' AS attoptions, 0 AS attcollation "
"FROM pg_attribute a LEFT JOIN pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::oid "
--- 5709,5716 ----
"false AS attisdropped, a.attlen, "
"a.attalign, false AS attislocal, "
"format_type(t.oid,a.atttypmod) AS atttypname, "
! "'' AS attoptions, 0 AS attcollation, "
! "NULL AS attfdwoptions "
"FROM pg_attribute a LEFT JOIN pg_type t "
"ON a.atttypid = t.oid "
"WHERE a.attrelid = '%u'::oid "
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5698,5704 ****
"attlen, attalign, "
"false AS attislocal, "
"(SELECT typname FROM pg_type WHERE oid = atttypid) AS atttypname, "
! "'' AS attoptions, 0 AS attcollation "
"FROM pg_attribute a "
"WHERE attrelid = '%u'::oid "
"AND attnum > 0::int2 "
--- 5728,5735 ----
"attlen, attalign, "
"false AS attislocal, "
"(SELECT typname FROM pg_type WHERE oid = atttypid) AS atttypname, "
! "'' AS attoptions, 0 AS attcollation, "
! "NULL AS attfdwoptions "
"FROM pg_attribute a "
"WHERE attrelid = '%u'::oid "
"AND attnum > 0::int2 "
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5726,5731 ****
--- 5757,5763 ----
i_attislocal = PQfnumber(res, "attislocal");
i_attoptions = PQfnumber(res, "attoptions");
i_attcollation = PQfnumber(res, "attcollation");
+ i_attfdwoptions = PQfnumber(res, "attfdwoptions");
tbinfo->numatts = ntups;
tbinfo->attnames = (char **) malloc(ntups * sizeof(char *));
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5742,5747 ****
--- 5774,5780 ----
tbinfo->attrdefs = (AttrDefInfo **) malloc(ntups * sizeof(AttrDefInfo *));
tbinfo->attoptions = (char **) malloc(ntups * sizeof(char *));
tbinfo->attcollation = (Oid *) malloc(ntups * sizeof(Oid));
+ tbinfo->attfdwoptions = (char **) malloc(ntups * sizeof(char *));
tbinfo->inhAttrs = (bool *) malloc(ntups * sizeof(bool));
tbinfo->inhAttrDef = (bool *) malloc(ntups * sizeof(bool));
tbinfo->inhNotNull = (bool *) malloc(ntups * sizeof(bool));
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5768,5773 ****
--- 5801,5807 ----
tbinfo->notnull[j] = (PQgetvalue(res, j, i_attnotnull)[0] == 't');
tbinfo->attoptions[j] = strdup(PQgetvalue(res, j, i_attoptions));
tbinfo->attcollation[j] = atooid(PQgetvalue(res, j, i_attcollation));
+ tbinfo->attfdwoptions[j] = strdup(PQgetvalue(res, j, i_attfdwoptions));
tbinfo->attrdefs[j] = NULL; /* fix below */
if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
hasdefaults = true;
*************** dumpTableSchema(Archive *fout, TableInfo
*** 12469,12474 ****
--- 12503,12523 ----
appendPQExpBuffer(q, "SET (%s);\n",
tbinfo->attoptions[j]);
}
+
+ /*
+ * Dump per-column generic options.
+ */
+ if (tbinfo->relkind == RELKIND_FOREIGN_TABLE &&
+ tbinfo->attfdwoptions[j] &&
+ tbinfo->attfdwoptions[j][0] != '\0')
+ {
+ appendPQExpBuffer(q, "ALTER FOREIGN TABLE %s ",
+ fmtId(tbinfo->dobj.name));
+ appendPQExpBuffer(q, "ALTER COLUMN %s ",
+ fmtId(tbinfo->attnames[j]));
+ appendPQExpBuffer(q, "OPTIONS (%s);\n",
+ tbinfo->attfdwoptions[j]);
+ }
}
}
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index c95614b..0a65401 100644
*** a/src/bin/pg_dump/pg_dump.h
--- b/src/bin/pg_dump/pg_dump.h
*************** typedef struct _tableInfo
*** 275,280 ****
--- 275,281 ----
bool *attislocal; /* true if attr has local definition */
char **attoptions; /* per-attribute options */
Oid *attcollation; /* per-attribute collation selection */
+ char **attfdwoptions; /* per-attribute generic options */
/*
* Note: we need to store per-attribute notnull, default, and constraint
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b50c5d6..5d7dfcf 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** describeOneTableDetails(const char *sche
*** 1281,1287 ****
res = NULL;
}
! /* Get column info */
printfPQExpBuffer(&buf, "SELECT a.attname,");
appendPQExpBuffer(&buf, "\n pg_catalog.format_type(a.atttypid, a.atttypmod),"
"\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
--- 1281,1292 ----
res = NULL;
}
! /*
! * Get column info
! *
! * You need to modify value of "firstvcol" which willbe defined below if
! * you are adding column(s) preceding to verbose-only columns.
! */
printfPQExpBuffer(&buf, "SELECT a.attname,");
appendPQExpBuffer(&buf, "\n pg_catalog.format_type(a.atttypid, a.atttypmod),"
"\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
*************** describeOneTableDetails(const char *sche
*** 1295,1300 ****
--- 1300,1311 ----
appendPQExpBuffer(&buf, "\n NULL AS attcollation");
if (tableinfo.relkind == 'i')
appendPQExpBuffer(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
+ else
+ appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
+ if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
+ appendPQExpBuffer(&buf, ",\n a.attfdwoptions");
+ else
+ appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
if (verbose)
{
appendPQExpBuffer(&buf, ",\n a.attstorage");
*************** describeOneTableDetails(const char *sche
*** 1386,1391 ****
--- 1397,1405 ----
if (tableinfo.relkind == 'i')
headers[cols++] = gettext_noop("Definition");
+ if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
+ headers[cols++] = gettext_noop("Options");
+
if (verbose)
{
headers[cols++] = gettext_noop("Storage");
*************** describeOneTableDetails(const char *sche
*** 1471,1480 ****
if (tableinfo.relkind == 'i')
printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false);
/* Storage and Description */
if (verbose)
{
! int firstvcol = (tableinfo.relkind == 'i' ? 7 : 6);
char *storage = PQgetvalue(res, i, firstvcol);
/* these strings are literal in our syntax, so not translated. */
--- 1485,1498 ----
if (tableinfo.relkind == 'i')
printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false);
+ /* FDW options for foreign table column, only for 9.2 or later */
+ if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
+ printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
+
/* Storage and Description */
if (verbose)
{
! int firstvcol = 8;
char *storage = PQgetvalue(res, i, firstvcol);
/* these strings are literal in our syntax, so not translated. */
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index 409d6ea..3ea87e8 100644
*** a/src/include/catalog/pg_attribute.h
--- b/src/include/catalog/pg_attribute.h
*************** CATALOG(pg_attribute,1249) BKI_BOOTSTRAP
*** 156,161 ****
--- 156,164 ----
/* Column-level options */
text attoptions[1];
+
+ /* Column-level FDW options */
+ text attfdwoptions[1];
} FormData_pg_attribute;
/*
*************** typedef FormData_pg_attribute *Form_pg_a
*** 179,185 ****
* ----------------
*/
! #define Natts_pg_attribute 20
#define Anum_pg_attribute_attrelid 1
#define Anum_pg_attribute_attname 2
#define Anum_pg_attribute_atttypid 3
--- 182,188 ----
* ----------------
*/
! #define Natts_pg_attribute 21
#define Anum_pg_attribute_attrelid 1
#define Anum_pg_attribute_attname 2
#define Anum_pg_attribute_atttypid 3
*************** typedef FormData_pg_attribute *Form_pg_a
*** 200,205 ****
--- 203,209 ----
#define Anum_pg_attribute_attcollation 18
#define Anum_pg_attribute_attacl 19
#define Anum_pg_attribute_attoptions 20
+ #define Anum_pg_attribute_attfdwoptions 21
/* ----------------
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 002ae6b..e006180 100644
*** a/src/include/catalog/pg_class.h
--- b/src/include/catalog/pg_class.h
*************** typedef FormData_pg_class *Form_pg_class
*** 132,138 ****
/* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId */
DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 29 0 t f f f f 3 _null_ _null_ ));
DESCR("");
! DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 20 0 f f f f f 3 _null_ _null_ ));
DESCR("");
DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 26 0 t f f f f 3 _null_ _null_ ));
DESCR("");
--- 132,138 ----
/* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId */
DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 29 0 t f f f f 3 _null_ _null_ ));
DESCR("");
! DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 21 0 f f f f f 3 _null_ _null_ ));
DESCR("");
DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 26 0 t f f f f 3 _null_ _null_ ));
DESCR("");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 92e40d3..a4fb3b5 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct ColumnDef
*** 500,505 ****
--- 500,506 ----
CollateClause *collClause; /* untransformed COLLATE spec, if any */
Oid collOid; /* collation OID (InvalidOid if not set) */
List *constraints; /* other constraints on column */
+ List *fdwoptions; /* per-column FDW options */
} ColumnDef;
/*
*************** typedef enum AlterTableType
*** 1197,1202 ****
--- 1198,1204 ----
AT_DropConstraint, /* drop constraint */
AT_DropConstraintRecurse, /* internal to commands/tablecmds.c */
AT_AlterColumnType, /* alter column type */
+ AT_AlterColumnGenericOptions, /* alter column OPTIONS (...) */
AT_ChangeOwner, /* change owner */
AT_ClusterOn, /* CLUSTER ON */
AT_DropCluster, /* SET WITHOUT CLUSTER */
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 2b3eddf..45292b5 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** ERROR: syntax error at or near "WITH OI
*** 646,664 ****
LINE 1: CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
! c1 integer NOT NULL,
! c2 text,
c3 date
) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | Storage | Description
! --------+---------+-----------+----------+-------------
! c1 | integer | not null | plain | ft1.c1
! c2 | text | | extended |
! c3 | date | | plain |
Server: sc
Has OIDs: no
--- 646,664 ----
LINE 1: CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS (param1 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | Options | Storage | Description
! --------+---------+-----------+---------------------------+----------+-------------
! c1 | integer | not null | {param1=val1} | plain | ft1.c1
! c2 | text | | {param2=val2,param3=val3} | extended |
! c3 | date | | | plain |
Server: sc
Has OIDs: no
*************** ALTER FOREIGN TABLE ft1 ADD COLUMN c6 in
*** 687,693 ****
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
! ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer;
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; -- ERROR
ERROR: "ft1" is not a table or view
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; -- ERROR
--- 687,693 ----
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
! ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; -- ERROR
ERROR: "ft1" is not a table or view
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; -- ERROR
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8
*** 698,703 ****
--- 698,724 ----
ERROR: "ft1" is not a table
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text;
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN xmin OPTIONS (ADD p1 'v1'); -- ERROR
+ ERROR: cannot alter system column "xmin"
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'),
+ ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
+ \d+ ft1
+ Foreign table "public.ft1"
+ Column | Type | Modifiers | Options | Storage | Description
+ --------+---------+-----------+---------------------------+----------+-------------
+ c1 | integer | not null | {param1=val1} | plain |
+ c2 | text | | {param2=val2,param3=val3} | extended |
+ c3 | date | | | plain |
+ c4 | integer | | | plain |
+ c6 | integer | not null | | plain |
+ c7 | integer | | {p1=v1,p2=v2} | plain |
+ c8 | text | | {p2=V2} | extended |
+ c9 | integer | | | plain |
+ c10 | integer | | {p1=v1} | plain |
+ Server: sc
+ Has OIDs: no
+
-- can't change the column type if it's used elsewhere
CREATE TABLE use_ft1_column_type (x ft1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
*************** ERROR: relation "ft1" does not exist
*** 726,742 ****
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers
! ------------------+---------+-----------
! foreign_column_1 | integer | not null
! c2 | text |
! c3 | date |
! c4 | integer |
! c6 | integer | not null
! c7 | integer |
! c8 | text |
! c10 | integer |
Server: sc
-- Information schema
--- 747,763 ----
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers | Options
! ------------------+---------+-----------+---------------------------
! foreign_column_1 | integer | not null | {param1=val1}
! c2 | text | | {param2=val2,param3=val3}
! c3 | date | |
! c4 | integer | |
! c6 | integer | not null |
! c7 | integer | | {p1=v1,p2=v2}
! c8 | text | | {p2=V2}
! c10 | integer | | {p1=v1}
Server: sc
-- Information schema
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index 58e5060..b3b49cc 100644
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
*************** CREATE FOREIGN TABLE ft1 () SERVER no_se
*** 264,271 ****
CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR
CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS; -- ERROR
CREATE FOREIGN TABLE ft1 (
! c1 integer NOT NULL,
! c2 text,
c3 date
) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
--- 264,271 ----
CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR
CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS; -- ERROR
CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS (param1 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
*************** ALTER FOREIGN TABLE ft1 ADD COLUMN c6 in
*** 288,294 ****
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
! ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer;
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; -- ERROR
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; -- ERROR
--- 288,294 ----
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
! ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; -- ERROR
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; -- ERROR
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c7
*** 297,302 ****
--- 297,307 ----
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text;
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN xmin OPTIONS (ADD p1 'v1'); -- ERROR
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'),
+ ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
+ \d+ ft1
-- can't change the column type if it's used elsewhere
CREATE TABLE use_ft1_column_type (x ft1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
force_not_null_v2.patchtext/plain; name=force_not_null_v2.patchDownload
diff --git a/contrib/file_fdw/data/text.csv b/contrib/file_fdw/data/text.csv
index ...bd4c327 .
*** a/contrib/file_fdw/data/text.csv
--- b/contrib/file_fdw/data/text.csv
***************
*** 0 ****
--- 1,4 ----
+ 123,123
+ abc,abc
+ NULL,NULL
+ ABC,ABC
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 224e74f..e846176 100644
*** a/contrib/file_fdw/file_fdw.c
--- b/contrib/file_fdw/file_fdw.c
***************
*** 23,30 ****
--- 23,32 ----
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
#include "miscadmin.h"
+ #include "nodes/makefuncs.h"
#include "optimizer/cost.h"
#include "utils/rel.h"
+ #include "utils/syscache.h"
PG_MODULE_MAGIC;
*************** static struct FileFdwOption valid_option
*** 57,72 ****
{"escape", ForeignTableRelationId},
{"null", ForeignTableRelationId},
{"encoding", ForeignTableRelationId},
/*
* force_quote is not supported by file_fdw because it's for COPY TO.
*/
- /*
- * force_not_null is not supported by file_fdw. It would need a parser
- * for list of columns, not to mention a way to check the column list
- * against the table.
- */
/* Sentinel */
{NULL, InvalidOid}
--- 59,70 ----
{"escape", ForeignTableRelationId},
{"null", ForeignTableRelationId},
{"encoding", ForeignTableRelationId},
+ {"force_not_null", AttributeRelationId}, /* specified as boolean value */
/*
* force_quote is not supported by file_fdw because it's for COPY TO.
*/
/* Sentinel */
{NULL, InvalidOid}
*************** static void fileGetOptions(Oid foreignta
*** 112,117 ****
--- 110,116 ----
static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
const char *filename,
Cost *startup_cost, Cost *total_cost);
+ static List * get_force_not_null(Oid relid);
/*
*************** file_fdw_validator(PG_FUNCTION_ARGS)
*** 145,150 ****
--- 144,150 ----
List *options_list = untransformRelOptions(PG_GETARG_DATUM(0));
Oid catalog = PG_GETARG_OID(1);
char *filename = NULL;
+ char *force_not_null = NULL;
List *other_options = NIL;
ListCell *cell;
*************** file_fdw_validator(PG_FUNCTION_ARGS)
*** 198,204 ****
buf.data)));
}
! /* Separate out filename, since ProcessCopyOptions won't allow it */
if (strcmp(def->defname, "filename") == 0)
{
if (filename)
--- 198,207 ----
buf.data)));
}
! /*
! * Separate out filename and force_not_null, since ProcessCopyOptions
! * won't allow them.
! */
if (strcmp(def->defname, "filename") == 0)
{
if (filename)
*************** file_fdw_validator(PG_FUNCTION_ARGS)
*** 207,212 ****
--- 210,229 ----
errmsg("conflicting or redundant options")));
filename = defGetString(def);
}
+ else if (strcmp(def->defname, "force_not_null") == 0)
+ {
+ if (force_not_null)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+
+ force_not_null = defGetString(def);
+ if (strcmp(force_not_null, "true") != 0 &&
+ strcmp(force_not_null, "false") != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("force_not_null must be true or false")));
+ }
else
other_options = lappend(other_options, def);
}
*************** is_valid_option(const char *option, Oid
*** 245,254 ****
--- 262,356 ----
}
/*
+ * Retrieve per-column generic options from pg_attribute and construct a list
+ * of column names for "force_not_null".
+ */
+ static List *
+ get_force_not_null(Oid relid)
+ {
+ Relation rel;
+ TupleDesc tupleDesc;
+ AttrNumber natts;
+ AttrNumber attnum;
+ List *columns = NIL;
+
+ rel = heap_open(relid, AccessShareLock);
+ tupleDesc = RelationGetDescr(rel);
+ natts = tupleDesc->natts;
+
+ /* Retrieve FDW options from every user-defined attributes. */
+ for (attnum = 1; attnum < natts; attnum++)
+ {
+ HeapTuple tuple;
+ Form_pg_attribute attr;
+ Datum datum;
+ bool isnull;
+ List *options;
+ ListCell *cell;
+
+
+ tuple = SearchSysCache2(ATTNUM,
+ RelationGetRelid(rel),
+ Int16GetDatum(attnum));
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("cache lookup failed for attribute %d of relation %u",
+ attnum, RelationGetRelid(rel))));
+ attr = (Form_pg_attribute) GETSTRUCT(tuple);
+
+ /* Skip dropped attributes. */
+ if (attr->attisdropped)
+ {
+ ReleaseSysCache(tuple);
+ continue;
+ }
+
+ datum = SysCacheGetAttr(ATTNUM,
+ tuple,
+ Anum_pg_attribute_attfdwoptions,
+ &isnull);
+ if (isnull)
+ datum = PointerGetDatum(NULL);
+ options = untransformRelOptions(datum);
+
+ /*
+ * Find force_not_null option and append attname to the list if
+ * the value was true.
+ */
+ foreach (cell, options)
+ {
+ DefElem *def = (DefElem *) lfirst(cell);
+ const char *value = defGetString(def);
+
+ if (strcmp(def->defname, "force_not_null") == 0 &&
+ strcmp(value, "true") == 0)
+ {
+ columns = lappend(columns, makeString(NameStr(attr->attname)));
+ elog(DEBUG1, "%s: force_not_null", NameStr(attr->attname));
+ }
+
+ }
+
+ ReleaseSysCache(tuple);
+ }
+
+ heap_close(rel, AccessShareLock);
+
+ /* Return DefElem only when any column is set "force_not_null=true". */
+ if (columns != NIL)
+ return list_make1(makeDefElem("force_not_null", (Node *) columns));
+ else
+ return NIL;
+ }
+
+ /*
* Fetch the options for a file_fdw foreign table.
*
* We have to separate out "filename" from the other options because
* it must not appear in the options list passed to the core COPY code.
+ * And we must construct List of DefElem from pg_attribute.attfdwoptions for
+ * "force_not_null".
*/
static void
fileGetOptions(Oid foreigntableid,
*************** fileGetOptions(Oid foreigntableid,
*** 296,301 ****
--- 398,406 ----
prev = lc;
}
+ /* Retrieve force_not_null from pg_attribute and append it to the list. */
+ options = list_concat(options, get_force_not_null(foreigntableid));
+
/*
* The validator should have checked that a filename was included in the
* options, but check again, just in case.
diff --git a/contrib/file_fdw/input/file_fdw.source b/contrib/file_fdw/input/file_fdw.source
index 1405752..5d7347f 100644
*** a/contrib/file_fdw/input/file_fdw.source
--- b/contrib/file_fdw/input/file_fdw.source
*************** CREATE FOREIGN TABLE agg_bad (
*** 78,83 ****
--- 78,97 ----
) SERVER file_server
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
+ -- per-column options tests
+ ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
+ ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
+ CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
+ CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
+ CREATE FOREIGN TABLE text_csv (
+ word1 text OPTIONS (force_not_null 'true'),
+ word2 text OPTIONS (force_not_null 'false')
+ ) SERVER file_server
+ OPTIONS (format 'text', filename '@abs_srcdir@/data/text.csv', null 'NULL');
+ SELECT * FROM text_csv ORDER BY word1; -- ERROR
+ ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
+ SELECT * FROM text_csv ORDER BY word1;
+
-- basic query tests
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
SELECT * FROM agg_csv ORDER BY a;
diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source
index 6dd2653..cf2cba5 100644
*** a/contrib/file_fdw/output/file_fdw.source
--- b/contrib/file_fdw/output/file_fdw.source
*************** CREATE FOREIGN TABLE agg_bad (
*** 93,98 ****
--- 93,128 ----
b float4
) SERVER file_server
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
+ -- per-column options tests
+ ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
+ ERROR: invalid option "force_not_null"
+ HINT: Valid options in this context are:
+ ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
+ ERROR: invalid option "force_not_null"
+ HINT: Valid options in this context are:
+ CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
+ ERROR: invalid option "force_not_null"
+ HINT: Valid options in this context are:
+ CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
+ ERROR: invalid option "force_not_null"
+ HINT: Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding
+ CREATE FOREIGN TABLE text_csv (
+ word1 text OPTIONS (force_not_null 'true'),
+ word2 text OPTIONS (force_not_null 'false')
+ ) SERVER file_server
+ OPTIONS (format 'text', filename '@abs_srcdir@/data/text.csv', null 'NULL');
+ SELECT * FROM text_csv ORDER BY word1; -- ERROR
+ ERROR: COPY force not null available only in CSV mode
+ ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
+ SELECT * FROM text_csv ORDER BY word1;
+ word1 | word2
+ -------+-------
+ 123 | 123
+ ABC | ABC
+ NULL |
+ abc | abc
+ (4 rows)
+
-- basic query tests
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
a | b
*************** SET ROLE file_fdw_superuser;
*** 216,222 ****
-- cleanup
RESET ROLE;
DROP EXTENSION file_fdw CASCADE;
! NOTICE: drop cascades to 7 other objects
DETAIL: drop cascades to server file_server
drop cascades to user mapping for file_fdw_user
drop cascades to user mapping for file_fdw_superuser
--- 246,252 ----
-- cleanup
RESET ROLE;
DROP EXTENSION file_fdw CASCADE;
! NOTICE: drop cascades to 8 other objects
DETAIL: drop cascades to server file_server
drop cascades to user mapping for file_fdw_user
drop cascades to user mapping for file_fdw_superuser
*************** drop cascades to user mapping for no_pri
*** 224,227 ****
--- 254,258 ----
drop cascades to foreign table agg_text
drop cascades to foreign table agg_csv
drop cascades to foreign table agg_bad
+ drop cascades to foreign table text_csv
DROP ROLE file_fdw_superuser, file_fdw_user, no_priv_user;
diff --git a/doc/src/sgml/file-fdw.sgml b/doc/src/sgml/file-fdw.sgml
index 8497d9a..5b35451 100644
*** a/doc/src/sgml/file-fdw.sgml
--- b/doc/src/sgml/file-fdw.sgml
***************
*** 111,124 ****
</variablelist>
<para>
! <command>COPY</>'s <literal>OIDS</literal>, <literal>FORCE_QUOTE</literal>,
! and <literal>FORCE_NOT_NULL</literal> options are currently not supported by
<literal>file_fdw</>.
</para>
<para>
! These options can only be specified for a foreign table, not in the
! options of the <literal>file_fdw</> foreign-data wrapper, nor in the
options of a server or user mapping using the wrapper.
</para>
--- 111,147 ----
</variablelist>
<para>
! A column of a foreign table created using this wrapper can have the
! following options:
! </para>
!
! <variablelist>
!
! <varlistentry>
! <term><literal>force_not_null</literal></term>
!
! <listitem>
! <para>
! Specifies whether values for the column shouldn't been matched against
! the null string. Acceptable values are <literal>true</> for no matching,
! and <literal>false</> for matching (case sensitive).
! <literal>true</> is same as specifying the column in <command>COPY</>'s
! <literal>FORCE_NOT_NULL</literal> option.
! </para>
! </listitem>
! </varlistentry>
!
! </variablelist>
!
! <para>
! <command>COPY</>'s <literal>OIDS</literal> and
! <literal>FORCE_QUOTE</literal> options are currently not supported by
<literal>file_fdw</>.
</para>
<para>
! These options can only be specified for a foreign table or its columns, not
! in the options of the <literal>file_fdw</> foreign-data wrapper, nor in the
options of a server or user mapping using the wrapper.
</para>
Re: per-column FDW options, v5
2011/7/29 Shigeru Hanada <shigeru.hanada@gmail.com>:
Here is a rebased version of per-column FDW options patch. I've
proposed this patch in last CF, but it was marked as returned with
feedback. So I would like to propose in next CF 2011-09. I already
moved CF item into new topic "SQL/MED" of CF 2011-09.
I did a quick review of this patch and it looks good to me, modulo
some quibbles with the wording of the documentation and comments and a
couple of minor stylistic nitpicks. Barring objections, I'll fix this
up a bit and commit it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: per-column FDW options, v5
On Fri, Jul 29, 2011 at 10:09 AM, Robert Haas <robertmhaas@gmail.com> wrote:
2011/7/29 Shigeru Hanada <shigeru.hanada@gmail.com>:
Here is a rebased version of per-column FDW options patch. I've
proposed this patch in last CF, but it was marked as returned with
feedback. So I would like to propose in next CF 2011-09. I already
moved CF item into new topic "SQL/MED" of CF 2011-09.I did a quick review of this patch and it looks good to me, modulo
some quibbles with the wording of the documentation and comments and a
couple of minor stylistic nitpicks. Barring objections, I'll fix this
up a bit and commit it.
Done.
Incidentally, I notice that if you do:
\d some_foreign_table
...the table-level options are not displayed. We probably ought to do
something about that...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: per-column FDW options, v5
Sorry, I've missed sending copy to list, so I quoted off-list discussion.
On Aug 5, 2011, at 7:59 PM, Shigeru Hanada<shigeru.hanada@gmail.com>
wrote:
2011/8/6 Robert Haas<robertmhaas@gmail.com>:
Done.
Thanks!
Incidentally, I notice that if you do:
\d some_foreign_table
...the table-level options are not displayed. We probably ought to do
something about that...Currently table-level options are showin in result of \det+ command
(only verbose mode), in same style as fdw and foreign servers.But \d is more popular for table describing, so moving table-level
options from \det+ to \d might be better. Thoughts?
(2011/08/06 9:26), Robert Haas wrote:
I'd show it both places.
After taking a look at describe.c, I think some styles are applicable to
FDW options in result of \d command.
(1) simple array literal style
Show table-level FDW options like other FDW options. It is simply a
result of array_out(); each options is shown as "key=value" with quoting
if necessary and delimited by ','. Whole line is surrounded by { and }.
If an element includes any character which need to be escaped, such
element is quoted with double-quotation.
Ex)
FDW Options: {"delimiter=,","quote=\""}
#delimiter is a comma, and qutoe is a double-quote
(2) reloptions style
Show FDW options like reloptions of \d+ result. Each options is shown
as "key=value" without quoting. Some special characters might make it
little illegible.
Ex)
FDW Options: delimiter=,, quote="
#delimiter is a comma, and qutoe is a double-quote
(3) OPTIONS clause style
Show FDW options as they were in OPTIONS clause. Each option is shown
as "key 'value'", and delimited with ','.
Ex)
FDW Options: delimiter ',', quote ''''
#delimiter is a comma, and qutoe is a single-quote
(1) can be implemented with minimum change, and it also keeps the
behavior consistent with other existing FDW objects. But IMHO (3) is
most readable, though it breaks backward compatibility about the format
of FDW options used in the result of \d* command. Thoughts?
BTW, I found wrong description about per-table FDW options; psql
document says that \d+ shows table-level FDW options, but actually it
doesn't show. I'll post this issue in another new thread.
Regards,
--
Shigeru Hanada
Re: per-column FDW options, v5
(2011/07/29 17:37), Shigeru Hanada wrote:
I also attached a rebased version of force_not_null patch, which adds
force_not_null option support to file_fdw. This is a use case of
per-column FDW option.
[just for redirection]
Robert has committed only per_column_option patch. So I posted
force_not_null patch in a new thread, and added it to CF 2011-09 as an
independent new item.
https://commitfest.postgresql.org/action/patch_view?id=615
Regards,
--
Shigeru Hanada
Re: per-column FDW options, v5
2011/8/8 Shigeru Hanada <shigeru.hanada@gmail.com>:
Currently table-level options are showin in result of \det+ command
(only verbose mode), in same style as fdw and foreign servers.But \d is more popular for table describing, so moving table-level
options from \det+ to \d might be better. Thoughts?(2011/08/06 9:26), Robert Haas wrote:
I'd show it both places.
After taking a look at describe.c, I think some styles are applicable to
FDW options in result of \d command.(1) simple array literal style
Show table-level FDW options like other FDW options. It is simply a
result of array_out(); each options is shown as "key=value" with quoting
if necessary and delimited by ','. Whole line is surrounded by { and }.
If an element includes any character which need to be escaped, such
element is quoted with double-quotation.Ex)
FDW Options: {"delimiter=,","quote=\""}
#delimiter is a comma, and qutoe is a double-quote(2) reloptions style
Show FDW options like reloptions of \d+ result. Each options is shown
as "key=value" without quoting. Some special characters might make it
little illegible.Ex)
FDW Options: delimiter=,, quote="
#delimiter is a comma, and qutoe is a double-quote(3) OPTIONS clause style
Show FDW options as they were in OPTIONS clause. Each option is shown
as "key 'value'", and delimited with ','.Ex)
FDW Options: delimiter ',', quote ''''
#delimiter is a comma, and qutoe is a single-quote(1) can be implemented with minimum change, and it also keeps the
behavior consistent with other existing FDW objects. But IMHO (3) is
most readable, though it breaks backward compatibility about the format
of FDW options used in the result of \d* command. Thoughts?
I'm against #2, but I could go either way on #1 vs. #3. If you pick
#3, would you also change the column options to be displayed that way,
or would we end up with table and column options displayed
differently?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: per-column FDW options, v5
(2011/08/09 1:16), Robert Haas wrote:
2011/8/8 Shigeru Hanada<shigeru.hanada@gmail.com>:
Currently table-level options are showin in result of \det+ command
(only verbose mode), in same style as fdw and foreign servers.But \d is more popular for table describing, so moving table-level
options from \det+ to \d might be better. Thoughts?(2011/08/06 9:26), Robert Haas wrote:
I'd show it both places.
After taking a look at describe.c, I think some styles are applicable to
FDW options in result of \d command.(1) simple array literal style
Show table-level FDW options like other FDW options. It is simply a
result of array_out(); each options is shown as "key=value" with quoting
if necessary and delimited by ','. Whole line is surrounded by { and }.
If an element includes any character which need to be escaped, such
element is quoted with double-quotation.Ex)
FDW Options: {"delimiter=,","quote=\""}
#delimiter is a comma, and qutoe is a double-quote(2) reloptions style
Show FDW options like reloptions of \d+ result. Each options is shown
as "key=value" without quoting. Some special characters might make it
little illegible.Ex)
FDW Options: delimiter=,, quote="
#delimiter is a comma, and qutoe is a double-quote(3) OPTIONS clause style
Show FDW options as they were in OPTIONS clause. Each option is shown
as "key 'value'", and delimited with ','.Ex)
FDW Options: delimiter ',', quote ''''
#delimiter is a comma, and qutoe is a single-quote(1) can be implemented with minimum change, and it also keeps the
behavior consistent with other existing FDW objects. But IMHO (3) is
most readable, though it breaks backward compatibility about the format
of FDW options used in the result of \d* command. Thoughts?I'm against #2, but I could go either way on #1 vs. #3. If you pick
#3, would you also change the column options to be displayed that way,
or would we end up with table and column options displayed
differently?
I'd like to pick #3, and also change per-column options format. In
addition, I'd like to change options format for other FDW objects such
as wrappers, servers and user mappings for consistency. Of course, only
if it's acceptable to break backward compatibility...
Regards,
--
Shigeru Hanada
Re: per-column FDW options, v5
2011/8/9 Shigeru Hanada <shigeru.hanada@gmail.com>:
(2011/08/09 1:16), Robert Haas wrote:
2011/8/8 Shigeru Hanada<shigeru.hanada@gmail.com>:
Currently table-level options are showin in result of \det+ command
(only verbose mode), in same style as fdw and foreign servers.But \d is more popular for table describing, so moving table-level
options from \det+ to \d might be better. Thoughts?(2011/08/06 9:26), Robert Haas wrote:
I'd show it both places.
After taking a look at describe.c, I think some styles are applicable to
FDW options in result of \d command.(1) simple array literal style
Show table-level FDW options like other FDW options. It is simply a
result of array_out(); each options is shown as "key=value" with quoting
if necessary and delimited by ','. Whole line is surrounded by { and }.
If an element includes any character which need to be escaped, such
element is quoted with double-quotation.Ex)
FDW Options: {"delimiter=,","quote=\""}
#delimiter is a comma, and qutoe is a double-quote(2) reloptions style
Show FDW options like reloptions of \d+ result. Each options is shown
as "key=value" without quoting. Some special characters might make it
little illegible.Ex)
FDW Options: delimiter=,, quote="
#delimiter is a comma, and qutoe is a double-quote(3) OPTIONS clause style
Show FDW options as they were in OPTIONS clause. Each option is shown
as "key 'value'", and delimited with ','.Ex)
FDW Options: delimiter ',', quote ''''
#delimiter is a comma, and qutoe is a single-quote(1) can be implemented with minimum change, and it also keeps the
behavior consistent with other existing FDW objects. But IMHO (3) is
most readable, though it breaks backward compatibility about the format
of FDW options used in the result of \d* command. Thoughts?I'm against #2, but I could go either way on #1 vs. #3. If you pick
#3, would you also change the column options to be displayed that way,
or would we end up with table and column options displayed
differently?I'd like to pick #3, and also change per-column options format. In
addition, I'd like to change options format for other FDW objects such
as wrappers, servers and user mappings for consistency. Of course, only
if it's acceptable to break backward compatibility...
I think it's fine to change the display format. We haven't had these
features for very long, so users hopefully shouldn't be expecting that
everything is set in stone. We have made far bigger changes to
backslash commands that have been around for far longer (\df, I'm
looking at you).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: per-column FDW options, v5
Excerpts from Robert Haas's message of jue ago 11 11:50:40 -0400 2011:
2011/8/9 Shigeru Hanada <shigeru.hanada@gmail.com>:
(3) OPTIONS clause style
Show FDW options as they were in OPTIONS clause. Each option is shown
as "key 'value'", and delimited with ','.Ex)
FDW Options: delimiter ',', quote ''''
#delimiter is a comma, and qutoe is a single-quote
I'm against #2, but I could go either way on #1 vs. #3. If you pick
#3, would you also change the column options to be displayed that way,
or would we end up with table and column options displayed
differently?I'd like to pick #3, and also change per-column options format. In
addition, I'd like to change options format for other FDW objects such
as wrappers, servers and user mappings for consistency. Of course, only
if it's acceptable to break backward compatibility...I think it's fine to change the display format. We haven't had these
features for very long, so users hopefully shouldn't be expecting that
everything is set in stone. We have made far bigger changes to
backslash commands that have been around for far longer (\df, I'm
looking at you).
We've never promised that backslash commands behave identically across
releases. I think they are more for human consumption than machine, so
why would we care about changing one of them a bit?
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: per-column FDW options, v5
On Thu, Aug 11, 2011 at 12:04 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Excerpts from Robert Haas's message of jue ago 11 11:50:40 -0400 2011:
2011/8/9 Shigeru Hanada <shigeru.hanada@gmail.com>:
(3) OPTIONS clause style
Show FDW options as they were in OPTIONS clause. Each option is shown
as "key 'value'", and delimited with ','.Ex)
FDW Options: delimiter ',', quote ''''
#delimiter is a comma, and qutoe is a single-quoteI'm against #2, but I could go either way on #1 vs. #3. If you pick
#3, would you also change the column options to be displayed that way,
or would we end up with table and column options displayed
differently?I'd like to pick #3, and also change per-column options format. In
addition, I'd like to change options format for other FDW objects such
as wrappers, servers and user mappings for consistency. Of course, only
if it's acceptable to break backward compatibility...I think it's fine to change the display format. We haven't had these
features for very long, so users hopefully shouldn't be expecting that
everything is set in stone. We have made far bigger changes to
backslash commands that have been around for far longer (\df, I'm
looking at you).We've never promised that backslash commands behave identically across
releases. I think they are more for human consumption than machine, so
why would we care about changing one of them a bit?
Yeah, I agree.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Change format of FDW options used in \d* commands (was: Re: per-column FDW options, v5)
(2011/08/12 1:05), Robert Haas wrote:
On Thu, Aug 11, 2011 at 12:04 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:Excerpts from Robert Haas's message of jue ago 11 11:50:40 -0400 2011:
2011/8/9 Shigeru Hanada<shigeru.hanada@gmail.com>:
I'd like to pick #3, and also change per-column options format. In
addition, I'd like to change options format for other FDW objects such
as wrappers, servers and user mappings for consistency. Of course, only
if it's acceptable to break backward compatibility...I think it's fine to change the display format. We haven't had these
features for very long, so users hopefully shouldn't be expecting that
everything is set in stone. We have made far bigger changes to
backslash commands that have been around for far longer (\df, I'm
looking at you).We've never promised that backslash commands behave identically across
releases. I think they are more for human consumption than machine, so
why would we care about changing one of them a bit?Yeah, I agree.
Thanks for the comments.
Attached patch changes various \d*+ commands to show FDW options in same
format as OPTIONS clause. IMHO the new format is easier to read.
Example)
old: {"delimiter=,","quote=\""}
new: delimiter ',', quote '"'
All regression tests including contrib's installcheck passed.
I'll add this patch to CF app as new item.
Regards,
--
Shigeru Hanada
Attachments:
20110812_fdw_option_format.patchtext/plain; name=20110812_fdw_option_format.patchDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 39c7136..baafd95 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** describeOneTableDetails(const char *sche
*** 1272,1278 ****
else
appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! appendPQExpBuffer(&buf, ",\n a.attfdwoptions");
else
appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
if (verbose)
--- 1272,1279 ----
else
appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! appendPQExpBuffer(&buf, ",\n array_to_string(ARRAY(SELECT option_name || ' ' || quote_literal(option_value) FROM "
! " pg_options_to_table(attfdwoptions)), ', ') AS attfdwoptions");
else
appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
if (verbose)
*************** describeOneTableDetails(const char *sche
*** 2038,2044 ****
/* Footer information about foreign table */
printfPQExpBuffer(&buf,
"SELECT s.srvname,\n"
! " f.ftoptions\n"
"FROM pg_catalog.pg_foreign_table f,\n"
" pg_catalog.pg_foreign_server s\n"
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
--- 2039,2048 ----
/* Footer information about foreign table */
printfPQExpBuffer(&buf,
"SELECT s.srvname,\n"
! " array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(ftoptions)), ', ') "
"FROM pg_catalog.pg_foreign_table f,\n"
" pg_catalog.pg_foreign_server s\n"
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
*************** listForeignDataWrappers(const char *patt
*** 3679,3685 ****
appendPQExpBuffer(&buf, ",\n ");
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
! ",\n fdwoptions AS \"%s\"",
gettext_noop("FDW Options"));
if (pset.sversion >= 90100)
--- 3683,3693 ----
appendPQExpBuffer(&buf, ",\n ");
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
! ",\n array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(fdwoptions)), ', ') "
! " AS \"%s\"",
gettext_noop("FDW Options"));
if (pset.sversion >= 90100)
*************** listForeignServers(const char *pattern,
*** 3752,3758 ****
",\n"
" s.srvtype AS \"%s\",\n"
" s.srvversion AS \"%s\",\n"
! " s.srvoptions AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
--- 3760,3770 ----
",\n"
" s.srvtype AS \"%s\",\n"
" s.srvversion AS \"%s\",\n"
! " array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(srvoptions)), ', ') "
! " AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
*************** listForeignTables(const char *pattern, b
*** 3873,3879 ****
if (verbose)
appendPQExpBuffer(&buf,
! ",\n ft.ftoptions AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("FDW Options"),
gettext_noop("Description"));
--- 3885,3895 ----
if (verbose)
appendPQExpBuffer(&buf,
! ",\n array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(ftoptions)), ', ') "
! " AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("FDW Options"),
gettext_noop("Description"));
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 4b60e8c..30ade7c 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** CREATE FOREIGN DATA WRAPPER foo OPTIONS
*** 56,62 ****
Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {testing=1} |
postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
--- 56,62 ----
Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | testing '1' |
postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
*************** CREATE FOREIGN DATA WRAPPER foo OPTIONS
*** 65,76 ****
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-----------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {testing=1,another=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
--- 65,76 ----
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+--------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | testing '1', another '2' |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
*************** ERROR: option "c" not found
*** 114,146 ****
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {a=1,b=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
--- 114,146 ----
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+--------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | a '1', b '2' |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+--------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | b '3', c '4' |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | b '3', c '4', a '2' |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
*************** HINT: Must be superuser to alter a fore
*** 150,161 ****
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
--- 150,161 ----
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | b '3', c '4', a '2', d '5' |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
*************** ERROR: permission denied to alter forei
*** 169,180 ****
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
--- 169,180 ----
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+----------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | b '3', c '4', a '2', d '5' |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
*************** ERROR: foreign-data wrapper "nonexisten
*** 183,194 ****
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
--- 183,194 ----
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+----------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | b '3', c '4', a '2', d '5' |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
*************** ERROR: invalid option "foo"
*** 289,305 ****
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
(8 rows)
SET ROLE regress_test_role;
--- 289,305 ----
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+---------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | host 'a', dbname 'b' |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | host 'a', dbname 'b' |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | host 'a', dbname 'b' |
! s7 | foreign_data_user | foo | | oracle | 17.0 | host 'a', dbname 'b' |
! s8 | foreign_data_user | postgresql | | | | host 'localhost', dbname 's8db' |
(8 rows)
SET ROLE regress_test_role;
*************** SET ROLE regress_test_role;
*** 311,328 ****
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
--- 311,328 ----
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+---------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | host 'a', dbname 'b' |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | host 'a', dbname 'b' |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | host 'a', dbname 'b' |
! s7 | foreign_data_user | foo | | oracle | 17.0 | host 'a', dbname 'b' |
! s8 | foreign_data_user | postgresql | | | | host 'localhost', dbname 's8db' |
! t1 | regress_test_role | foo | | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
*************** GRANT regress_test_indirect TO regress_t
*** 335,353 ****
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
RESET ROLE;
--- 335,353 ----
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+---------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | host 'a', dbname 'b' |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | host 'a', dbname 'b' |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | host 'a', dbname 'b' |
! s7 | foreign_data_user | foo | | oracle | 17.0 | host 'a', dbname 'b' |
! s8 | foreign_data_user | postgresql | | | | host 'localhost', dbname 's8db' |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
RESET ROLE;
*************** ALTER SERVER s3 OPTIONS (tnsname 'orcl',
*** 365,385 ****
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-----------------------------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | {servername=s1} |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521} |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b} |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
SET ROLE regress_test_role;
--- 365,385 ----
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------+-------------
! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | servername 's1' |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | host 'a', dbname 'b' |
! s3 | foreign_data_user | foo | | oracle | | tnsname 'orcl', port '1521' |
! s4 | foreign_data_user | foo | | oracle | | host 'a', dbname 'b' |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | host 'a', dbname 'b' |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | host 'a', dbname 'b' |
! s8 | foreign_data_user | postgresql | | | | host 'localhost', dbname 's8db' |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
SET ROLE regress_test_role;
*************** ERROR: role "regress_test_indirect" can
*** 416,436 ****
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------+-------------
! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | {servername=s1} |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521} |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b} |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {dbname=db1,connect_timeout=30} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
-- DROP SERVER
--- 416,436 ----
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+------------------------------------+-------------
! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | servername 's1' |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | host 'a', dbname 'b' |
! s3 | foreign_data_user | foo | | oracle | | tnsname 'orcl', port '1521' |
! s4 | foreign_data_user | foo | | oracle | | host 'a', dbname 'b' |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | host 'a', dbname 'b' |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | host 'a', dbname 'b' |
! s8 | foreign_data_user | postgresql | | | | dbname 'db1', connect_timeout '30' |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
-- DROP SERVER
*************** CREATE FOREIGN TABLE ft1 (
*** 653,673 ****
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+---------------------------+----------+-------------
! c1 | integer | not null | {param1=val1} | plain | ft1.c1
! c2 | text | | {param2=val2,param3=val3} | extended |
! c3 | date | | | plain |
Server: sc
! FDW Options: {"delimiter=,","quote=\""}
Has OIDs: no
\det+
! List of foreign tables
! Schema | Table | Server | FDW Options | Description
! --------+-------+--------+----------------------------+-------------
! public | ft1 | sc | {"delimiter=,","quote=\""} | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
--- 653,673 ----
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+------------------------------+----------+-------------
! c1 | integer | not null | param1 'val1' | plain | ft1.c1
! c2 | text | | param2 'val2', param3 'val3' | extended |
! c3 | date | | | plain |
Server: sc
! FDW Options: delimiter ',', quote '"'
Has OIDs: no
\det+
! List of foreign tables
! Schema | Table | Server | FDW Options | Description
! --------+-------+--------+--------------------------+-------------
! public | ft1 | sc | delimiter ',', quote '"' | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c7
*** 705,724 ****
ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+---------------------------+----------+-------------
! c1 | integer | not null | {param1=val1} | plain |
! c2 | text | | {param2=val2,param3=val3} | extended |
! c3 | date | | | plain |
! c4 | integer | | | plain |
! c6 | integer | not null | | plain |
! c7 | integer | | {p1=v1,p2=v2} | plain |
! c8 | text | | {p2=V2} | extended |
! c9 | integer | | | plain |
! c10 | integer | | {p1=v1} | plain |
Server: sc
! FDW Options: {"delimiter=,","quote=\""}
Has OIDs: no
-- can't change the column type if it's used elsewhere
--- 705,724 ----
ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+------------------------------+----------+-------------
! c1 | integer | not null | param1 'val1' | plain |
! c2 | text | | param2 'val2', param3 'val3' | extended |
! c3 | date | | | plain |
! c4 | integer | | | plain |
! c6 | integer | not null | | plain |
! c7 | integer | | p1 'v1', p2 'v2' | plain |
! c8 | text | | p2 'V2' | extended |
! c9 | integer | | | plain |
! c10 | integer | | p1 'v1' | plain |
Server: sc
! FDW Options: delimiter ',', quote '"'
Has OIDs: no
-- can't change the column type if it's used elsewhere
*************** ERROR: relation "ft1" does not exist
*** 749,767 ****
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers | FDW Options
! ------------------+---------+-----------+---------------------------
! foreign_column_1 | integer | not null | {param1=val1}
! c2 | text | | {param2=val2,param3=val3}
c3 | date | |
c4 | integer | |
c6 | integer | not null |
! c7 | integer | | {p1=v1,p2=v2}
! c8 | text | | {p2=V2}
! c10 | integer | | {p1=v1}
Server: sc
! FDW Options: {quote=~,escape=@}
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
--- 749,767 ----
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers | FDW Options
! ------------------+---------+-----------+------------------------------
! foreign_column_1 | integer | not null | param1 'val1'
! c2 | text | | param2 'val2', param3 'val3'
c3 | date | |
c4 | integer | |
c6 | integer | not null |
! c7 | integer | | p1 'v1', p2 'v2'
! c8 | text | | p2 'V2'
! c10 | integer | | p1 'v1'
Server: sc
! FDW Options: quote '~', escape '@'
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
Re: Change format of FDW options used in \d* commands (was: Re: per-column FDW options, v5)
2011/8/12 Shigeru Hanada <shigeru.hanada@gmail.com>:
(2011/08/12 1:05), Robert Haas wrote:
On Thu, Aug 11, 2011 at 12:04 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:Excerpts from Robert Haas's message of jue ago 11 11:50:40 -0400 2011:
2011/8/9 Shigeru Hanada<shigeru.hanada@gmail.com>:
I'd like to pick #3, and also change per-column options format. In
addition, I'd like to change options format for other FDW objects such
as wrappers, servers and user mappings for consistency. Of course, only
if it's acceptable to break backward compatibility...I think it's fine to change the display format. We haven't had these
features for very long, so users hopefully shouldn't be expecting that
everything is set in stone. We have made far bigger changes to
backslash commands that have been around for far longer (\df, I'm
looking at you).We've never promised that backslash commands behave identically across
releases. I think they are more for human consumption than machine, so
why would we care about changing one of them a bit?Yeah, I agree.
Thanks for the comments.
Attached patch changes various \d*+ commands to show FDW options in same
format as OPTIONS clause. IMHO the new format is easier to read.Example)
old: {"delimiter=,","quote=\""}
new: delimiter ',', quote '"'All regression tests including contrib's installcheck passed.
I'll add this patch to CF app as new item.
IMHO, the new format should put parentheses around the options list.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: Change format of FDW options used in \d* commands
Thanks for the review.
(2011/08/13 3:59), Robert Haas wrote:
IMHO, the new format should put parentheses around the options list.
Agreed. Revised version of patch has been attached. This version puts
parentheses around FDW option only when it was not NULL.
Regards,
--
Shigeru Hanada
Attachments:
20110818_fdw_option_format.patchtext/plain; name=20110818_fdw_option_format.patchDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 39c7136..8518215 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** describeOneTableDetails(const char *sche
*** 1272,1278 ****
else
appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! appendPQExpBuffer(&buf, ",\n a.attfdwoptions");
else
appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
if (verbose)
--- 1272,1280 ----
else
appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! appendPQExpBuffer(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT option_name || ' ' || quote_literal(option_value) FROM "
! " pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
else
appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
if (verbose)
*************** describeOneTableDetails(const char *sche
*** 2038,2044 ****
/* Footer information about foreign table */
printfPQExpBuffer(&buf,
"SELECT s.srvname,\n"
! " f.ftoptions\n"
"FROM pg_catalog.pg_foreign_table f,\n"
" pg_catalog.pg_foreign_server s\n"
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
--- 2040,2049 ----
/* Footer information about foreign table */
printfPQExpBuffer(&buf,
"SELECT s.srvname,\n"
! " array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(ftoptions)), ', ') "
"FROM pg_catalog.pg_foreign_table f,\n"
" pg_catalog.pg_foreign_server s\n"
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
*************** describeOneTableDetails(const char *sche
*** 2061,2067 ****
ftoptions = PQgetvalue(result, 0, 1);
if (ftoptions && ftoptions[0] != '\0')
{
! printfPQExpBuffer(&buf, "FDW Options: %s", ftoptions);
printTableAddFooter(&cont, buf.data);
}
PQclear(result);
--- 2066,2072 ----
ftoptions = PQgetvalue(result, 0, 1);
if (ftoptions && ftoptions[0] != '\0')
{
! printfPQExpBuffer(&buf, "FDW Options: (%s)", ftoptions);
printTableAddFooter(&cont, buf.data);
}
PQclear(result);
*************** listForeignDataWrappers(const char *patt
*** 3679,3685 ****
appendPQExpBuffer(&buf, ",\n ");
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
! ",\n fdwoptions AS \"%s\"",
gettext_noop("FDW Options"));
if (pset.sversion >= 90100)
--- 3684,3695 ----
appendPQExpBuffer(&buf, ",\n ");
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
! ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(fdwoptions)), ', ') || ')' "
! " END AS \"%s\"",
gettext_noop("FDW Options"));
if (pset.sversion >= 90100)
*************** listForeignServers(const char *pattern,
*** 3752,3758 ****
",\n"
" s.srvtype AS \"%s\",\n"
" s.srvversion AS \"%s\",\n"
! " s.srvoptions AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
--- 3762,3773 ----
",\n"
" s.srvtype AS \"%s\",\n"
" s.srvversion AS \"%s\",\n"
! " CASE WHEN srvoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(srvoptions)), ', ') || ')' "
! " END AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
*************** listUserMappings(const char *pattern, bo
*** 3818,3824 ****
if (verbose)
appendPQExpBuffer(&buf,
! ",\n um.umoptions AS \"%s\"",
gettext_noop("FDW Options"));
appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
--- 3833,3844 ----
if (verbose)
appendPQExpBuffer(&buf,
! ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(umoptions)), ', ') || ')' "
! " END AS \"%s\"",
gettext_noop("FDW Options"));
appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
*************** listForeignTables(const char *pattern, b
*** 3873,3879 ****
if (verbose)
appendPQExpBuffer(&buf,
! ",\n ft.ftoptions AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("FDW Options"),
gettext_noop("Description"));
--- 3893,3904 ----
if (verbose)
appendPQExpBuffer(&buf,
! ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(ftoptions)), ', ') || ')' "
! " END AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("FDW Options"),
gettext_noop("Description"));
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 4b60e8c..8bae4d9 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** ERROR: foreign-data wrapper "foo" alrea
*** 52,63 ****
DROP FOREIGN DATA WRAPPER foo;
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {testing=1} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
--- 52,63 ----
DROP FOREIGN DATA WRAPPER foo;
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (testing '1') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
*************** CREATE FOREIGN DATA WRAPPER foo OPTIONS
*** 65,76 ****
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-----------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {testing=1,another=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
--- 65,76 ----
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (testing '1', another '2') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
*************** ALTER FOREIGN DATA WRAPPER foo OPTIONS (
*** 113,146 ****
ERROR: option "c" not found
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {a=1,b=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
--- 113,146 ----
ERROR: option "c" not found
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (a '1', b '2') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (b '3', c '4') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-----------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (b '3', c '4', a '2') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
*************** HINT: Must be superuser to alter a fore
*** 150,161 ****
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
--- 150,161 ----
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+------------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (b '3', c '4', a '2', d '5') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
*************** ERROR: permission denied to alter forei
*** 169,180 ****
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
--- 169,180 ----
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+------------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
*************** ERROR: foreign-data wrapper "nonexisten
*** 183,194 ****
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
--- 183,194 ----
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+------------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
*************** ERROR: invalid option "foo"
*** 289,305 ****
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
(8 rows)
SET ROLE regress_test_role;
--- 289,305 ----
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
(8 rows)
SET ROLE regress_test_role;
*************** SET ROLE regress_test_role;
*** 311,328 ****
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
--- 311,328 ----
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
! t1 | regress_test_role | foo | | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
*************** GRANT regress_test_indirect TO regress_t
*** 335,353 ****
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
RESET ROLE;
--- 335,353 ----
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
RESET ROLE;
*************** ALTER SERVER s3 OPTIONS (tnsname 'orcl',
*** 365,385 ****
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-----------------------------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | {servername=s1} |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521} |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b} |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
SET ROLE regress_test_role;
--- 365,385 ----
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-----------------------------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | (servername 's1') |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | (tnsname 'orcl', port '1521') |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | (host 'a', dbname 'b') |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
SET ROLE regress_test_role;
*************** ERROR: role "regress_test_indirect" can
*** 416,436 ****
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------+-------------
! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | {servername=s1} |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521} |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b} |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {dbname=db1,connect_timeout=30} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
-- DROP SERVER
--- 416,436 ----
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+--------------------------------------+-------------
! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | (servername 's1') |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | (tnsname 'orcl', port '1521') |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | (host 'a', dbname 'b') |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
-- DROP SERVER
*************** ERROR: must be owner of foreign server
*** 584,599 ****
ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
RESET ROLE;
\deu+
! List of user mappings
! Server | User name | FDW Options
! --------+-------------------+-----------------------------
s4 | foreign_data_user |
! s4 | public | {"mapping=is public"}
! s5 | regress_test_role | {modified=1}
! s6 | regress_test_role | {username=test}
! s8 | foreign_data_user | {password=public}
! t1 | public | {modified=1}
! t1 | regress_test_role | {username=bob,password=boo}
(7 rows)
-- DROP USER MAPPING
--- 584,599 ----
ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
RESET ROLE;
\deu+
! List of user mappings
! Server | User name | FDW Options
! --------+-------------------+----------------------------------
s4 | foreign_data_user |
! s4 | public | (mapping 'is public')
! s5 | regress_test_role | (modified '1')
! s6 | regress_test_role | (username 'test')
! s8 | foreign_data_user | (password 'public')
! t1 | public | (modified '1')
! t1 | regress_test_role | (username 'bob', password 'boo')
(7 rows)
-- DROP USER MAPPING
*************** CREATE FOREIGN TABLE ft1 (
*** 653,673 ****
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+---------------------------+----------+-------------
! c1 | integer | not null | {param1=val1} | plain | ft1.c1
! c2 | text | | {param2=val2,param3=val3} | extended |
! c3 | date | | | plain |
Server: sc
! FDW Options: {"delimiter=,","quote=\""}
Has OIDs: no
\det+
List of foreign tables
Schema | Table | Server | FDW Options | Description
--------+-------+--------+----------------------------+-------------
! public | ft1 | sc | {"delimiter=,","quote=\""} | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
--- 653,673 ----
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+--------------------------------+----------+-------------
! c1 | integer | not null | (param1 'val1') | plain | ft1.c1
! c2 | text | | (param2 'val2', param3 'val3') | extended |
! c3 | date | | | plain |
Server: sc
! FDW Options: (delimiter ',', quote '"')
Has OIDs: no
\det+
List of foreign tables
Schema | Table | Server | FDW Options | Description
--------+-------+--------+----------------------------+-------------
! public | ft1 | sc | (delimiter ',', quote '"') | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c7
*** 705,724 ****
ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+---------------------------+----------+-------------
! c1 | integer | not null | {param1=val1} | plain |
! c2 | text | | {param2=val2,param3=val3} | extended |
! c3 | date | | | plain |
! c4 | integer | | | plain |
! c6 | integer | not null | | plain |
! c7 | integer | | {p1=v1,p2=v2} | plain |
! c8 | text | | {p2=V2} | extended |
! c9 | integer | | | plain |
! c10 | integer | | {p1=v1} | plain |
Server: sc
! FDW Options: {"delimiter=,","quote=\""}
Has OIDs: no
-- can't change the column type if it's used elsewhere
--- 705,724 ----
ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+--------------------------------+----------+-------------
! c1 | integer | not null | (param1 'val1') | plain |
! c2 | text | | (param2 'val2', param3 'val3') | extended |
! c3 | date | | | plain |
! c4 | integer | | | plain |
! c6 | integer | not null | | plain |
! c7 | integer | | (p1 'v1', p2 'v2') | plain |
! c8 | text | | (p2 'V2') | extended |
! c9 | integer | | | plain |
! c10 | integer | | (p1 'v1') | plain |
Server: sc
! FDW Options: (delimiter ',', quote '"')
Has OIDs: no
-- can't change the column type if it's used elsewhere
*************** ERROR: relation "ft1" does not exist
*** 749,767 ****
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers | FDW Options
! ------------------+---------+-----------+---------------------------
! foreign_column_1 | integer | not null | {param1=val1}
! c2 | text | | {param2=val2,param3=val3}
c3 | date | |
c4 | integer | |
c6 | integer | not null |
! c7 | integer | | {p1=v1,p2=v2}
! c8 | text | | {p2=V2}
! c10 | integer | | {p1=v1}
Server: sc
! FDW Options: {quote=~,escape=@}
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
--- 749,767 ----
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers | FDW Options
! ------------------+---------+-----------+--------------------------------
! foreign_column_1 | integer | not null | (param1 'val1')
! c2 | text | | (param2 'val2', param3 'val3')
c3 | date | |
c4 | integer | |
c6 | integer | not null |
! c7 | integer | | (p1 'v1', p2 'v2')
! c8 | text | | (p2 'V2')
! c10 | integer | | (p1 'v1')
Server: sc
! FDW Options: (quote '~', escape '@')
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
Re: Change format of FDW options used in \d* commands
2011/8/18 Shigeru Hanada <shigeru.hanada@gmail.com>:
Thanks for the review.
(2011/08/13 3:59), Robert Haas wrote:
IMHO, the new format should put parentheses around the options list.
Agreed. Revised version of patch has been attached. This version puts
parentheses around FDW option only when it was not NULL.
ISTM you should do quote_ident() on the option names. For example, after:
create foreign data wrapper dummy;
create server s1 foreign data wrapper dummy;
create foreign table ft1 (a int) server s1 options ("(" ')');
With your patch, I get:
rhaas=# \d+ ft1
Foreign table "public.ft1"
Column | Type | Modifiers | FDW Options | Storage | Description
--------+---------+-----------+-------------+---------+-------------
a | integer | | | plain |
Server: s1
FDW Options: (( ')')
Has OIDs: no
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: Change format of FDW options used in \d* commands
(2011/08/18 23:21), Robert Haas wrote:
ISTM you should do quote_ident() on the option names. For example, after:
create foreign data wrapper dummy;
create server s1 foreign data wrapper dummy;
create foreign table ft1 (a int) server s1 options ("(" ')');With your patch, I get:
rhaas=# \d+ ft1
Foreign table "public.ft1"
Column | Type | Modifiers | FDW Options | Storage | Description
--------+---------+-----------+-------------+---------+-------------
a | integer | | | plain |
Server: s1
FDW Options: (( ')')
Has OIDs: no
Oops, good catch. I've fixed psql to use quote_ident() for option_name,
and modified regression tests to use special characters in option names.
Please try revised version of patch.
BTW, I noticed that pg_dump has same issue since 8.4, initial release of
SQL/MED infrastructure. If a FDW option was defined on any FDW object
with a name which contains one of special characters such as space and
parentheses, pg_dump generates invalid OPTIONS clause such as "OPTIONS
(separated name 'value')".
~~~~~~~~~~~~~~
Perhaps this issue has been overlooked because dblink is practically the
only use case of FDW option before 9.1. Since 9.1, users might get
various FDW and some of those might use special characters in option
name. ISTM that this fix should be back-patched, at least to 9.1.
Please find attached patches for each STABLE branch.
Regards,
--
Shigeru Hanada
Attachments:
20110819_fdw_option_format.patchtext/plain; name=20110819_fdw_option_format.patchDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 39c7136..d5466f8 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** describeOneTableDetails(const char *sche
*** 1272,1278 ****
else
appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! appendPQExpBuffer(&buf, ",\n a.attfdwoptions");
else
appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
if (verbose)
--- 1272,1280 ----
else
appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! appendPQExpBuffer(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM "
! " pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
else
appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
if (verbose)
*************** describeOneTableDetails(const char *sche
*** 2038,2044 ****
/* Footer information about foreign table */
printfPQExpBuffer(&buf,
"SELECT s.srvname,\n"
! " f.ftoptions\n"
"FROM pg_catalog.pg_foreign_table f,\n"
" pg_catalog.pg_foreign_server s\n"
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
--- 2040,2049 ----
/* Footer information about foreign table */
printfPQExpBuffer(&buf,
"SELECT s.srvname,\n"
! " array_to_string(ARRAY(SELECT "
! " quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(ftoptions)), ', ') "
"FROM pg_catalog.pg_foreign_table f,\n"
" pg_catalog.pg_foreign_server s\n"
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
*************** describeOneTableDetails(const char *sche
*** 2061,2067 ****
ftoptions = PQgetvalue(result, 0, 1);
if (ftoptions && ftoptions[0] != '\0')
{
! printfPQExpBuffer(&buf, "FDW Options: %s", ftoptions);
printTableAddFooter(&cont, buf.data);
}
PQclear(result);
--- 2066,2072 ----
ftoptions = PQgetvalue(result, 0, 1);
if (ftoptions && ftoptions[0] != '\0')
{
! printfPQExpBuffer(&buf, "FDW Options: (%s)", ftoptions);
printTableAddFooter(&cont, buf.data);
}
PQclear(result);
*************** listForeignDataWrappers(const char *patt
*** 3679,3685 ****
appendPQExpBuffer(&buf, ",\n ");
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
! ",\n fdwoptions AS \"%s\"",
gettext_noop("FDW Options"));
if (pset.sversion >= 90100)
--- 3684,3695 ----
appendPQExpBuffer(&buf, ",\n ");
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
! ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(fdwoptions)), ', ') || ')' "
! " END AS \"%s\"",
gettext_noop("FDW Options"));
if (pset.sversion >= 90100)
*************** listForeignServers(const char *pattern,
*** 3752,3758 ****
",\n"
" s.srvtype AS \"%s\",\n"
" s.srvversion AS \"%s\",\n"
! " s.srvoptions AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
--- 3762,3773 ----
",\n"
" s.srvtype AS \"%s\",\n"
" s.srvversion AS \"%s\",\n"
! " CASE WHEN srvoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(srvoptions)), ', ') || ')' "
! " END AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
*************** listUserMappings(const char *pattern, bo
*** 3818,3824 ****
if (verbose)
appendPQExpBuffer(&buf,
! ",\n um.umoptions AS \"%s\"",
gettext_noop("FDW Options"));
appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
--- 3833,3844 ----
if (verbose)
appendPQExpBuffer(&buf,
! ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(umoptions)), ', ') || ')' "
! " END AS \"%s\"",
gettext_noop("FDW Options"));
appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
*************** listForeignTables(const char *pattern, b
*** 3873,3879 ****
if (verbose)
appendPQExpBuffer(&buf,
! ",\n ft.ftoptions AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("FDW Options"),
gettext_noop("Description"));
--- 3893,3904 ----
if (verbose)
appendPQExpBuffer(&buf,
! ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(ftoptions)), ', ') || ')' "
! " END AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("FDW Options"),
gettext_noop("Description"));
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 4b60e8c..4165a41 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** ERROR: foreign-data wrapper "foo" alrea
*** 52,63 ****
DROP FOREIGN DATA WRAPPER foo;
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {testing=1} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
--- 52,63 ----
DROP FOREIGN DATA WRAPPER foo;
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (testing '1') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
*************** CREATE FOREIGN DATA WRAPPER foo OPTIONS
*** 65,76 ****
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-----------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {testing=1,another=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
--- 65,76 ----
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (testing '1', another '2') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
*************** ALTER FOREIGN DATA WRAPPER foo OPTIONS (
*** 113,146 ****
ERROR: option "c" not found
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {a=1,b=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
--- 113,146 ----
ERROR: option "c" not found
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (a '1', b '2') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (b '3', c '4') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-----------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (b '3', c '4', a '2') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
*************** HINT: Must be superuser to alter a fore
*** 150,161 ****
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
--- 150,161 ----
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+------------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (b '3', c '4', a '2', d '5') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
*************** ERROR: permission denied to alter forei
*** 169,180 ****
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
--- 169,180 ----
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+------------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
*************** ERROR: foreign-data wrapper "nonexisten
*** 183,194 ****
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
--- 183,194 ----
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+------------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
*************** drop cascades to user mapping for foreig
*** 274,280 ****
-- exercise CREATE SERVER
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
ERROR: foreign-data wrapper "foo" does not exist
! CREATE FOREIGN DATA WRAPPER foo OPTIONS (test_wrapper 'true');
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
ERROR: server "s1" already exists
--- 274,280 ----
-- exercise CREATE SERVER
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
ERROR: foreign-data wrapper "foo" does not exist
! CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true');
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
ERROR: server "s1" already exists
*************** ERROR: invalid option "foo"
*** 289,305 ****
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
(8 rows)
SET ROLE regress_test_role;
--- 289,305 ----
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
(8 rows)
SET ROLE regress_test_role;
*************** SET ROLE regress_test_role;
*** 311,328 ****
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
--- 311,328 ----
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
! t1 | regress_test_role | foo | | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
*************** GRANT regress_test_indirect TO regress_t
*** 335,353 ****
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
RESET ROLE;
--- 335,353 ----
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
RESET ROLE;
*************** ALTER SERVER s0 OPTIONS (a '1');
*** 361,385 ****
ERROR: server "s0" does not exist
ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1');
ALTER SERVER s2 VERSION '1.1';
! ALTER SERVER s3 OPTIONS (tnsname 'orcl', port '1521');
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-----------------------------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | {servername=s1} |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521} |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b} |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
SET ROLE regress_test_role;
--- 361,385 ----
ERROR: server "s0" does not exist
ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1');
ALTER SERVER s2 VERSION '1.1';
! ALTER SERVER s3 OPTIONS ("tns name" 'orcl', port '1521');
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-----------------------------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | (servername 's1') |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | (host 'a', dbname 'b') |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
SET ROLE regress_test_role;
*************** ERROR: role "regress_test_indirect" can
*** 416,436 ****
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------+-------------
! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | {servername=s1} |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521} |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b} |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {dbname=db1,connect_timeout=30} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
-- DROP SERVER
--- 416,436 ----
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+--------------------------------------+-------------
! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | (servername 's1') |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | (host 'a', dbname 'b') |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
-- DROP SERVER
*************** ERROR: server "s1" does not exist
*** 533,539 ****
CREATE USER MAPPING FOR current_user SERVER s4;
CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate
ERROR: user mapping "foreign_data_user" already exists for server s4
! CREATE USER MAPPING FOR public SERVER s4 OPTIONS (mapping 'is public');
CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR
ERROR: invalid option "username"
HINT: Valid options in this context are: user, password
--- 533,539 ----
CREATE USER MAPPING FOR current_user SERVER s4;
CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate
ERROR: user mapping "foreign_data_user" already exists for server s4
! CREATE USER MAPPING FOR public SERVER s4 OPTIONS ("this mapping" 'is public');
CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR
ERROR: invalid option "username"
HINT: Valid options in this context are: user, password
*************** ERROR: must be owner of foreign server
*** 584,599 ****
ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
RESET ROLE;
\deu+
! List of user mappings
! Server | User name | FDW Options
! --------+-------------------+-----------------------------
s4 | foreign_data_user |
! s4 | public | {"mapping=is public"}
! s5 | regress_test_role | {modified=1}
! s6 | regress_test_role | {username=test}
! s8 | foreign_data_user | {password=public}
! t1 | public | {modified=1}
! t1 | regress_test_role | {username=bob,password=boo}
(7 rows)
-- DROP USER MAPPING
--- 584,599 ----
ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
RESET ROLE;
\deu+
! List of user mappings
! Server | User name | FDW Options
! --------+-------------------+----------------------------------
s4 | foreign_data_user |
! s4 | public | ("this mapping" 'is public')
! s5 | regress_test_role | (modified '1')
! s6 | regress_test_role | (username 'test')
! s8 | foreign_data_user | (password 'public')
! t1 | public | (modified '1')
! t1 | regress_test_role | (username 'bob', password 'boo')
(7 rows)
-- DROP USER MAPPING
*************** ERROR: syntax error at or near "WITH OI
*** 646,673 ****
LINE 1: CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS (param1 'val1') NOT NULL,
c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
! ) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+---------------------------+----------+-------------
! c1 | integer | not null | {param1=val1} | plain | ft1.c1
! c2 | text | | {param2=val2,param3=val3} | extended |
! c3 | date | | | plain |
Server: sc
! FDW Options: {"delimiter=,","quote=\""}
Has OIDs: no
\det+
! List of foreign tables
! Schema | Table | Server | FDW Options | Description
! --------+-------+--------+----------------------------+-------------
! public | ft1 | sc | {"delimiter=,","quote=\""} | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
--- 646,673 ----
LINE 1: CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
! ) SERVER sc OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+--------------------------------+----------+-------------
! c1 | integer | not null | ("param 1" 'val1') | plain | ft1.c1
! c2 | text | | (param2 'val2', param3 'val3') | extended |
! c3 | date | | | plain |
Server: sc
! FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
Has OIDs: no
\det+
! List of foreign tables
! Schema | Table | Server | FDW Options | Description
! --------+-------+--------+-------------------------------------------------+-------------
! public | ft1 | sc | (delimiter ',', quote '"', "be quoted" 'value') | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c7
*** 705,724 ****
ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+---------------------------+----------+-------------
! c1 | integer | not null | {param1=val1} | plain |
! c2 | text | | {param2=val2,param3=val3} | extended |
! c3 | date | | | plain |
! c4 | integer | | | plain |
! c6 | integer | not null | | plain |
! c7 | integer | | {p1=v1,p2=v2} | plain |
! c8 | text | | {p2=V2} | extended |
! c9 | integer | | | plain |
! c10 | integer | | {p1=v1} | plain |
Server: sc
! FDW Options: {"delimiter=,","quote=\""}
Has OIDs: no
-- can't change the column type if it's used elsewhere
--- 705,724 ----
ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+--------------------------------+----------+-------------
! c1 | integer | not null | ("param 1" 'val1') | plain |
! c2 | text | | (param2 'val2', param3 'val3') | extended |
! c3 | date | | | plain |
! c4 | integer | | | plain |
! c6 | integer | not null | | plain |
! c7 | integer | | (p1 'v1', p2 'v2') | plain |
! c8 | text | | (p2 'V2') | extended |
! c9 | integer | | | plain |
! c10 | integer | | (p1 'v1') | plain |
Server: sc
! FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
Has OIDs: no
-- can't change the column type if it's used elsewhere
*************** ERROR: relation "ft1" does not exist
*** 749,767 ****
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers | FDW Options
! ------------------+---------+-----------+---------------------------
! foreign_column_1 | integer | not null | {param1=val1}
! c2 | text | | {param2=val2,param3=val3}
c3 | date | |
c4 | integer | |
c6 | integer | not null |
! c7 | integer | | {p1=v1,p2=v2}
! c8 | text | | {p2=V2}
! c10 | integer | | {p1=v1}
Server: sc
! FDW Options: {quote=~,escape=@}
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
--- 749,767 ----
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers | FDW Options
! ------------------+---------+-----------+--------------------------------
! foreign_column_1 | integer | not null | ("param 1" 'val1')
! c2 | text | | (param2 'val2', param3 'val3')
c3 | date | |
c4 | integer | |
c6 | integer | not null |
! c7 | integer | | (p1 'v1', p2 'v2')
! c8 | text | | (p2 'V2')
! c10 | integer | | (p1 'v1')
Server: sc
! FDW Options: (quote '~', "be quoted" 'value', escape '@')
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
*************** SELECT * FROM information_schema.foreign
*** 775,781 ****
SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
foreign_data_wrapper_catalog | foreign_data_wrapper_name | option_name | option_value
------------------------------+---------------------------+--------------+--------------
! regression | foo | test_wrapper | true
(1 row)
SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
--- 775,781 ----
SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
foreign_data_wrapper_catalog | foreign_data_wrapper_name | option_name | option_value
------------------------------+---------------------------+--------------+--------------
! regression | foo | test wrapper | true
(1 row)
SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
*************** SELECT * FROM information_schema.user_ma
*** 815,829 ****
(8 rows)
SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4;
! authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
! --------------------------+------------------------+---------------------+-------------+--------------
! foreign_data_user | regression | s8 | password | public
! PUBLIC | regression | s4 | mapping | is public
! PUBLIC | regression | t1 | modified | 1
! regress_test_role | regression | s5 | modified | 1
! regress_test_role | regression | s6 | username | test
! regress_test_role | regression | t1 | password | boo
! regress_test_role | regression | t1 | username | bob
(7 rows)
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
--- 815,829 ----
(8 rows)
SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4;
! authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
! --------------------------+------------------------+---------------------+--------------+--------------
! foreign_data_user | regression | s8 | password | public
! PUBLIC | regression | s4 | this mapping | is public
! PUBLIC | regression | t1 | modified | 1
! regress_test_role | regression | s5 | modified | 1
! regress_test_role | regression | s6 | username | test
! regress_test_role | regression | t1 | password | boo
! regress_test_role | regression | t1 | username | bob
(7 rows)
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
*************** SELECT * FROM information_schema.foreign
*** 853,861 ****
SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4;
foreign_table_catalog | foreign_table_schema | foreign_table_name | option_name | option_value
-----------------------+----------------------+--------------------+-------------+--------------
regression | foreign_schema | foreign_table_1 | escape | @
regression | foreign_schema | foreign_table_1 | quote | ~
! (2 rows)
SET ROLE regress_test_role;
SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
--- 853,862 ----
SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4;
foreign_table_catalog | foreign_table_schema | foreign_table_name | option_name | option_value
-----------------------+----------------------+--------------------+-------------+--------------
+ regression | foreign_schema | foreign_table_1 | be quoted | value
regression | foreign_schema | foreign_table_1 | escape | @
regression | foreign_schema | foreign_table_1 | quote | ~
! (3 rows)
SET ROLE regress_test_role;
SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index b3b49cc..dadd405 100644
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
*************** DROP FOREIGN DATA WRAPPER foo CASCADE;
*** 116,122 ****
-- exercise CREATE SERVER
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
! CREATE FOREIGN DATA WRAPPER foo OPTIONS (test_wrapper 'true');
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
--- 116,122 ----
-- exercise CREATE SERVER
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
! CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true');
CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
*************** ALTER SERVER s0;
*** 154,160 ****
ALTER SERVER s0 OPTIONS (a '1'); -- ERROR
ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1');
ALTER SERVER s2 VERSION '1.1';
! ALTER SERVER s3 OPTIONS (tnsname 'orcl', port '1521');
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
--- 154,160 ----
ALTER SERVER s0 OPTIONS (a '1'); -- ERROR
ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1');
ALTER SERVER s2 VERSION '1.1';
! ALTER SERVER s3 OPTIONS ("tns name" 'orcl', port '1521');
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
*************** CREATE USER MAPPING FOR regress_test_mis
*** 210,216 ****
CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
CREATE USER MAPPING FOR current_user SERVER s4;
CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate
! CREATE USER MAPPING FOR public SERVER s4 OPTIONS (mapping 'is public');
CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR
CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password 'secret');
ALTER SERVER s5 OWNER TO regress_test_role;
--- 210,216 ----
CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
CREATE USER MAPPING FOR current_user SERVER s4;
CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate
! CREATE USER MAPPING FOR public SERVER s4 OPTIONS ("this mapping" 'is public');
CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR
CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password 'secret');
ALTER SERVER s5 OWNER TO regress_test_role;
*************** CREATE FOREIGN TABLE ft1 () SERVER no_se
*** 264,273 ****
CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR
CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS; -- ERROR
CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS (param1 'val1') NOT NULL,
c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
! ) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
--- 264,273 ----
CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR
CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS; -- ERROR
CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
! ) SERVER sc OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
pg_dump_quote_option_name_84.patchtext/plain; name=pg_dump_quote_option_name_84.patchDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ccbc1d8..834464b 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getForeignDataWrappers(int *numForeignDa
*** 5473,5479 ****
appendPQExpBuffer(query, "SELECT tableoid, oid, fdwname, "
"(%s fdwowner) AS rolname, fdwvalidator::pg_catalog.regproc, fdwacl,"
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
--- 5473,5480 ----
appendPQExpBuffer(query, "SELECT tableoid, oid, fdwname, "
"(%s fdwowner) AS rolname, fdwvalidator::pg_catalog.regproc, fdwacl,"
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
*************** getForeignServers(int *numForeignServers
*** 5558,5564 ****
"(%s srvowner) AS rolname, "
"srvfdw, srvtype, srvversion, srvacl,"
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(srvoptions)), ', ') AS srvoptions "
"FROM pg_foreign_server",
username_subquery);
--- 5559,5566 ----
"(%s srvowner) AS rolname, "
"srvfdw, srvtype, srvversion, srvacl,"
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(srvoptions)), ', ') AS srvoptions "
"FROM pg_foreign_server",
username_subquery);
*************** dumpUserMappings(Archive *fout,
*** 9576,9582 ****
appendPQExpBuffer(query,
"SELECT usename, "
! "array_to_string(ARRAY(SELECT option_name || ' ' || quote_literal(option_value) FROM pg_options_to_table(umoptions)), ', ') AS umoptions\n"
"FROM pg_user_mappings "
"WHERE srvid = %u",
catalogId.oid);
--- 9578,9584 ----
appendPQExpBuffer(query,
"SELECT usename, "
! "array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM pg_options_to_table(umoptions)), ', ') AS umoptions\n"
"FROM pg_user_mappings "
"WHERE srvid = %u",
catalogId.oid);
pg_dump_quote_option_name_90.patchtext/plain; name=pg_dump_quote_option_name_90.patchDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8721e65..13ff651 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getForeignDataWrappers(int *numForeignDa
*** 5884,5890 ****
appendPQExpBuffer(query, "SELECT tableoid, oid, fdwname, "
"(%s fdwowner) AS rolname, fdwvalidator::pg_catalog.regproc, fdwacl,"
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
--- 5884,5891 ----
appendPQExpBuffer(query, "SELECT tableoid, oid, fdwname, "
"(%s fdwowner) AS rolname, fdwvalidator::pg_catalog.regproc, fdwacl,"
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
*************** getForeignServers(int *numForeignServers
*** 5969,5975 ****
"(%s srvowner) AS rolname, "
"srvfdw, srvtype, srvversion, srvacl,"
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(srvoptions)), ', ') AS srvoptions "
"FROM pg_foreign_server",
username_subquery);
--- 5970,5977 ----
"(%s srvowner) AS rolname, "
"srvfdw, srvtype, srvversion, srvacl,"
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(srvoptions)), ', ') AS srvoptions "
"FROM pg_foreign_server",
username_subquery);
*************** dumpUserMappings(Archive *fout,
*** 10275,10281 ****
appendPQExpBuffer(query,
"SELECT usename, "
! "array_to_string(ARRAY(SELECT option_name || ' ' || quote_literal(option_value) FROM pg_options_to_table(umoptions)), ', ') AS umoptions\n"
"FROM pg_user_mappings "
"WHERE srvid = %u",
catalogId.oid);
--- 10277,10283 ----
appendPQExpBuffer(query,
"SELECT usename, "
! "array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM pg_options_to_table(umoptions)), ', ') AS umoptions\n"
"FROM pg_user_mappings "
"WHERE srvid = %u",
catalogId.oid);
pg_dump_quote_option_name_91.patchtext/plain; name=pg_dump_quote_option_name_91.patchDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9e69b0f..9a0923f 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getForeignDataWrappers(int *numForeignDa
*** 6369,6375 ****
"fdwhandler::pg_catalog.regproc, "
"fdwvalidator::pg_catalog.regproc, fdwacl, "
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
--- 6369,6376 ----
"fdwhandler::pg_catalog.regproc, "
"fdwvalidator::pg_catalog.regproc, fdwacl, "
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
*************** getForeignDataWrappers(int *numForeignDa
*** 6381,6387 ****
"'-' AS fdwhandler, "
"fdwvalidator::pg_catalog.regproc, fdwacl, "
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
--- 6382,6389 ----
"'-' AS fdwhandler, "
"fdwvalidator::pg_catalog.regproc, fdwacl, "
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
*************** getForeignServers(int *numForeignServers
*** 6468,6474 ****
"(%s srvowner) AS rolname, "
"srvfdw, srvtype, srvversion, srvacl,"
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(srvoptions)), ', ') AS srvoptions "
"FROM pg_foreign_server",
username_subquery);
--- 6470,6477 ----
"(%s srvowner) AS rolname, "
"srvfdw, srvtype, srvversion, srvacl,"
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(srvoptions)), ', ') AS srvoptions "
"FROM pg_foreign_server",
username_subquery);
*************** dumpUserMappings(Archive *fout,
*** 11395,11401 ****
appendPQExpBuffer(query,
"SELECT usename, "
! "array_to_string(ARRAY(SELECT option_name || ' ' || quote_literal(option_value) FROM pg_options_to_table(umoptions)), ', ') AS umoptions\n"
"FROM pg_user_mappings "
"WHERE srvid = %u",
catalogId.oid);
--- 11398,11404 ----
appendPQExpBuffer(query,
"SELECT usename, "
! "array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM pg_options_to_table(umoptions)), ', ') AS umoptions\n"
"FROM pg_user_mappings "
"WHERE srvid = %u",
catalogId.oid);
*************** dumpTableSchema(Archive *fout, TableInfo
*** 12043,12049 ****
/* retrieve name of foreign server and generic options */
appendPQExpBuffer(query,
"SELECT fs.srvname, array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value)"
" FROM pg_options_to_table(ftoptions)), ', ') AS ftoptions "
"FROM pg_foreign_table ft JOIN pg_foreign_server fs "
" ON (fs.oid = ft.ftserver) "
--- 12046,12053 ----
/* retrieve name of foreign server and generic options */
appendPQExpBuffer(query,
"SELECT fs.srvname, array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value)"
" FROM pg_options_to_table(ftoptions)), ', ') AS ftoptions "
"FROM pg_foreign_table ft JOIN pg_foreign_server fs "
" ON (fs.oid = ft.ftserver) "
pg_dump_quote_option_name_92.patchtext/plain; name=pg_dump_quote_option_name_92.patchDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index cf0fc4b..d7a147e 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 5626,5632 ****
"CASE WHEN a.attcollation <> t.typcollation "
"THEN a.attcollation ELSE 0 END AS attcollation, "
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(attfdwoptions)), ', ') "
" AS attfdwoptions "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
--- 5626,5633 ----
"CASE WHEN a.attcollation <> t.typcollation "
"THEN a.attcollation ELSE 0 END AS attcollation, "
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(attfdwoptions)), ', ') "
" AS attfdwoptions "
"FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
*************** getForeignDataWrappers(int *numForeignDa
*** 6420,6426 ****
"fdwhandler::pg_catalog.regproc, "
"fdwvalidator::pg_catalog.regproc, fdwacl, "
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
--- 6421,6428 ----
"fdwhandler::pg_catalog.regproc, "
"fdwvalidator::pg_catalog.regproc, fdwacl, "
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
*************** getForeignDataWrappers(int *numForeignDa
*** 6432,6438 ****
"'-' AS fdwhandler, "
"fdwvalidator::pg_catalog.regproc, fdwacl, "
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
--- 6434,6441 ----
"'-' AS fdwhandler, "
"fdwvalidator::pg_catalog.regproc, fdwacl, "
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions "
"FROM pg_foreign_data_wrapper",
username_subquery);
*************** getForeignServers(int *numForeignServers
*** 6519,6525 ****
"(%s srvowner) AS rolname, "
"srvfdw, srvtype, srvversion, srvacl,"
"array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value) "
" FROM pg_options_to_table(srvoptions)), ', ') AS srvoptions "
"FROM pg_foreign_server",
username_subquery);
--- 6522,6529 ----
"(%s srvowner) AS rolname, "
"srvfdw, srvtype, srvversion, srvacl,"
"array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value) "
" FROM pg_options_to_table(srvoptions)), ', ') AS srvoptions "
"FROM pg_foreign_server",
username_subquery);
*************** dumpUserMappings(Archive *fout,
*** 11446,11452 ****
appendPQExpBuffer(query,
"SELECT usename, "
! "array_to_string(ARRAY(SELECT option_name || ' ' || quote_literal(option_value) FROM pg_options_to_table(umoptions)), ', ') AS umoptions\n"
"FROM pg_user_mappings "
"WHERE srvid = %u",
catalogId.oid);
--- 11450,11456 ----
appendPQExpBuffer(query,
"SELECT usename, "
! "array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM pg_options_to_table(umoptions)), ', ') AS umoptions\n"
"FROM pg_user_mappings "
"WHERE srvid = %u",
catalogId.oid);
*************** dumpTableSchema(Archive *fout, TableInfo
*** 12094,12100 ****
/* retrieve name of foreign server and generic options */
appendPQExpBuffer(query,
"SELECT fs.srvname, array_to_string(ARRAY("
! " SELECT option_name || ' ' || quote_literal(option_value)"
" FROM pg_options_to_table(ftoptions)), ', ') AS ftoptions "
"FROM pg_foreign_table ft JOIN pg_foreign_server fs "
" ON (fs.oid = ft.ftserver) "
--- 12098,12105 ----
/* retrieve name of foreign server and generic options */
appendPQExpBuffer(query,
"SELECT fs.srvname, array_to_string(ARRAY("
! " SELECT quote_ident(option_name) || ' ' || "
! " quote_literal(option_value)"
" FROM pg_options_to_table(ftoptions)), ', ') AS ftoptions "
"FROM pg_foreign_table ft JOIN pg_foreign_server fs "
" ON (fs.oid = ft.ftserver) "
Re: Change format of FDW options used in \d* commands
2011/8/19 Shigeru Hanada <shigeru.hanada@gmail.com>:
BTW, I noticed that pg_dump has same issue since 8.4, initial release of
SQL/MED infrastructure. If a FDW option was defined on any FDW object
with a name which contains one of special characters such as space and
parentheses, pg_dump generates invalid OPTIONS clause such as "OPTIONS
(separated name 'value')".
~~~~~~~~~~~~~~
Perhaps this issue has been overlooked because dblink is practically the
only use case of FDW option before 9.1. Since 9.1, users might get
various FDW and some of those might use special characters in option
name. ISTM that this fix should be back-patched, at least to 9.1.
Please find attached patches for each STABLE branch.
Good catch, committed.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: Change format of FDW options used in \d* commands
2011/8/19 Shigeru Hanada <shigeru.hanada@gmail.com>:
Oops, good catch. I've fixed psql to use quote_ident() for option_name,
and modified regression tests to use special characters in option names.
Please try revised version of patch.
This part looks good to me, too. Committed.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company