per-column generic option - Mailing list pgsql-hackers

Hi,

I would like to propose support for per-column generic option, which is
defined in the SQL/MED standard.  In 9.0 release, support for foreign
tables and per-table generic option have been added, but support for
per-column generic option hasn't.

Please examine the description below and attached patch
per_column_option_v1.patch.  Any comments or questions are welcome.

Possible use cases
~~~~~~~~~~~~~~~~~~
Purpose of per-column generic option is passing column-specific settings
to the foreign-data wrapper.

1) For file_fdw, per-column generic option can be used to represent
per-column COPY option FORCE_NOT_NULL with boolean value (currently
file_fdw doesn't support FORCE_NOT_NULL option).

2) For postgresql_fdw (even though it has not been implemented yet),
per-column generic option could be used to represent the name of the
column on the foreign side.  It is similar to per-table generic option
such as "nspname" and "relname" for namespace name/relation name,
proposed in the last development cycles. Such option would be named
"attname" after pg_attribute.attname.

Catalog design
~~~~~~~~~~~~~~
This proposal requires changing some catalogs.

1) To store per-column generic options, new attribute attfdwoptions
(text[]) was added at tail of pg_attribute.  This is similar to the
generic option of other FDW objects such as FDW, server, user mapping
and foreign table.  Existing attribute attoptions is not used for
generic options.

2) To conform the SQL/MED standard, an information_schema view
COLUMN_OPTIONS was added.  Also underlying view
_pg_foreign_table_columns was added to show only columns which current
user has any access privilege.  This fashion is same as other FDW views.

Syntax design
~~~~~~~~~~~~~
Per-column generic options can be operated via CREATE FOREIGN TABLE
statement and ALTER FOREIGN TABLE statement.  Similar to other generic
options, ADD/SET/DROP can be specified for ALTER FOREIGN TABLE.

1) In CREATE FOREIGN TABLE statement, per-column generic options can be
specified in a column definition without operation qualifier such as
SET, ADD and DROP; all options are treated as ADD.  Similar to other FDW
objects, multiple options can be specified for one column by separating
option-value pairs with comma.

    -- multiple options can be specified for one column at once
    CREATE FOREIGN TABLE foo (
       c1 int  OPTIONS (opt1 'value1'),
       c2 text OPTIONS (opt2 'values2', opt3 'value3'),
       c3 date OPTIONS (opt4 'value4) NOT NULL
    ) SERVER server;

To avoid syntax conflict between "OPTIONS (...)" and "DEFAULT b_expr"
(b_expr can end with a token "OPTION"), I placed OPTIONS (...) between
data type and any other column qualifier such as default values and
constraints.

The SQL/MED standard doesn't consider any column qualifier other than
data type, so it defines the syntax simply as below.  I think new syntax
conforms the standard...

    CREATE FOREIGN TABLE foo (
      { column_name data_type [ OPTIONS ( option 'value' [, ...] ) ] }
        [, ... ]
    ) SERVER server [ OPTIONS (...) ]

Please note that CREATE FOREIGN TABLE shares the columnDef, a syntax
element for a column definition, with CREATE TABLE.  I thought that they
should so, and I didn't introduce separated syntax for foreign tables.

2) Similar to other FDW objects' ALTER statement, ALTER FOREIGN TABLE
ALTER COLUMN accepts ADD/SET/DROP operation for each option.  DROP
requires only option name.

    ALTER FOREIGN TABLE foo ALTER COLUMN c1
        OPTIONS (SET opt1 'VALUE1');    -- should be set in advance
    ALTER FOREIGN TABLE foo ALTER COLUMN c1
        OPTIONS (ADD opt2 'VALUE1', DROP opt1);

Similar to other ALTER FOREIGN TABLE commands, ALTER COLUMN ... OPTIONS
(...) can be contained in the list of ALTER commands.

    ALTER FOREIGN TABLE foo
        ALTER COLUMN col1 OPTIONS (opt1 'val1'),
        ALTER COLUMN col2 SET NOT NULL;

psql support
~~~~~~~~~~~~
1) psql should support describing per-column generic options, so \dec
command was added.  If the form \dec+ is used, generic options are also
displayed.  Output sample is:

postgres=# \dec csv_branches
  List of foreign table columns
 Schema |    Table     |  Column
--------+--------------+----------
 public | csv_branches | bid
 public | csv_branches | bbalance
 public | csv_branches | filler
(3 rows)

postgres=# \dec+ csv_branches
               List of foreign table columns
 Schema |    Table     |  Column  |        Options
--------+--------------+----------+------------------------
 public | csv_branches | bid      | {force_not_null=false}
 public | csv_branches | bbalance | {force_not_null=true}
 public | csv_branches | filler   |
(3 rows)

Here I found an inconsistency about privilege to see generic options
(not only column but also FDW and server et al).  The
information_schema.*_options only shows options which are associated to
objects that current user can access, but \de*+ doesn't have such
restriction.  \de* commands should be fixed to hide forbidden objects?

2) psql can support tab-completion CREATE/ALTER FOREIGN TABLE statement
about OPTIONS, but the patch doesn't include this feature.

pg_dump support
~~~~~~~~~~~~~~~
Sorry, I overlooked this issue till writing this post...  I'm going to
work on this and post revised patch soon.  Please examine other parts first.

Documents
~~~~~~~~~
1) Is "generic options" proper term to mean FDW-specific option
associated to a FDW object?  It's used in the SQL/MED standard, but
seems not popular...  "FDW option" would be better than "generic option"?

Regards,
--
Shigeru Hanada