Re: inherit support for foreign tables

On 11/18/13, 8:36 AM, Robert Haas wrote:
> 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.

A sophisticated enough FDW could verify that the appropriate check already existed in tho foreign side, or it could do something like:

BEGIN;
UPDATE SET ... WHERE <where>
SELECT EXISTS( SELECT 1 WHERE <where> AND NOT (<check condition>) );

And then rollback if the SELECT returns true.

But obviously you can't always do that, so I think there's a place for both true constraints and "suggested constraints".
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net