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