4 ms·

Wow, that is awesome and is something I've been worrying about recently. However, there is a second part that is still missing that would allow for actual post

Wow, that is awesome and is something I've been worrying about recently. However, there is a second part that is still missing that would allow for actual postgresql application development. There needs to be a way to check all constraints and report all failures, rather than just stopping at the first error. Without that, users submit data, get an error for one field, fix it and resubmit, get an error for the next field, etc, etc. Reporting all the errors the first time is really important for usability. So right now you have to duplicate all the database constraints in your app, which is a huge source of code duplication (and thus bugs).

jeffdavis 13y ago
"check all constraints and report all failures" Interesting point. Foreign key failures are usually the result of a program bug. Unique and exclusion[1] constraints depend on concurrent actions, and can't be caught ahead of time as easily. So I assume you are mostly talking about CHECK constraints. Also, we're talking about a single statement, which probably means a single table. It does sound like a good idea to validate all of the check constraints on a table at once, and report all of the failures. [1] http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION http://www.postgresql.org/docs/current/static/ddl-constraint...
- papsosouid 13y ago
  Yes and no. Yes in that I am talking specifically about check constraints when I say that we have to duplicate them all in our apps. You can't really duplicate the others. But no in that I don't mean to validate only check constraints and report all failures, ideally it would do unique and FK too. If there's an underlying reason that isn't feasible, then doing all the check constraints at once would certainly be a big improvement though. We're already in the position of having data validation type checks done separately from uniques now, so that would get users of our apps the current behaviour while letting us delete a bunch of code.
  - plq 13y ago
    FWIW, we try very hard to not write any check constraints and do any such validation at the protocol level, before the request even hits the authn/authz layer. (Spyne makes it easy ! http://spyne.io http://spyne.io)
    - papsosouid 13y ago
      >FWIW, we try very hard to not write any check constraints I like check constraints. I would never consider removing them under any circumstances, I've dealt with far too many databases full of inconsistent data. I'd just rather not have to duplicate them (or triplicate, etc) in each of my apps that accesses the database.

jeffdavis 13y ago

"check all constraints and report all failures" Interesting point. Foreign key failures are usually the result of a program bug. Unique and exclusion[1] constraints depend on concurrent actions, and can't be caught ahead of time as easily. So I assume you are mostly talking about CHECK constraints. Also, we're talking about a single statement, which probably means a single table. It does sound like a good idea to validate all of the check constraints on a table at once, and report all of the failures. [1] http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION http://www.postgresql.org/docs/current/static/ddl-constraint...

papsosouid 13y ago
Yes and no. Yes in that I am talking specifically about check constraints when I say that we have to duplicate them all in our apps. You can't really duplicate the others. But no in that I don't mean to validate only check constraints and report all failures, ideally it would do unique and FK too. If there's an underlying reason that isn't feasible, then doing all the check constraints at once would certainly be a big improvement though. We're already in the position of having data validation type checks done separately from uniques now, so that would get users of our apps the current behaviour while letting us delete a bunch of code.
- plq 13y ago
  FWIW, we try very hard to not write any check constraints and do any such validation at the protocol level, before the request even hits the authn/authz layer. (Spyne makes it easy ! http://spyne.io http://spyne.io)
  - papsosouid 13y ago
    >FWIW, we try very hard to not write any check constraints I like check constraints. I would never consider removing them under any circumstances, I've dealt with far too many databases full of inconsistent data. I'd just rather not have to duplicate them (or triplicate, etc) in each of my apps that accesses the database.