Hi hackers,
At first I've posted to pgsql-docs but there is no reply at present.
http://archives.postgresql.org/pgsql-docs/2011-06/msg00006.php
So I post revised patch to pgsql-hackers, because the patch is for
documents under development for 9.1 beta2. Please let me know if this
was wrong list to discuss these issues.
Attached patch includes fixes for FOREIGN TABLE documents:
1) "NOT NULL" should be added to syntax of ALTER FOREIGN TABLE ADD
COLUMN command.
2) Mentions about unsupported features should be removed from ALTER
FOREIGN TABLE document. They had been proposed but haven't been
committed for 9.1. These unsupported features are not mentioned in
CREATE FOREIGN TABLE document.
- table inheritance
- oid system column
- CHECK constraint
- per-column FDW options
3) It would be useful to mention differences between ordinary tables and
foreign tables in CREATE FOREIGN TABLE document.
- NOT NULL constraint is just for optimization, at least at present
- serial/bigserial can't be used because they need default value
The description in the patch are based on the advice which were posted
by Thom Brown and Robert Haas in the thread:
http://archives.postgresql.org/pgsql-hackers/2011-03/msg01949.php
Regards,
--
Shigeru Hanada
diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml
b/doc/src/sgml/ref/alter_foreign_table.sgml
index c2ebdac..af7d5fa 100644
*** a/doc/src/sgml/ref/alter_foreign_table.sgml
--- b/doc/src/sgml/ref/alter_foreign_table.sgml
*************** ALTER FOREIGN TABLE <replaceable class="
*** 32,38 ****
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one
of:</phrase>
! ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable>
<replaceable class="PARAMETER">type</replaceable>
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
--- 32,38 ----
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one
of:</phrase>
! ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable>
<replaceable class="PARAMETER">type</replaceable> [ NULL | NOT NULL ]
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 FOREIGN TABLE <replaceable class="
*** 125,131 ****
<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
--- 125,131 ----
<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
*************** ALTER FOREIGN TABLE <replaceable class="
*** 150,157 ****
You must own the table to use <command>ALTER FOREIGN TABLE</>.
To change the schema of a foreign table, you must also have
<literal>CREATE</literal> privilege on the new schema.
- To add the table as a new child of a parent table, you must own the
- parent table as well.
To alter the owner, you must also be a direct or indirect member of the new
owning role, and that role must have <literal>CREATE</literal> privilege on
the table's schema. (These restrictions enforce that altering the owner
--- 150,155 ----
*************** ALTER FOREIGN TABLE <replaceable class="
*** 260,269 ****
</para>
<para>
! Consistency with the foreign server is not checked when a column is
! added or removed with <literal>ADD COLUMN</literal> or
! <literal>DROP COLUMN</literal>, a system <literal>oid</> column is added
! or removed, a <literal>CHECK</> or <literal>NOT NULL</> constraint is
added, or column type is changed with <literal>SET DATA TYPE</>. It is
the
user's responsibility to ensure that the table definition matches the
remote side.
--- 258,266 ----
</para>
<para>
! Consistency with the foreign server is not checked when a column is added
! or removed with <literal>ADD COLUMN</literal> or
! <literal>DROP COLUMN</literal>, <literal>NOT NULL</> constraint is
added, or column type is changed with <literal>SET DATA TYPE</>. It is
the
user's responsibility to ensure that the table definition matches the
remote side.
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml
b/doc/src/sgml/ref/create_foreign_table.sgml
index ad91072..50d8628 100644
*** a/doc/src/sgml/ref/create_foreign_table.sgml
--- b/doc/src/sgml/ref/create_foreign_table.sgml
*************** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <
*** 94,100 ****
<listitem>
<para>
The data type of the column. This can include array
! specifiers. For more information on the data types supported by
<productname>PostgreSQL</productname>, refer to <xref
linkend="datatype">.
</para>
--- 94,106 ----
<listitem>
<para>
The data type of the column. This can include array
! specifiers.
! Unlike an ordinary table, default values cannot be used on a foreign
! tables. Serial and bigserial implicitly set default value on the column,
! therefore they cannot be used on a foreign table.
! </para>
! <para>
! For more information on the data types supported by
<productname>PostgreSQL</productname>, refer to <xref
linkend="datatype">.
</para>
*************** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <
*** 107,112 ****
--- 113,130 ----
<para>
The column is not allowed to contain null values.
</para>
+ <para>
+ Note that defining <literal>NOT NULL</> constraint on a column doesn't
+ guarantee that the data on the foreign side don't include any null
value.
+ This means that query results might include null values even if the
+ column was defined as <literal>NOT NULL</>. To ensure the integrity of
+ query result, FDW needs to throw an error when invalid data was returned
+ from the foreign server.
+ </para>
+ <para>
+ At present, <literal>NOT NULL</> constraint is used only for query plan
+ optimization.
+ </para>
</listitem>
</varlistentry>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
