Re: NOT NULL constraints in foreign tables

On Mon, Aug 20, 2012 at 3:56 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Fri, 2012-08-17 at 15:44 -0400, Robert Haas wrote:
>> On Fri, Aug 17, 2012 at 2:58 PM, Alvaro Herrera
>> <alvherre(at)2ndquadrant(dot)com> wrote:
>> > I mean, what are NOT NULL in foreign tables for? Are they harmed or
>> > helped by having pg_constraint rows?
>>
>> As I've mentioned when this has come up before, I think that
>> constraints on foreign tables should be viewed as declarative
>> statements about the contents of the foreign data that the DB will
>> assume true. This could be useful for a variety of purposes:
>> constraint exclusion, query optimization, etc.
>
> There are at least three kinds of constraint enforcement:
>
> 1. Enforced before the query runs (e.g. the current behavior on a normal
> table).
>
> 2. Enforced when the query runs by validating the constraint as you go,
> and then throwing an error when it turns out to be false.
>
> 3. Don't make any attempt to enforce, and silently produce wrong results
> if it's false.
>
> Which are you proposing, and how will the user know which kind of
> constraint they've got?

I'm proposing #1 for regular tables, as has always been the case, and
#3 for foreign tables. #1 is not a reasonable alternative for foreign
tables because we lack enforcement power in that case, and #2 is also
not reasonable, because the only point of allowing declarative
constraints is to get better performance, and if we go with #2 then
we've pretty much thrown that out the window.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company