Re: NOT NULL constraints in foreign tables

On Wed, Aug 22, 2012 at 12:59 PM, Jeff Davis <davis(dot)jeffrey(at)gmail(dot)com> wrote:
> On Tue, 2012-08-21 at 10:41 -0400, Robert Haas wrote:
>> The thing to keep in mind here is that EVERY property of a foreign
>> table is subject to change at any arbitrary point in time, without our
>> knowledge. ... Why should CHECK constraints be any different than,
>> say, column types?
>
> So, let's say someone changes column types from int to bigint on the
> remote side, and you still have int on the local side. It continues to
> work and everything is fine until all of a sudden you get 2^33 back, and
> that generates an error.
>
> That sounds closer to the semantics of constraint enforcement mechanism
> #2 than #3 to me. That is, everything is fine until you get something
> that you know is wrong, and you throw an error.

Sure, but in that case you're not paying anything extra for it.

>> Why should that be any worse with foreign tables than anything else?
>> I mean, lots of people, as things stand today, manage to set up
>> partitioned tables using CHECK constraints. There are undoubtedly
>> people who don't understand the planner benefit of having an
>> appropriate CHECK constraint on each partition, but it's not exactly a
>> common cause of confusion.
>
> But there are no consequences there other than performance. With
> unenforced constraints, they may get correct results during development
> and testing, and wrong results occasionally when in production. That's
> hard to explain to a user.

Sure. Of course, your example of a column that is bigserial on one
side and an integer on the other side is a perfect example of how that
could happen *anyway*. I'm all in favor of building things in a way
that minimizes the possibility of user confusion. But since foreign
tables inevitably carry large amounts of risk in that area anyway, I
can't get very excited about fixing 10% of the problem. That seems
likely to create the perception of safety without the reality.

> And if you don't issue a query at all, the constraint might not still be
> true; but I don't think that implies that checking it when you do run a
> query is useless.

Well, it does to me, but your mileage may vary (and obviously does).

>> I think if we go down this road of trying to validate
>> remote-side CHECK constraints, we're going to end up with a mishmash
>> of cases where constraints are checked and other cases where
>> constraints are not checked, and then that really is going to be
>> confusing.
>
> If we use keywords to differentiate constraints that are different
> semantically, then we can just say that some types of constraints are
> allowed on foreign tables and some are not.
>
> I guess what I'd like to avoid is saying that a check constraint on a
> regular table means one thing, and the same check constraint on a
> foreign table means something else. If we differentiate them by
> requiring special keywords like "NOT ENFORCED", then it would be more
> user-visible what's going on, and it would allow room for new semantics
> later if we want. Normal constraints would be disallowed on foreign
> tables, but NOT ENFORCED ones would be allowed.

This, I could get behind.

> That brings up another point: what if someone really, really, doesn't
> want to pay the overhead of enforcing their constraint on a local table,
> but wants the planner benefit? Would they have to make it a remote table
> to bypass the constraint check?

This is also a good point.

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