Home Explore Blog CI



postgresql

7th chunk of `doc/src/sgml/ref/create_foreign_table.sgml`
b86a2b67bbefc45d41fac528ee9edde3f8fdc783edbb318900000001000009ab
 and most foreign data wrappers
    do not attempt to enforce them either; that is, the constraint is
    simply assumed to hold true.  There would be little point in such
    enforcement since it would only apply to rows inserted or updated via
    the foreign table, and not to rows modified by other means, such as
    directly on the remote server.  Instead, a constraint attached to a
    foreign table should represent a constraint that is being enforced by
    the remote server.
   </para>

   <para>
    Some special-purpose foreign data wrappers might be the only access
    mechanism for the data they access, and in that case it might be
    appropriate for the foreign data wrapper itself to perform constraint
    enforcement.  But you should not assume that a wrapper does that
    unless its documentation says so.
   </para>

   <para>
    Although <productname>PostgreSQL</productname> does not attempt to enforce
    constraints on foreign tables, it does assume that they are correct
    for purposes of query optimization.  If there are rows visible in the
    foreign table that do not satisfy a declared constraint, queries on
    the table might produce errors or incorrect answers.  It is the user's
    responsibility to ensure that the constraint definition matches
    reality.
   </para>

   <caution>
    <para>
     When a foreign table is used as a partition of a partitioned table,
     there is an implicit constraint that its contents must satisfy the
     partitioning rule.  Again, it is the user's responsibility to ensure
     that that is true, which is best done by installing a matching
     constraint on the remote server.
    </para>
   </caution>

   <para>
    Within a partitioned table containing foreign-table partitions,
    an <command>UPDATE</command> that changes the partition key value can
    cause a row to be moved from a local partition to a foreign-table
    partition, provided the foreign data wrapper supports tuple routing.
    However, it is not currently possible to move a row from a
    foreign-table partition to another partition.
    An <command>UPDATE</command> that would require doing that will fail
    due to the partitioning constraint, assuming that that is properly
    enforced by the remote server.
   </para>

   <para>
    Similar considerations apply to generated columns.  Stored generated
    columns are computed on insert or update on the local
    <productname>PostgreSQL</productname>

Title: Foreign Table Constraints and Partitioning Considerations
Summary
PostgreSQL assumes constraints on foreign tables are correct for query optimization, even though it doesn't enforce them. The user is responsible for ensuring constraints match reality, and this is especially important when foreign tables are partitions of partitioned tables. Updates that change partition key values can move rows from local partitions to foreign-table partitions, but not the other way around. Similar considerations apply to stored generated columns which are computed locally.