Re: inherit support for foreign tables - Mailing list pgsql-hackers

On Thu, Nov 14, 2013 at 12:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 2) Allow foreign tables to have CHECK constraints
>> Like NOT NULL, I think we don't need to enforce the check duroing
>> INSERT/UPDATE against foreign table.
>
> Really?  It's one thing to say that somebody who adds a CHECK constraint
> to a foreign table is responsible to make sure that the foreign data will
> satisfy the constraint.  It feels like a different thing to say that ALTER
> TABLE ADD CONSTRAINT applied to a parent table will silently assume that
> some child table that happens to be foreign doesn't need any enforcement.
>
> Perhaps more to the point, inheritance trees are the main place where the
> planner depends on the assumption that CHECK constraints represent
> reality.  Are we really prepared to say that it's the user's fault if the
> planner generates an incorrect plan on the strength of a CHECK constraint
> that's not actually satisfied by the foreign data?  If so, that had better
> be documented by this patch.  But for a project that refuses to let people
> create a local CHECK or FOREIGN KEY constraint without mechanically
> checking it, it seems pretty darn weird to be so laissez-faire about
> constraints on foreign data.

I can see both sides of this issue.  We certainly have no way to force
the remote side to enforce CHECK constraints defined on the local
side, because the remote side could also be accepting writes from
other sources that don't have any matching constraint. But having said
that, I can't see any particularly principled reason why we shouldn't
at least check the new rows we insert ourselves.  After all, we could
be in the situation proposed by KaiGai Kohei, where the foreign data
wrapper API is being used as a surrogate storage engine API - i.e.
there are no writers to the foreign side except ourselves.  In that
situation, it would seem odd to randomly fail to enforce the
constraints.

On the other hand, the performance costs of checking every row bound
for the remote table could be quite steep.  Consider an update on an
inheritance hierarchy that sets a = a + 1 for every row.  If we don't
worry about verifying that the resulting rows satisfy all local-side
constraints, we can potentially ship a single update statement to the
remote server and let it do all the work there.  But if we DO have to
worry about that, then we're going to have to ship every updated row
over the wire in at least one direction, if not both.  If the purpose
of adding CHECK constraints was to enable constraint exclusion, that's
a mighty steep price to pay for it.

I think it's been previously proposed that we have some version of a
CHECK constraint that effectively acts as an assertion for query
optimization purposes, but isn't actually enforced by the system.  I
can see that being useful in a variety of situations, including this
one.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company