Are stored procedures/triggers common in your industry

On 4/20/22 13:18, Guyren Howe wrote:

I’ve really only ever worked in web development. 90+% of web
developers regard doing anything at all clever in the database with
suspicion.

I’m considering working on a book about implementing business logic in
Postgres, and I’m curious about how common that actually is.

I have to wonder if any particular subset of the programming industry is
less qualified to make such a judgement?

We’ve used them in the past, but sparingly. Usually if the data is abstracted nicely for loading into the DB, you can get away with most processes only needing SQL, at least in our cases. There are obvious exceptions for things like monitoring or logging.

Our use has been for running some setup scripts (with PERL) to generate some derivative CAD models from the PG DB on the fly, but that was a real specific process need.

Bobb

My machine - - - PW19-S295-C024

From: Guyren Howe <guyren@gmail.com>
Sent: Wednesday, April 20, 2022 2:18 PM
To: pgsql-general@lists.postgresql.org
Subject: Are stored procedures/triggers common in your industry

Think Before You Click: This email originated outside our organization.

I’ve really only ever worked in web development. 90+% of web developers regard doing anything at all clever in the database with suspicion.

I’m considering working on a book about implementing business logic in Postgres, and I’m curious about how common that actually is.

On Apr 20, 2022, at 3:18 PM, Guyren Howe <guyren@gmail.com> wrote:

I’ve really only ever worked in web development. 90+% of web developers regard doing anything at all clever in the database with suspicion.

I’m considering working on a book about implementing business logic in Postgres, and I’m curious about how common that actually is.

We have some business logic in Postgres functions, particularly triggers. Our apps are written in Python, and we use pytest to exercise our SQL functions to ensure they're doing what we think they’re doing. It works well for us.

FWIW, we’re not a Web dev shop.

Cheers
Philip

On 4/20/22 12:18, Guyren Howe wrote:

I’ve really only ever worked in web development. 90+% of web developers
regard doing anything at all clever in the database with suspicion.

I’m considering working on a book about implementing business logic in
Postgres, and I’m curious about how common that actually is.

For my purposes keeping this logic in the database makes changing or
running multiple front ends easier. There is one place to change the
logic vs keeping the same logic in different front ends in potentially
different languages in sync. So for me it is common.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 20/04/2022 20:26, Philip Semanchuk wrote:

We have some business logic in Postgres functions, particularly triggers. Our apps are written in Python, and we use pytest to exercise our SQL functions to ensure they're doing what we think they’re doing. It works well for us.

FWIW, we’re not a Web dev shop.

Cheers
Philip

We have a a great amount of our business logic in triggers; makes for
light, multiple and consistent front-ends. It's worked very well for
many years and continues to grow.

Tim Clarke

Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/

Watch our latest Minerva Briefings on BrightTALK<https://www.brighttalk.com/channel/18792/?utm_source=brighttalk-sharing&amp;utm_medium=web&amp;utm_campaign=linkshare&gt;

Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom

________________________________

Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.

I've really only ever worked in web development. 90+% of web
developers regard doing anything at all clever in the database with
suspicion.

One common argument they use is that if you write your business logic in
stored procedure, you are locked to that database since stored procedure
languages are pretty much vendor locked.

TBH when one sees tens of thousands of Oracle PL/SQL code, there is some
truth in this.

Agree on the lock in comment, however, can't we say that of anything one is
dependent on in the tech stack, whether that be at the java vs javascript
vs python, or now aws vs azure vs gcp?

Have always wondered that lock in concern seems to be only mentioned in
light of dbs, but not any other piece of the tech stack.

On Wed, Apr 20, 2022 at 3:31 PM Ravi Krishna <srkrishna@vivaldi.net> wrote:

Agree with the comment on python, et al. I meant lock in within the context
of Oracle PL/SQL.

Actually, the point of Postgres support for all the languages you mentioned
is interesting in that such mitigates the lockin argument if the DB used
is. Postgres . Another reason to use Postgres, among all the other
reasons we know.

Architecturally speaking, using stored procs is a an elegant solution that
provides proper abstraction for the Data Layer API, effectively separating
the data access and security layers (stored proc design considerations)
from the data model and storage considerations. This API type of layer
provided by procs is more closely aligned with how one thinks about
microservices.

Secondly, it will generally be runtime faster as it avoids the critical
network round trips that take up those precious milliseconds.

On Wed, Apr 20, 2022 at 3:54 PM Guyren Howe <guyren@gmail.com> wrote:

It's a very wierd concern for me. I have never liked that justification as
we convert 1:1 SAS to python. If you use Django, converting it to flask is
really hard. If you use postgresql, converting it to oracle is really hard.

I love stored procedures and triggers. Many of my colleagues don't
understand why sticking everything on a database is a great idea. These are
the same people who think that unique constraints are too much overhead.
It's a great tool to use. Do you need CRUD stored procedures when sql
alchemy exists? Nope. Do you need it when doing an extremely complex select
with multiple joins that you want to run all the time? Maybe. Or allowing
insert operations on base tables in a view. Or tracking and monitoring who
does what. Also, you can back up stroed procedures to make updates easy.

Thanks,
Ben

On Wed, Apr 20, 2022, 4:48 PM Alex Aquino <alex@efficiencygeek.com> wrote:

On 4/20/22 15:18, Guyren Howe wrote:

I’ve really only ever worked in web development. 90+% of web
developers regard doing anything at all clever in the database with
suspicion.

