3 ms·

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

by 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).
    - merb 11y ago
      I do most what you say and still think both of you are right. I mean stored procedures have some use cases but i've seen people using it EVERYWHERE and I've seen people (including myself) NEVER use it. I mean currently my dataset is so small I don't need stored procedures, I barely do anything more than CRUD. Okay I have a bigger GROUP BY query but that is all, and at one point I load a HUGE dataset into my application memory (1000 rows) but that works REALLY REALLY fast in scala and I tried to create a stored procedure around it, but I failed, and the application code uses the dataset to generate a big calculation. Currently I just have a Map<String, Map<String, List<Row>> which is easy accessible and usable for my calculation. I mean I could've done similar with stored procedures but the performance gains are really low.
- herge 11y ago
  Not enforcing as the final line, but reporting the error back to the user in a way that can be handled/translated/etc.
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.

by batou 11y ago

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).
  - merb 11y ago
    I do most what you say and still think both of you are right. I mean stored procedures have some use cases but i've seen people using it EVERYWHERE and I've seen people (including myself) NEVER use it. I mean currently my dataset is so small I don't need stored procedures, I barely do anything more than CRUD. Okay I have a bigger GROUP BY query but that is all, and at one point I load a HUGE dataset into my application memory (1000 rows) but that works REALLY REALLY fast in scala and I tried to create a stored procedure around it, but I failed, and the application code uses the dataset to generate a big calculation. Currently I just have a Map<String, Map<String, List<Row>> which is easy accessible and usable for my calculation. I mean I could've done similar with stored procedures but the performance gains are really low.
herge 11y ago
Not enforcing as the final line, but reporting the error back to the user in a way that can be handled/translated/etc.

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.