Re: NOT NULL constraints in foreign tables

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.

> 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.

> It seems to me that the term runtime enforcement is a misnomer; you
> can't really "enforce" anything about a foreign table.

Maybe I chose the wrong terms, but there are at least 3 semantically
different concepts. Feel free to suggest a better term.

> If we
> were to propose changing the semantics from the former to the latter,
> we'd be laughed out of town, and rightly so.

I'm not proposing changing the semantics, I'm saying that there are more
than just 2 semantic options available, and they offer different kinds
of guarantees. Users may be interested in all 3 for different use cases.

> I mean, let's
> suppose that we were to allow unique constraints on foreign tables.

I'm sure there are cases where people will not want what I am
suggesting, but I think there are cases where it is plausibly useful.

> Now, if the query is something like "SELECT
> * FROM foreign_table WHERE id = 1", you could fairly cheaply validate
> that there is only one row with id = 1, but that's not the same thing
> as validating that the assumption (namely, that foreign_table (id) is
> unique) is still true.

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.

> 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.

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?

Regards,
Jeff Davis