I’m considering working on a book about implementing business logic in
Postgres, and I’m curious about how common that actually is.

Well, there are 2 schools of thought:

1. Put the business logic into the application
2. Put the business logic into the database

Putting the business logic into the application can give you more
flexibility around enforcing them. On the other hand, you also increase
chances of inconsistency. There will likely be more than one application
using reference tables like ADDRESS, ZIP_CODE, STATE, COUNTRY, QUARTER,
ACCOUNT, CUSTOMER and similar. If there is a rule that a country must
exist before you add an address in that country into the table, that can
be enforced by a foreign key. Enforcing it within the application does 2
things:

1. Move the rule code to the application server which is traditionally
weaker than a database server. In other words, you are more likely
to run out of CPU juice and memory on an application server than you
are likely to run out of resources on the DB server.
2. There is a possibility for inconsistency. Different applications can
use different business rules for the same set of tables. That means
that data entered by one application may make the table internally
inconsistent for another application.

I am a big proponent of using foreign keys, check constraints and
triggers to enforce business rules. I am also a big proponent of
avoiding NULL values wherever possible. Database design is an art. CAD
software used to be popular once upon a time, in a galaxy far, far
away.  Properly enforcing the business rules in the database itself
makes the application more clear and easier to write.

Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On Wed, Apr 20, 2022 at 12:18:23PM -0700, Guyren Howe <guyren@gmail.com> wrote:

I’ve really only ever worked in web development. 90+% of web
developers regard doing anything at all clever in the database with
suspicion.

I’m considering working on a book about implementing business logic in
Postgres, and I’m curious about how common that actually is.

I'm used to putting all business logic in the database
(after choosing a great FLOSS database that you'll
never want to migrate away from - like Postgres). And
I've never regretted it (in decades of doing it).

One of the main reasons is speed. I once had a job
where a program selected data out of a database,
dragged it over a network, effectively grouped it into
summaries, sent the summaries back over the network,
and inserted them back into the database one at a
time(!). Replacing it with a stored procedure changed
it from taking 2-3 hours to 2 minutes. And that was a
place that already made heavy use of stored procedures,
so I don't know what went wrong there. The point is
that whenever a lot of data activity is needed, it's
much faster when it's done where the data lives.

The other main reason is security. The database can
provide an effective "firewall" between the data and
the client. I never liked the idea of trusting
arbitrary SQL sent from the client. It means you have
to trust every single client application and every
single user (even the ones with good intentions that
produce bad queries in some reporting software and
throwing it at the database and bringing it to its
knees) and every single developer (who might not know
SQL and relies on ORMs that trick them into thinking
they don't need to). But when the clients are only
permitted to execute security defining stored
procedures that have been loaded by the privileged
database owner, you know exactly what code can run
inside the database. SQL injections become impossible
no matter how many bugs and flaws there are in the
client software or its supply chain.

Another good but less critical reason is that when you
use multiple languages, or you migrate partially or
completely from the old cool language to the new cool
language, you don't have to replicate the business
logic in the new language, and you can eliminate the
risk of introducing bugs into mission critical code.
The existing business logic and its test suite can stay
stable while all the bells and whistles on the outside
change however they like.

There are other nice benefits but that's enough.

I think it's safe to disregard the suspicions of the
90+% of web developers you mentioned. The requirements
that they have for a database might be quite
undemanding. Most individual actions on a website
probably don't result in a lot of data activity (or
rather activity that involves a lot of data). The CRUD
model is probably all they need. So their views are
understandable, but they are based on limited
requirements. However, I still use stored procedures
for everything on websites for security reasons.

Everyone's mileage varies. We're all in different places.

cheers,
raf

You mentioned testing, and reminds me of another benefit. Way faster, more
reliable, cheaper to test on the DB side. Testing logic in SPs or SQL is
much easier, especially when testing requires a sequence of calls for a use
case. It is easier because of the DBs support for transactions. With
transactions and state management built into the DB, a testing process can
always revert to a reliable starting point and end point, thereby
facilitating more dependable, automated test harnesses. The alternative
done mostly now is testing via UIs or APIs where there is no inherent
transaction management, so a lot of work goes into preparing the test bed
to be a known state and introspecting the results to verify. This is
usually done with some mix of manual and automated processes.

On Thu, Apr 21, 2022 at 12:31 AM raf <raf@raf.org> wrote:

On Thu, Apr 21, 2022 at 08:42:10AM -0500, Alex Aquino <alex@efficiencygeek.com> wrote:

You mentioned testing, and reminds me of another benefit. Way faster, more
reliable, cheaper to test on the DB side. Testing logic in SPs or SQL is
much easier, especially when testing requires a sequence of calls for a use
case. It is easier because of the DBs support for transactions. With
transactions and state management built into the DB, a testing process can
always revert to a reliable starting point and end point, thereby
facilitating more dependable, automated test harnesses. The alternative
done mostly now is testing via UIs or APIs where there is no inherent
transaction management, so a lot of work goes into preparing the test bed
to be a known state and introspecting the results to verify. This is
usually done with some mix of manual and automated processes.

Actually, my full work tests take ages to run (~40m).
I know that mocking the db to make unit tests fast is popular,
but that's not helpful when the most important code being tested
is in the database. :-) It's more important to me that the tests
actually test everything than that they be fast.

But yes, being able to do complex system testing with transaction
rollback is great.

cheers,
raf