4 ms·

Stored procedures may be all of those things, but they don't have to - it's just that most of the time, developers don't really care, so they have fancy version

by glogla 11y ago

Stored procedures may be all of those things, but they don't have to - it's just that most of the time, developers don't really care, so they have fancy versioning, deployment and continuous integration for all their code, except for stored procedures. Here is interesting talk about database migrations and stored procedures and unit tests: http://www.pgcon.org/2013/schedule/events/615.en.html http://www.pgcon.org/2013/schedule/events/615.en.html Also, DB procedures are not easy to "debug" in the traditional way, but SQL client is basically the first REPL every programmer becomes familiar with. You can easily step stored procedure by running it's commands one by one, unless it's fancy Oracle forall loop with cursor or something (and the cursor select can still be selected as normal). Also, databases tend to have more strong data types than programming languages in general so putting constraints in DB means, that bad data are not savable in the system.

batou 11y ago
Hmm no. Avoid state at all costs. Stored procedures are stateful. Schema and migrations is pain enough already. Write me a check constraint that validates an email address being put in a varchar column and reports back a sensible message which can be bound to an entry field with metadata about the error. Write me a constraint and key arrangement which is unique across two and three columns in separate groups. No. You're wrong.
- nunwuo 11y ago
  > Avoid state at all costs. Stored procedures are stateful. Schema and migrations is pain enough already. What do you mean by that? How is having a bunch of queries in a stored procedure more "stateful" than having the same queries in the application? > Write me a check constraint that validates an email address being put in a varchar column and reports back a sensible message which can be bound to an entry field with metadata about the error. Postgres gives you metadata about the error, though the error message will still be a generic "CHECK constraint violated" or some such. > Write me a constraint and key arrangement which is unique across two and three columns in separate groups. I'm not sure what you want to see based on that description, but surely you're not advocating enforcing unique constraints in the application?
  - batou 11y ago
    Stateful: If I have to load the stored procedure into the persistence engine then that step is required. This is no more stateful than queries in the application but it means that the relevant state in both the application and the database engine needs to be reloaded and constantly sychronised. Ergo, two times the work. CHECK constraint violated is no good for humans. Prevention is better than cure here. Why shouldn't I enforce unique constraints in the application? 1. Open a transaction 2. Get a user by name from the ORM. 3. Exists? Tell user that the username is already registered. 4. Doesn't exist? Save new User instance. 5. Commit transaction. Steps 2 and 3 can be as arbitrarily complicated as you need them to be, are fully testable and cheap with anything that uses MVCC.
    - dragonwriter 11y ago
      > CHECK constraint violated is no good for humans. Well, sure, an application should respond to DB errors by presenting appropriate messages on the UI, just like any other errors it encounters. You should only see "CHECK constraint violated" if you are bypassing the app and using the DB. Otherwise, you should see something nice provided by the app. > Why shouldn't I enforce unique constraints in the application? Because you should do it in the database whether or not you do it in the application, and then once you have, well, DRY.
    - rapala 11y ago
      Or you could do: 1. Insert new User instance 2. Unique constraint violation? Tell user that the username is already registered.
    - giaour 11y ago
      > Why shouldn't I enforce unique constraints in the application? This tightly couples your database to your application. You can no longer guarantee that your database is reliable when used otherwise.
    - brightball 11y ago
      "Why shouldn't I enforce unique constraints in the application?" You should to both. For all the reasons you mention, it's often cleaner to just do it in the application especially when you can use a framework with a simple "validate_uniqueness" flag. But, what you're describing is also the very definition of a race condition. It's the same reason you don't increment counters by retrieving them, adding 1 to it and then saving the number back to the database and instead pass in an increment command. Check it in the application but let the database make sure it doesn't get violated in a race condition. There's a significant amount of either/or in this entire conversation (not just you, the whole thread) when the database absolutely can and should be leveraged for certain things. It's extremism and purism where the problems get introduced (in both directions).
- dragonwriter 11y ago
  > Avoid state at all costs. Stored procedures are stateful. Stored procedures are no more state than application code is. > Write me a constraint and key arrangement which is unique across two and three columns in separate groups. What does "unique across two and three columns in separate groups" mean? I get that its something more complex than a simple multicolumn uniqueness constraint, but not what it is supposed to do. I suspect that whatever it is can be done with PostgreSQL -- possibly using the (relatively) new exclusion constraints -- but I can't quite be sure without more clarity on what you mean.
