psql document fix about showing FDW options
Started by Shigeru Hanadaover 14 years ago6 messages
I noticed that psql document wrongly says that \d+ command shows
per-table FDW options of a foreign table, but in fact, per-table FDW
options are shown only in the result of \det+ command. Attached patch
removes this wrong description.
This fix should be applied to 9.1 too.
Regards,
--
Shigeru Hanada
Attachments:
fix_psql_doc.patchtext/plain; name=fix_psql_doc.patchDownload
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index e2e2abe..926ee60 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** testdb=>
*** 899,906 ****
The command form <literal>\d+</literal> is identical, except that
more information is displayed: any comments associated with the
columns of the table are shown, as is the presence of OIDs in the
! table, the view definition if the relation is a view, and the generic
! options if the relation is a foreign table.
</para>
<para>
--- 899,905 ----
The command form <literal>\d+</literal> is identical, except that
more information is displayed: any comments associated with the
columns of the table are shown, as is the presence of OIDs in the
! table, and the view definition if the relation is a view.
</para>
<para>
Re: psql document fix about showing FDW options
2011/8/8 Shigeru Hanada <shigeru.hanada@gmail.com>:
I noticed that psql document wrongly says that \d+ command shows
per-table FDW options of a foreign table, but in fact, per-table FDW
options are shown only in the result of \det+ command. Attached patch
removes this wrong description.This fix should be applied to 9.1 too.
I think we ought to fix the behavior, rather than the documentation.
It's just weird to have something show up in the list view that
doesn't appear in the detail view.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: psql document fix about showing FDW options
(2011/08/09 1:20), Robert Haas wrote:
2011/8/8 Shigeru Hanada<shigeru.hanada@gmail.com>:
I noticed that psql document wrongly says that \d+ command shows
per-table FDW options of a foreign table, but in fact, per-table FDW
options are shown only in the result of \det+ command. Attached patch
removes this wrong description.This fix should be applied to 9.1 too.
I think we ought to fix the behavior, rather than the documentation.
It's just weird to have something show up in the list view that
doesn't appear in the detail view.
Agreed, but you might have misunderstand behavior of \d* commands,
because currently per-table options aren't shown in detail view of \d
command. Please let me clarify the specs about FDW options in backslash
commands.
Current head shows FDW options in the result of:
command | shown FDW options
--------------+-------------------
\d (list) | none
\d+ (list) | none
\d (detail) | per-column
\d+ (detail) | per-column
\det | none
\det+ | per-table
Note that \det doesn't have detail view.
I think showing per-table FDW options in footer of \d (not only \d+) is
reasonable because it shows per-column FDW options too. Please see a
sample below.
postgres=# \d pgbench_accounts
Foreign table "public.pgbench_accounts"
Column | Type | Modifiers | Options
----------+---------------+-----------+---------------
aid | integer | not null | {colname=aid}
bid | integer | |
abalance | integer | |
filler | character(84) | |
Server: pgbench
Options: {nspname=public,"relname=foo bar"}
It seems fine ... but here I recall that the header "Options" is also
used for reloptions. So it might be worth changing those headers to
"FDW Options" to avoid future conflicts. If we do so, we would also
have to change existing headers for FDW/server/user mapping as well for
consistency, but it breaks backward compatibility. Thoughts?
I attached WIP patch for these fixes.
Please apply show_per_table_options.patch first against head, and then
rename_desc_headers.patch can be applied.
Regards,
--
Shigeru Hanada
Attachments:
show_per_table_options.patchtext/plain; name=show_per_table_options.patchDownload
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index db97df1..e8b4f73 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** testdb=>
*** 892,906 ****
<para>
For some types of relation, <literal>\d</> shows additional information
for each column: column values for sequences, indexed expression for
! indexes and per-column foreign data wrapper 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
columns of the table are shown, as is the presence of OIDs in the
! table, the view definition if the relation is a view, and the generic
! options if the relation is a foreign table.
</para>
<para>
--- 892,905 ----
<para>
For some types of relation, <literal>\d</> shows additional information
for each column: column values for sequences, indexed expression for
! indexes and foreign data wrapper 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
columns of the table are shown, as is the presence of OIDs in the
! table, the view definition if the relation is a view.
</para>
<para>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f08f917..0f54964 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** describeOneTableDetails(const char *sche
*** 2064,2072 ****
/* print foreign server name */
if (tableinfo.relkind == 'f')
{
/* Footer information about foreign table */
printfPQExpBuffer(&buf,
! "SELECT s.srvname\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;",
--- 2064,2075 ----
/* print foreign server name */
if (tableinfo.relkind == 'f')
{
+ char *ftoptions;
+
/* 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;",
*************** describeOneTableDetails(const char *sche
*** 2080,2088 ****
--- 2083,2100 ----
goto error_return;
}
+ /* Print server name */
printfPQExpBuffer(&buf, "Server: %s",
PQgetvalue(result, 0, 0));
printTableAddFooter(&cont, buf.data);
+
+ /* Print per-table FDW options, if any */
+ ftoptions = PQgetvalue(result, 0, 1);
+ if (ftoptions && ftoptions[0] != '\0')
+ {
+ printfPQExpBuffer(&buf, "Options: %s", ftoptions);
+ printTableAddFooter(&cont, buf.data);
+ }
PQclear(result);
}
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index ef13f27..5c48d83 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
*** 660,665 ****
--- 660,666 ----
c2 | text | | {param2=val2,param3=val3} | extended |
c3 | date | | | plain |
Server: sc
+ Options: {"delimiter=,","quote=\""}
Has OIDs: no
\det+
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8
*** 717,722 ****
--- 718,724 ----
c9 | integer | | | plain |
c10 | integer | | {p1=v1} | plain |
Server: sc
+ Options: {"delimiter=,","quote=\""}
Has OIDs: no
-- can't change the column type if it's used elsewhere
*************** ALTER FOREIGN TABLE foreign_schema.ft1 R
*** 759,764 ****
--- 761,767 ----
c8 | text | | {p2=V2}
c10 | integer | | {p1=v1}
Server: sc
+ Options: {quote=~,escape=@}
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
rename_desc_headers.patchtext/plain; name=rename_desc_headers.patchDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 0f54964..ba36a92 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** describeOneTableDetails(const char *sche
*** 1398,1404 ****
headers[cols++] = gettext_noop("Definition");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! headers[cols++] = gettext_noop("Options");
if (verbose)
{
--- 1398,1404 ----
headers[cols++] = gettext_noop("Definition");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! headers[cols++] = gettext_noop("FDW Options");
if (verbose)
{
*************** describeOneTableDetails(const char *sche
*** 2092,2098 ****
ftoptions = PQgetvalue(result, 0, 1);
if (ftoptions && ftoptions[0] != '\0')
{
! printfPQExpBuffer(&buf, "Options: %s", ftoptions);
printTableAddFooter(&cont, buf.data);
}
PQclear(result);
--- 2092,2098 ----
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
*** 3710,3716 ****
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
",\n fdwoptions AS \"%s\"",
! gettext_noop("Options"));
if (pset.sversion >= 90100)
appendPQExpBuffer(&buf,
--- 3710,3716 ----
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
",\n fdwoptions AS \"%s\"",
! gettext_noop("FDW Options"));
if (pset.sversion >= 90100)
appendPQExpBuffer(&buf,
*************** listForeignServers(const char *pattern,
*** 3786,3792 ****
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
! gettext_noop("Options"),
gettext_noop("Description"));
}
--- 3786,3792 ----
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
! gettext_noop("FDW Options"),
gettext_noop("Description"));
}
*************** listUserMappings(const char *pattern, bo
*** 3849,3855 ****
if (verbose)
appendPQExpBuffer(&buf,
",\n um.umoptions AS \"%s\"",
! gettext_noop("Options"));
appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
--- 3849,3855 ----
if (verbose)
appendPQExpBuffer(&buf,
",\n um.umoptions AS \"%s\"",
! gettext_noop("FDW Options"));
appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
*************** listForeignTables(const char *pattern, b
*** 3905,3911 ****
appendPQExpBuffer(&buf,
",\n ft.ftoptions AS \"%s\",\n"
" d.description AS \"%s\"",
! gettext_noop("Options"),
gettext_noop("Description"));
appendPQExpBuffer(&buf,
--- 3905,3911 ----
appendPQExpBuffer(&buf,
",\n ft.ftoptions AS \"%s\",\n"
" d.description AS \"%s\"",
! gettext_noop("FDW Options"),
gettext_noop("Description"));
appendPQExpBuffer(&buf,
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 5c48d83..4b60e8c 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** DROP FOREIGN DATA WRAPPER foo;
*** 53,59 ****
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
dummy | foreign_data_user | - | - | | | useless
foo | foreign_data_user | - | - | | {testing=1} |
--- 53,59 ----
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} |
*************** ERROR: option "testing" provided more t
*** 66,72 ****
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
------------+-------------------+---------+--------------------------+-------------------+-----------------------+-------------
dummy | foreign_data_user | - | - | | | useless
foo | foreign_data_user | - | - | | {testing=1,another=2} |
--- 66,72 ----
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} |
*************** HINT: Must be superuser to create a for
*** 81,92 ****
RESET ROLE;
CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | postgresql_fdw_validator | | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- ALTER FOREIGN DATA WRAPPER
--- 81,92 ----
RESET ROLE;
CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | postgresql_fdw_validator | | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- ALTER FOREIGN DATA WRAPPER
*************** ALTER FOREIGN DATA WRAPPER foo VALIDATOR
*** 98,109 ****
ERROR: function bar(text[], oid) does not exist
ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
--- 98,109 ----
ERROR: function bar(text[], oid) does not exist
ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
*************** ALTER FOREIGN DATA WRAPPER foo OPTIONS (
*** 113,134 ****
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 | 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 | 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');
--- 113,134 ----
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 (
*** 136,142 ****
ERROR: option "b" provided more than once
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
------------+-------------------+---------+--------------------------+-------------------+---------------+-------------
dummy | foreign_data_user | - | - | | | useless
foo | foreign_data_user | - | - | | {b=3,c=4,a=2} |
--- 136,142 ----
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} |
*************** SET ROLE regress_test_role_super;
*** 151,157 ****
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
------------+-------------------+---------+--------------------------+-------------------+-------------------+-------------
dummy | foreign_data_user | - | - | | | useless
foo | foreign_data_user | - | - | | {b=3,c=4,a=2,d=5} |
--- 151,157 ----
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} |
*************** HINT: Must be superuser to alter a fore
*** 170,176 ****
RESET ROLE;
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
dummy | foreign_data_user | - | - | | | useless
foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
--- 170,176 ----
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} |
*************** DROP FOREIGN DATA WRAPPER IF EXISTS none
*** 184,190 ****
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
dummy | foreign_data_user | - | - | | | useless
foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
--- 184,190 ----
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} |
*************** ALTER ROLE regress_test_role_super SUPER
*** 203,213 ****
DROP FOREIGN DATA WRAPPER foo;
DROP ROLE regress_test_role_super;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------+-------------
! dummy | foreign_data_user | - | - | | | useless
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(2 rows)
CREATE FOREIGN DATA WRAPPER foo;
--- 203,213 ----
DROP FOREIGN DATA WRAPPER foo;
DROP ROLE regress_test_role_super;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(2 rows)
CREATE FOREIGN DATA WRAPPER foo;
*************** CREATE SERVER s1 FOREIGN DATA WRAPPER fo
*** 215,239 ****
COMMENT ON SERVER s1 IS 'foreign server';
CREATE USER MAPPING FOR current_user SERVER s1;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options | Description
! ------+-------------------+----------------------+-------------------+------+---------+---------+----------------
! s1 | foreign_data_user | foo | | | | | foreign server
(1 row)
\deu+
! List of user mappings
! Server | User name | Options
! --------+-------------------+---------
s1 | foreign_data_user |
(1 row)
--- 215,239 ----
COMMENT ON SERVER s1 IS 'foreign server';
CREATE USER MAPPING FOR current_user SERVER s1;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+------+---------+-------------+----------------
! s1 | foreign_data_user | foo | | | | | foreign server
(1 row)
\deu+
! List of user mappings
! Server | User name | FDW Options
! --------+-------------------+-------------
s1 | foreign_data_user |
(1 row)
*************** NOTICE: drop cascades to 2 other object
*** 252,274 ****
DETAIL: drop cascades to server s1
drop cascades to user mapping for foreign_data_user
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------+-------------
! dummy | foreign_data_user | - | - | | | useless
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(2 rows)
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options | Description
! ------+-------+----------------------+-------------------+------+---------+---------+-------------
(0 rows)
\deu+
! List of user mappings
! Server | User name | Options
! --------+-----------+---------
(0 rows)
-- exercise CREATE SERVER
--- 252,274 ----
DETAIL: drop cascades to server s1
drop cascades to user mapping for foreign_data_user
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(2 rows)
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------+----------------------+-------------------+------+---------+-------------+-------------
(0 rows)
\deu+
! List of user mappings
! Server | User name | FDW Options
! --------+-----------+-------------
(0 rows)
-- exercise CREATE SERVER
*************** HINT: Valid options in this context are
*** 290,296 ****
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 | Options | Description
------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
s1 | foreign_data_user | foo | | | | |
s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
--- 290,296 ----
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} |
*************** CREATE SERVER t1 FOREIGN DATA WRAPPER fo
*** 312,318 ****
RESET ROLE;
\des+
List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options | Description
------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
s1 | foreign_data_user | foo | | | | |
s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
--- 312,318 ----
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} |
*************** SET ROLE regress_test_role;
*** 336,342 ****
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options | Description
------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
s1 | foreign_data_user | foo | | | | |
s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
--- 336,342 ----
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} |
*************** GRANT USAGE ON FOREIGN SERVER s1 TO regr
*** 366,372 ****
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 | 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 | | | |
--- 366,372 ----
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 | | | |
*************** DETAIL: owner of server s1
*** 417,423 ****
privileges for foreign-data wrapper foo
\des+
List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | 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 | | | |
--- 417,423 ----
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 | | | |
*************** ALTER USER MAPPING FOR public SERVER t1
*** 585,591 ****
RESET ROLE;
\deu+
List of user mappings
! Server | User name | Options
--------+-------------------+-----------------------------
s4 | foreign_data_user |
s4 | public | {"mapping=is public"}
--- 585,591 ----
RESET ROLE;
\deu+
List of user mappings
! Server | User name | FDW Options
--------+-------------------+-----------------------------
s4 | foreign_data_user |
s4 | public | {"mapping=is public"}
*************** COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
*** 654,671 ****
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
! Options: {"delimiter=,","quote=\""}
Has OIDs: no
\det+
List of foreign tables
! Schema | Table | Server | Options | Description
--------+-------+--------+----------------------------+-------------
public | ft1 | sc | {"delimiter=,","quote=\""} | ft1
(1 row)
--- 654,671 ----
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)
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c7
*** 706,712 ****
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 |
--- 706,712 ----
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 |
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8
*** 718,724 ****
c9 | integer | | | plain |
c10 | integer | | {p1=v1} | plain |
Server: sc
! Options: {"delimiter=,","quote=\""}
Has OIDs: no
-- can't change the column type if it's used elsewhere
--- 718,724 ----
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
*************** ALTER FOREIGN TABLE foreign_schema.ft1 R
*** 750,756 ****
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}
--- 750,756 ----
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}
*************** ALTER FOREIGN TABLE foreign_schema.ft1 R
*** 761,767 ****
c8 | text | | {p2=V2}
c10 | integer | | {p1=v1}
Server: sc
! Options: {quote=~,escape=@}
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
--- 761,767 ----
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: psql document fix about showing FDW options
2011/8/9 Shigeru Hanada <shigeru.hanada@gmail.com>:
postgres=# \d pgbench_accounts
Foreign table "public.pgbench_accounts"
Column | Type | Modifiers | Options
----------+---------------+-----------+---------------
aid | integer | not null | {colname=aid}
bid | integer | |
abalance | integer | |
filler | character(84) | |
Server: pgbench
Options: {nspname=public,"relname=foo bar"}
Looks like you've got postgresql_fdw working there... do you plan to
submit that for 9.2 soon? Any plans for qual pushdown?
I attached WIP patch for these fixes.
Please apply show_per_table_options.patch first against head, and then
rename_desc_headers.patch can be applied.
Looks good to me. Committed.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: psql document fix about showing FDW options
(2011/08/12 0:48), Robert Haas wrote:
2011/8/9 Shigeru Hanada<shigeru.hanada@gmail.com>:
postgres=# \d pgbench_accounts
Foreign table "public.pgbench_accounts"
Column | Type | Modifiers | Options
----------+---------------+-----------+---------------
aid | integer | not null | {colname=aid}
bid | integer | |
abalance | integer | |
filler | character(84) | |
Server: pgbench
Options: {nspname=public,"relname=foo bar"}Looks like you've got postgresql_fdw working there... do you plan to
submit that for 9.2 soon? Any plans for qual pushdown?
Yeah, I have (hopefully) working FDW for PostgreSQL which is based on
the one which has been proposed for 9.1, and updates done by Heikki.
I've implemented:
* SELECT clause omitting
* WHERE clause pushdown (assuming remote has same functions/oprators)
* private connection caching, and VIEW which shows active connections
* switch simple SELECT and CURSOR based on estimated # of rows,
and FDW options to control this behavior
I'd like to submit this fdw in CF 2011-09 (ASAP) for discussion of
itself and FDW API enhancement. Please see the wiki page below for details.
http://wiki.postgresql.org/wiki/SQL/MED#PostgreSQL
I attached WIP patch for these fixes.
Please apply show_per_table_options.patch first against head, and then
rename_desc_headers.patch can be applied.Looks good to me. Committed.
Thanks!
Regards,
--
Shigeru Hanada
Re: psql document fix about showing FDW options
2011/8/11 Shigeru Hanada <shigeru.hanada@gmail.com>:
Yeah, I have (hopefully) working FDW for PostgreSQL which is based on
the one which has been proposed for 9.1, and updates done by Heikki.
I've implemented:* SELECT clause omitting
* WHERE clause pushdown (assuming remote has same functions/oprators)
* private connection caching, and VIEW which shows active connections
* switch simple SELECT and CURSOR based on estimated # of rows,
and FDW options to control this behaviorI'd like to submit this fdw in CF 2011-09 (ASAP) for discussion of
itself and FDW API enhancement. Please see the wiki page below for details.
Great stuff. Look forward to seeing the patch(es).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company