David Fetter <david@fetter.org> writes:
> On Wed, Mar 14, 2012 at 10:27:28AM -0400, Tom Lane wrote:
>> Hm. That opinion seems to me to connect to the recently-posted
>> patch to make contrib/file_fdw enforce NOT NULL constraints. Should
>> we instead have the position that constraints declared for foreign
>> tables are statements that we can take on faith, and it's the user's
>> fault if they are wrong?
> I think that's something FDWs need to be able to communicate to
> PostgreSQL. For example, something talking to another PostgreSQL
> would (potentially, anyhow) have access to deep knowledge of the
> remote side, but file_fdw would only have best efforts even for clever
> things like statistics.
I think we're talking at cross-purposes. What you're saying seems to
assume that it's the system's responsibility to do something about a
constraint declared on a foreign table. What I'm suggesting is that
maybe it isn't. A constraint, ordinarily, would be enforced against
table *updates*, and then just assumed valid during reads. In the case
of a foreign table, we can't enforce constraints during updates because
we don't have control of all updates. Should we ignore declared
constraints because they're not necessarily true? Should we assume on
faith that they're true? The posted patch for file_fdw takes the
approach of silently filtering out rows for which they're not true,
which is not obviously the right thing either --- quite aside from
whether that's a sane semantics, it's not going to scale to foreign key
constraints, and even for simple NOT NULL and CHECK constraints it
results in a runtime penalty on selects, which is not what people would
expect from a constraint.
regards, tom lane
