6 ms·
As long as you have the necessary tooling for source control, versioning, code search, etc. -- why is it the pits? I mean, if you stick to a rigorous procedure
by crazygringo 7y ago
As long as you have the necessary tooling for source control, versioning, code search, etc. -- why is it the pits?
I mean, if you stick to a rigorous procedure of automatically exporting your stored procedures to files and committing them to git with messages... how is that different from any other development?
Serious question -- I've done tons of database work but only a little bit with stored procedures, so wondering if I'm missing a crucial detail here.
- throw149102 7y agoNot OP, but I want to say there's a lot more that goes into the development process than just source control, versioning, and code search. There's linters, and dozens of different kinds of tests, and ways of automatically running those tests, and layers of procedure before code changes go public. This seems much harder to do for stored procedures. Especially considering that stored procedure languages are fragmented between different databases; you don't just pay the cost of having to change the stored procedures when changing databases, you are also paying for the fact that it is harder to build automated tools to analyze the stored procedures, because the API for each database is going to be different. That being said, there are lots of factors that influence how maintainable a specific stored procedure is. Team size, business needs, company culture, etc. Stored procedures get a bad rep from the places where they are train wrecks. YMMV.
- Intermernet 7y agoI'd highly recommend playing around with stored procedures. They, at a minimum, can provide data integrity guarantees that you can't get with anything else. As an example, for ages the only way to do a reliable (pseudo atomic) upsert call on most databases was through a stored procedure. Once I discovered stored procedures, I started using them everywhere. After a while I realised that you need to put your logic in the appropriate place for all of the usual reasons (performance, maintainability, security etc) and resisted the temptation to dump all business logic into stored procedures, but I still use them a lot. They're like the bridge between the dba world and the traditional developer world. "An elegant weapon, for a more civilised age..."
- danielbarla 7y agoIt's a completely valid question, so I'll try to give a serious answer, from my perspective. Firstly, I'd like to say that SPs can be quite awesome, for several of the reasons already stated. It is normally one of the few ways one can truly enforce integrity, access controls, and logging at the DB level. (Also, most of my experience is from an MS SQL and C# background, so some of this may not translate over to PostgreSQL quite so well) 1. Verbosity and fitness for purpose: while there is a kind of beauty in pure set-based SQL querying, stored procedures by their nature tend to be a procedural paradigm, and in my opinion, they are not particularly well suited to it. They are insanely verbose by today's standards, and doing anything typically requires several times more code than the equivalent in most modern languages (your mileage may vary with different database engines!). This results in large, monolithic SPs, which are difficult to read, understand, and test. The "standard library" is generally far less extensive than mainstream languages, leading to sub-standard, verbose, slow implementations (string concatenations and manipulations are a good example in MS SQL). 2. Code navigation, dependency management: the inverse of point 1 - in general, the tooling around databases for code searching, navigation, etc, is quite woeful in comparison to non database languages. In MS SQL, dependency management is so unreliable, that the official tooling misses several classes of dependencies, just depending on the type of syntax you happened to use. In your question, you start by saying that if you have the necessary tooling already obtained, it's all good. In my experience, rarely are even "barely good enough" tools in place, let alone anything close to what exists in other environments. 3. Unit testing: since databases tend to be stateful, the very concept of unit testing is a bit of an alien one. People don't tend to write SPs with unit tests in mind, and at least in the MS SQL world, the few "unit testing" frameworks that did exist tended to be incredibly painful to use - to the point that they were generally not worth it. Brittle, expensive integration tests are not the same. 4. Execution plan caching: the way databases handle caching of SP execution plans leaves much to be desired - generally the first set of parameters that an SP gets called with end up determining the execution plan for all future calls. Even in well managed code bases, this ends up being an issue, occasionally needing a DBA to manually go in, identify a misbehaving execution plan, and evict it. Vary by param, and no-cache options do not fully solve this. While the need for such mechanisms is obvious, it is surprising that these have been a pain for 20+ years. Now, execution plan caching also happens for normal SQL too, so my point is rather that the culture around SPs tends to have an unreasonable fear of dynamic SQL, which would be far more appropriate _where the query actually is of a dynamic nature!_ I can imagine people replying "but clearly, you're doing things that you shouldn't be - that's not what SPs are for!". And I agree, in a large part that is my point too. But then, what exactly should SPs be used for?
- ggreer 7y agoLet's say that when you deploy, you want to direct 1% of traffic to the new code, then 5%, then 25%, then 50%, and finally 100%. And if monitoring metrics go red at any point, automatically roll back. A typical canary deployment. How do you do that with a sproc? With normal application code, you can have the load balancer send an arbitrary percentage of traffic to the new instances of the service. A sproc binds the code and the data together. But you only have one database, so there's no way to do a gradual deploy of the code. That would be like doing a gradual deploy of a schema migration. It's only possible if you have a lot of databases. Also unless you're using MS SQL server, debugging sprocs is a pain. The only workaround I've seen is to add a debug or loglevel arg to the sproc. But actually pausing execution and inspecting data? Good luck.
- dragonwriter 7y ago> Let's say that when you deploy, you want to direct 1% of traffic to the new code, then 5%, then 25%, then 50%, and finally 100%. And if monitoring metrics go red at any point, automatically roll back. > How do you do that with a sproc? Wrap the real sproc (well, the set of real sprocs, since old and new will sometimes coexist) with a wrapper sproc that probabilistically directs to the appropriate working sproc based on a table value (or anything else the sproc can read) that is updated to reflect the current stage in the conversion process, including any monitoring-based reversals.
- ggreer 7y agoHow do you correlate those errors at the application level? If only 1% of sproc calls are the new sproc, any elevated error rates in the application will be hard to see over typical noise.
- dragonwriter 7y ago> How do you correlate those errors at the application level? If only 1% of sproc calls are the new sproc, any elevated error rates in the application will be hard to see over typical noise. By using the DBs error reporting features to report back errors to the application, including information about which ultimate implementation was handling the request.