Re: [HACKERS] NOT NULL constraints in foreign tables
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
Re: [HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NOT NULL constraints in foreign tables
On Mon, Aug 20, 2012 at 5:14 PM, Jeff Davis pg...@j-davis.com wrote: On Mon, 2012-08-20 at 16:50 -0400, Robert Haas wrote: #3 for foreign tables. I'm skeptical of that approach for two reasons: (1) It will be hard to inform users which constraints are enforced and which aren't. 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. The very existence of the table, the names of its columns, the types of those columns, and any additional constraints on that columns are all things that can vary between the declaration and what's actually present, and can be subsequently changed on the remote side at any time. Why should CHECK constraints be any different than, say, column types? (2) It will be hard for users to understand the planner benefits or the consequences when the constraint is not enforced. 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. That being said, I can imagine good use cases (like when the foreign table is in postgres, and already has that constraint declared), so I'm not outright opposed to it. #1 is not a reasonable alternative for foreign tables because we lack enforcement power in that case, Right. 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. Declared constraints can improve the plans, while runtime-enforced constraints slow down execution of a given plan. I'm not really sure whether runtime enforcement is a good trade-off, but it doesn't seem like an obviously bad one. It seems to me that the term runtime enforcement is a misnomer; you can't really enforce anything about a foreign table. You can throw an error if the data doesn't meet expectations, but by that time the cat is already out of the bag. The purpose of a CHECK constraint on a plain table is to prevent bad data from going into a table, not to throw an error when retrieving previously-inserted bad data. If we were to propose changing the semantics from the former to the latter, we'd be laughed out of town, and rightly so. Also, what did you mean by the only point of allowing declarative constraints is to get better performance? Maybe the user wants to get an error if some important assumption about the remote data source is not as true as when they declared the constraint. I think that need is going to be best served by issuing a query to validate whatever constraint you're interested in, i.e. SELECT * FROM foreign_table WHERE NOT (whatever_i_am_assuming). I mean, let's suppose that we were to allow unique constraints on foreign tables. This is clearly useful, because, for example, it would allow join removal in a case like local_table LEFT JOIN foreign_table ON local_table.id = foreign_table.id; and it would also allow a selectivity estimate of -1 for that column. But are you going to validate that the column in question is still unique every time you access the table? Presumably not; you'd have to read and sort the entire table to do that. 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 the query is SELECT max(some_other_column) FROM foreign_table, you can't really validate anything at all, or at least not without a lot of extra overhead. Now, it's more feasible to think that you could validate a CHECK constraint on each row that the query actually touches, but that's still going to be pretty expensive, and it's still not the same thing as validating that the assumption holds true for all rows in the table. 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. We're definitely not going to be able to check everything all the time, so checking nothing ever seems like the principled alternative. Also, for tightly-coupled databases under common control, there's really no need to be constantly checking and rechecking that the other guy hasn't pulled the rug out from under you. We certainly need to have that as an option. For loosely-coupled databases under different administrative control there might be some point, but it seems sort of hopeless: if the other DBA doesn't care about breaking your stuff,
Re: [HACKERS] NOT NULL constraints in foreign tables
On Fri, Aug 17, 2012 at 4:08 PM, Alvaro Herrera alvhe...@2ndquadrant.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 alvhe...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NOT NULL constraints in foreign tables
On Fri, 2012-08-17 at 15:44 -0400, Robert Haas wrote: On Fri, Aug 17, 2012 at 2:58 PM, Alvaro Herrera alvhe...@2ndquadrant.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? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NOT NULL constraints in foreign tables
On Mon, Aug 20, 2012 at 3:56 PM, Jeff Davis pg...@j-davis.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 alvhe...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NOT NULL constraints in foreign tables
On Mon, 2012-08-20 at 16:50 -0400, Robert Haas wrote: #3 for foreign tables. I'm skeptical of that approach for two reasons: (1) It will be hard to inform users which constraints are enforced and which aren't. (2) It will be hard for users to understand the planner benefits or the consequences when the constraint is not enforced. That being said, I can imagine good use cases (like when the foreign table is in postgres, and already has that constraint declared), so I'm not outright opposed to it. #1 is not a reasonable alternative for foreign tables because we lack enforcement power in that case, Right. 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. Declared constraints can improve the plans, while runtime-enforced constraints slow down execution of a given plan. I'm not really sure whether runtime enforcement is a good trade-off, but it doesn't seem like an obviously bad one. Also, what did you mean by the only point of allowing declarative constraints is to get better performance? Maybe the user wants to get an error if some important assumption about the remote data source is not as true as when they declared the constraint. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NOT NULL constraints in foreign tables
Alvaro Herrera alvhe...@2ndquadrant.com writes: I mean, what are NOT NULL in foreign tables for? There was previous discussion about that, in the context of check constraints in general, but I don't believe we reached consensus. http://archives.postgresql.org/message-id/1038.1331738...@sss.pgh.pa.us There's also an open commitfest entry for changing the behavior of file_fdw, which IMO is completely premature since we haven't got consensus on what it should do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NOT NULL constraints in foreign tables
On Fri, Aug 17, 2012 at 2:58 PM, Alvaro Herrera alvhe...@2ndquadrant.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. -- 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
Re: [HACKERS] NOT NULL constraints in foreign tables
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 alvhe...@2ndquadrant.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? 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? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NOT NULL constraints in foreign tables
On 17-08-2012 16:44, Robert Haas wrote: On Fri, Aug 17, 2012 at 2:58 PM, Alvaro Herrera alvhe...@2ndquadrant.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. +1. I don't see us providing a mechanism to cross-check changes between data sources. Even if we do it for creation time, schema could be changed behind the scenes. Let's use at least constraints (NOT NULL, CHECK, UNIQUE, PK -- UNIQUE + NOT NULL) to improve optimizer but warn (loudly) that those constraints are merely for optimization. -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
