3 ms·

Why are stored procs still used? My experience with the black hole (or really I should say not in version control) pushed me away from them.

detaro 7y ago
Why not put them in version control, and have your deployment process pull them out of the repo and apply them to the database?
- tejtm 7y ago
  yep CREATE OR REPLACE FUNCTION ... what whatever it was, it now is what came out of version control
- ben509 7y ago
  It's a bit more complicated than just "upload all my stored procedures" in practice. I think we need a version control / deployment system that is designed for databases to make this work. We want it to regard the database as the source of truth, and understand that while you can blow away a development database, you can't do that to prod. We want it to be able to track which clients are using which stored procedures so it can prohibit dropping stored procedures that are in use. Ideally, when those clients upgrade to newer versions, it's then able to drop them automatically. Stored procedures can bind to tables, views, types, etc. so it needs to be able to recommend migration plans.
  - cryptonector 7y ago
    I don't get it. You have a source file. It has your store procedures and what not. You deploy it. You have the server startup load it. Done. What's the problem? Well, you might complain that some schema changes are hard to make this way, and that's true, but for stored procedures it's straightforward, and even for other things you can manage and make the update atomic (though it'd be nicer to have better SQL language support for schema changes). As with everything, you need to mind backwards compatibility. This is true for C, C++, Rust, Java, and SQL/PlPgSQL. There's nothing special about this case.
    - ben509 7y ago
      > You have the server startup load it. We have to take down a production database to do this? Or do you mean the app server is going to do it, in which case how do you handle different app servers with different versions of the stored procedure? Especially if we're doing phased deployments?
      - cryptonector 7y ago
        No, you can load it at any time. Just write (and test) SQL that is safe to load for upgrading a schema.
dandigangi 7y ago
Thanks for the comments. They were helpful.

detaro 7y ago

Why not put them in version control, and have your deployment process pull them out of the repo and apply them to the database?

tejtm 7y ago
yep CREATE OR REPLACE FUNCTION ... what whatever it was, it now is what came out of version control
ben509 7y ago
It's a bit more complicated than just "upload all my stored procedures" in practice. I think we need a version control / deployment system that is designed for databases to make this work. We want it to regard the database as the source of truth, and understand that while you can blow away a development database, you can't do that to prod. We want it to be able to track which clients are using which stored procedures so it can prohibit dropping stored procedures that are in use. Ideally, when those clients upgrade to newer versions, it's then able to drop them automatically. Stored procedures can bind to tables, views, types, etc. so it needs to be able to recommend migration plans.
- cryptonector 7y ago
  I don't get it. You have a source file. It has your store procedures and what not. You deploy it. You have the server startup load it. Done. What's the problem? Well, you might complain that some schema changes are hard to make this way, and that's true, but for stored procedures it's straightforward, and even for other things you can manage and make the update atomic (though it'd be nicer to have better SQL language support for schema changes). As with everything, you need to mind backwards compatibility. This is true for C, C++, Rust, Java, and SQL/PlPgSQL. There's nothing special about this case.
  - ben509 7y ago
    > You have the server startup load it. We have to take down a production database to do this? Or do you mean the app server is going to do it, in which case how do you handle different app servers with different versions of the stored procedure? Especially if we're doing phased deployments?
    - cryptonector 7y ago
      No, you can load it at any time. Just write (and test) SQL that is safe to load for upgrading a schema.

dandigangi 7y ago

Thanks for the comments. They were helpful.