On Thu, Nov 14, 2013 at 12:28 AM, Tom Lane <t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
