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
somethinglike:
 

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@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net