Re: NOT NULL constraints in foreign tables

On Fri, Aug 17, 2012 at 4:08 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Excerpts from Robert Haas's message of vie ago 17 15:44:29 -0400 2012:
>> 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.
>
> So pg_constraint rows for NOT NULLs are a good thing, right?

Well, they aren't a bad thing, anyway. The query optimizer looks at
attisnull directly in the one case where this really matters (which
has to do with reordering left joins IIRC). Allowing all check
constraints would certainly be a step forward, as it would allow
constraint exclusion.

> In general, it seems to me that you're saying we should just lift the
> DefineRelation-enforced restriction that foreign tables ought not to
> have constraints. So if the user wants to specify
>
> CREATE FOREIGN TABLE people (
> who person CHECK (who IS OF TYPE 'human'),
> ..
> ) server foobar;
>
> we ought to let them. Correct?

Yeah, that's my view. Note that I excluded this from the initial
syntax commit of foreign tables out of some feeling that there were
some loose ends that weren't adequately handled by the original patch,
which did allow them. I no longer remember what the deficiencies
were, unfortunately. Obviously, at a minimum, we need to make sure
that they are dumped-and-restored properly, displayed by psql
properly, and documented. But in general +1 for allowing this.

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