9 ms·

Working with stored procedures is just the pits. I wonder why you would describe that as "great". Maybe it's optimized, but so would be a giant executable wri

Working with stored procedures is just the pits. I wonder why you would describe that as "great". Maybe it's optimized, but so would be a giant executable written in assembly.

icedchai 7y ago
Last time I worked with stored procs with an Oracle 8 database, over 20 years ago. PL/SQL was awful.
- 7thaccount 7y ago
  I work with one extremely large application written in Oracle stored SQL procedures. It is a pain, but pretty efficient.
asguy 7y ago
It is great: your code runs right near the data it’s working with. If it’s the pits, you need a better suited development environment. I use emacs and interactively code inside the DB. Python, Lua, JavaScript, PGSQL, whatever.
- tomrod 7y ago
  I'm a journeyman in this space, and don't know emacs well. Can you help me unpack your comment here? You write python in the database directly?
  - cat199 7y ago
    not OP but likely: 1) coding via repl - e.g. one emacs buffer has stored procedure code, send functions over via keyboard shortcut to SQL repl buffer connected to DB to evaluate it 'live' (jupyter notebooks are basically a copy of this mode of operation in a more consumer-friendly package) 2) w/r/t python in db, as OP mentions pgsql, pgsql has support for in-db extensibility using a variety of languages, of which python is one: https://www.postgresql.org/docs/current/plpython.html https://www.postgresql.org/docs/current/plpython.html probably referring to this
    - tomrod 7y ago
      Thanks!
xupybd 7y ago
I've found the opposite. It can take a long time to get your head into that space but it produces much more maintainable and robust systems in my experience.
- pingec 7y ago
  I have a similar experience. I've worked with a product that consists of tens of thousands of stored procedures implementing all business logic. Because you are right next to the data you can get away with doing many thing you would have not otherwise. Also it acted like a barrier, the core team who understood the business well worked on the stored procedures, keeping things fast and stable. Another team was doing the tech to interface with the database and UX/UI was then implemented in various technologies in parallel and changed/got recycled every few years to whatever was in fashion. The difference in culture between the two teams was huge, it seemed to work out well in their case.
  - xupybd 7y ago
    It also saves a lot of extra work. Often with more conventional systems you have to pull the data out. Move it into a structure native to the language doing the processing. In the database you don't need to do any of this. The data is there. The type information is there.
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 ago
  Not 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 ago
  I'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 ago

Working with stored procedures is just the pits. I wonder why you would describe that as "great". Maybe it's optimized, but so would be a giant executable written in assembly.

icedchai 7y ago

Last time I worked with stored procs with an Oracle 8 database, over 20 years ago. PL/SQL was awful.

7thaccount 7y ago
I work with one extremely large application written in Oracle stored SQL procedures. It is a pain, but pretty efficient.

asguy 7y ago

It is great: your code runs right near the data it’s working with. If it’s the pits, you need a better suited development environment. I use emacs and interactively code inside the DB. Python, Lua, JavaScript, PGSQL, whatever.

tomrod 7y ago
I'm a journeyman in this space, and don't know emacs well. Can you help me unpack your comment here? You write python in the database directly?
- cat199 7y ago
  not OP but likely: 1) coding via repl - e.g. one emacs buffer has stored procedure code, send functions over via keyboard shortcut to SQL repl buffer connected to DB to evaluate it 'live' (jupyter notebooks are basically a copy of this mode of operation in a more consumer-friendly package) 2) w/r/t python in db, as OP mentions pgsql, pgsql has support for in-db extensibility using a variety of languages, of which python is one: https://www.postgresql.org/docs/current/plpython.html https://www.postgresql.org/docs/current/plpython.html probably referring to this
  - tomrod 7y ago
    Thanks!

xupybd 7y ago

I've found the opposite. It can take a long time to get your head into that space but it produces much more maintainable and robust systems in my experience.

pingec 7y ago
I have a similar experience. I've worked with a product that consists of tens of thousands of stored procedures implementing all business logic. Because you are right next to the data you can get away with doing many thing you would have not otherwise. Also it acted like a barrier, the core team who understood the business well worked on the stored procedures, keeping things fast and stable. Another team was doing the tech to interface with the database and UX/UI was then implemented in various technologies in parallel and changed/got recycled every few years to whatever was in fashion. The difference in culture between the two teams was huge, it seemed to work out well in their case.
- xupybd 7y ago
  It also saves a lot of extra work. Often with more conventional systems you have to pull the data out. Move it into a structure native to the language doing the processing. In the database you don't need to do any of this. The data is there. The type information is there.

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 ago
Not 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 ago
I'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 ago