Egregoros

everyone who says "it's terrible to use these database features, this should all be application logic because... testing, and $reasons" seem to be from people who are NoSQL-brained and can't escape it

@feld you need to demonstrate serious competence and professionalism before you are entrusted with that power.

@feld anyway the argument against stored procedures was, one, you're probably doing it wrong and two, now you're locked into one vendor's system for life. that said I have implemented them before when I needed both the querying and custom aggregation

@feld maybe it depends on the features. When I was a DBA the programmers (ok, I also programmed too) avoided many database features. Stored procedures were harder to upgrade and were slower. There was a dream of being cross DB compatible too by sticking to the SQL standards as much as possible. We’d lived through many tech changes and wanted to minimize work if it’d be needed again.

@drewdaniels stored procedures were slower? Weird, doing that work in the database should be *faster* than doing it in the application layer.

@feld the problem is that queries in the sproc either didn’t get optimized, or bypassed the cache I think. I think Percona used to have a blog post about it.

@drewdaniels Percona as in MySQL fork?

@feld I've always opted towards views before stored procedures but I remember having to do a few things with Microsoft SQL Server that needed to be done as stored procedures and there's something to say for having some programmatic logic be there instead of inside of if your application (especially if you're using different technology stacks for different applications).

@sun@shitposter.world @feld@friedcheese.us how often you're transitioning between DB vendors anyways?

(also, since writing them is litteraly part of my job, the best use cases I'd say are either small ones for ensuring nore complex consistency terms of the database, or large ones for bulk data transformation and saving.

@ignaloidas @feld I have presided over at least ten different projects to migrate a company away from Oracle to PostgreSQL. In a previous job I left in 2010, almost everything was written using PL/SQL in Oracle. They are still using Oracle because it was impossible to rewrite them.

I am very against using stored procedures unless you can can bring a strong case for why your usage doesn't get us swamped for life on one platform.

@ignaloidas @feld I agree, nobody should be against stored procedures for batch processes

@ignaloidas @sun I once worked with a company that had 20 million lines of PL/SQL for Oracle

@feld @ignaloidas oracle best practices

@sun @ignaloidas "compiling" and versioned "packages" of this code in the database still makes me go googly-eyed thinking about it

it's like they turned the database into an operating system

@feld@friedcheese.us @sun@shitposter.world ok thats bonkers and stupid

Both because it's on oracle, and because SQL is kinda ass for such code volume

@ignaloidas @feld you can serve entire pl/sql php-style pages directly out of the database. it is an abomination. it's deprecated now at least.

@sun @ignaloidas that's exactly what they built. A ridiculous super customizable CMS where the HTML, js, and CSS was dynamically being generated out of the database too. Thanks to Apache's mod_ora or whatever that made this possible.