jeletonskelly 11y ago
> so they have fancy versioning, deployment and continuous integration for all their code, except for stored procedures. Exactly, because those things are extremely important to how code gets shipped and delivers value to the business. Stored procedures become a huge risk to future development which ultimately means it's a risk to the businesses ability to deliver value. What happens when you need to change DB vendors because the business has been so successful that you've outgrown a relational database? You have to rewrite the ENTIRE MC portion of your MVC application. Why would someone ever do this?

by glogla 11y ago

batou 11y ago

Hmm no. Avoid state at all costs. Stored procedures are stateful. Schema and migrations is pain enough already. Write me a check constraint that validates an email address being put in a varchar column and reports back a sensible message which can be bound to an entry field with metadata about the error. Write me a constraint and key arrangement which is unique across two and three columns in separate groups. No. You're wrong.

nunwuo 11y ago
> Avoid state at all costs. Stored procedures are stateful. Schema and migrations is pain enough already. What do you mean by that? How is having a bunch of queries in a stored procedure more "stateful" than having the same queries in the application? > Write me a check constraint that validates an email address being put in a varchar column and reports back a sensible message which can be bound to an entry field with metadata about the error. Postgres gives you metadata about the error, though the error message will still be a generic "CHECK constraint violated" or some such. > Write me a constraint and key arrangement which is unique across two and three columns in separate groups. I'm not sure what you want to see based on that description, but surely you're not advocating enforcing unique constraints in the application?
- batou 11y ago
  Stateful: If I have to load the stored procedure into the persistence engine then that step is required. This is no more stateful than queries in the application but it means that the relevant state in both the application and the database engine needs to be reloaded and constantly sychronised. Ergo, two times the work. CHECK constraint violated is no good for humans. Prevention is better than cure here. Why shouldn't I enforce unique constraints in the application? 1. Open a transaction 2. Get a user by name from the ORM. 3. Exists? Tell user that the username is already registered. 4. Doesn't exist? Save new User instance. 5. Commit transaction. Steps 2 and 3 can be as arbitrarily complicated as you need them to be, are fully testable and cheap with anything that uses MVCC.
  - dragonwriter 11y ago
    > CHECK constraint violated is no good for humans. Well, sure, an application should respond to DB errors by presenting appropriate messages on the UI, just like any other errors it encounters. You should only see "CHECK constraint violated" if you are bypassing the app and using the DB. Otherwise, you should see something nice provided by the app. > Why shouldn't I enforce unique constraints in the application? Because you should do it in the database whether or not you do it in the application, and then once you have, well, DRY.
  - rapala 11y ago
    Or you could do: 1. Insert new User instance 2. Unique constraint violation? Tell user that the username is already registered.
  - giaour 11y ago
    > Why shouldn't I enforce unique constraints in the application? This tightly couples your database to your application. You can no longer guarantee that your database is reliable when used otherwise.
  - brightball 11y ago
    "Why shouldn't I enforce unique constraints in the application?" You should to both. For all the reasons you mention, it's often cleaner to just do it in the application especially when you can use a framework with a simple "validate_uniqueness" flag. But, what you're describing is also the very definition of a race condition. It's the same reason you don't increment counters by retrieving them, adding 1 to it and then saving the number back to the database and instead pass in an increment command. Check it in the application but let the database make sure it doesn't get violated in a race condition. There's a significant amount of either/or in this entire conversation (not just you, the whole thread) when the database absolutely can and should be leveraged for certain things. It's extremism and purism where the problems get introduced (in both directions).
dragonwriter 11y ago
> Avoid state at all costs. Stored procedures are stateful. Stored procedures are no more state than application code is. > Write me a constraint and key arrangement which is unique across two and three columns in separate groups. What does "unique across two and three columns in separate groups" mean? I get that its something more complex than a simple multicolumn uniqueness constraint, but not what it is supposed to do. I suspect that whatever it is can be done with PostgreSQL -- possibly using the (relatively) new exclusion constraints -- but I can't quite be sure without more clarity on what you mean.

jeletonskelly 11y ago

> so they have fancy versioning, deployment and continuous integration for all their code, except for stored procedures. Exactly, because those things are extremely important to how code gets shipped and delivers value to the business. Stored procedures become a huge risk to future development which ultimately means it's a risk to the businesses ability to deliver value. What happens when you need to change DB vendors because the business has been so successful that you've outgrown a relational database? You have to rewrite the ENTIRE MC portion of your MVC application. Why would someone ever do this?