On Wed, Aug 22, 2012 at 12:59 PM, Jeff Davis <davis.jeff...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
