Re: Why are stored procedures looked on so negatively? - Mailing list pgsql-general

On 24/07/13 20:33, Jeff Janes wrote:
> On Tue, Jul 23, 2013 at 5:29 PM, Some Developer
> <someukdeveloper@gmail.com> wrote:
>> I've done quite a bit of reading on stored procedures recently and the
>> consensus seems to be that you shouldn't use them unless you really must.
>
> I think that mostly speaks to the method you used for finding things
> to read.  This is a well known holy war.
>
>>
>> I don't understand this argument.
>
> That is a conclusion, not an argument.  You didn't give us the
> argument behind the conclusion!
>
>> If you implement all of your logic in the
>> application then you need to make a network request to the database server,
>> return the required data from the database to the app server, do the
>> processing and then return the results. A stored procedure is going to be a
>> lot faster than that even if you just take away network latency / transfer
>> time.
>
> Sorry, I don't get this at all.  Whether you use a stored procedure or
> not, the database needs to be told what to do by the outside word, and
> needs to return the result to the outside world.  So you can not get
> rid of that minimal round trip, no matter what, unless your database
> becomes solipsist.  Now, if the application-side code needs to make a
> lot of round trips to the database in order to implement one logical
> unit of work, that is a different matter and stored procedures could
> help there (but so could consolidating the round trips into a fewer
> number of more sophisticated SQL--which is often but not always
> possible).

The reason that I think stored procedures and triggers are the correct
way to go for my database is because I need certain actions to be
performed when data is inserted, updated and deleted. Doing that in the
app layer would be a waste of time since the database already provides a
very well tested set of functionality to handle this.

The added advantage of removing load from the app servers so they can
actually deal with serving the app is a bonus.

I'm not planning on creating a complex application in the database in
its own right, just augmenting what is already available with a few time
savers and (a couple of) speed optimisations for commonly carried out tasks.

> One of the very annoying uses of stored procedures I see is insisting
> that all access goes through them, with no direct access to the
> underlying tables via ordinary SQL.  They have now replaced one of the
> most successful, powerful, and well-known data access APIs ever, with
> some home grown API that is probably half-baked.  Sometimes a case can
> be made for that (particularly for large bureaucratic organizations,
> or intensely regulated ones) , but don't expect it to be free of
> consequences.

I certainly won't be doing that. All the queries will be accessing the
tables directly and the stored procedures will only fire when a trigger
goes off. This is more about have async actions take place when a user
creates / edits / deletes a certain type of action. The added advantage
that triggers work well with the PostgreSQL transaction system is a real
bonus.

>> I'm in the middle of building a database and was going to make extensive use
>> of stored procedures and trigger functions because it makes more sense for
>> the actions to happen at the database layer rather than in the app layer.
>>
>> Should I use them or not?
>
> Are you a one man shop, and always will be?  If so, i think it is
> mostly a matter of what you prefer developing in, and what you are
> most used to developing in.  If you hire someone to help you out, do
> you want that person to be able to do interesting (and perhaps bad)
> things with the database through SQL, or do you want them to be mostly
> restricted to changing the font and color of the web page showing the
> results?  A case could be made for either way.
>
> Cheers,
>
> Jeff
>

Thanks for your input. Hopefully I've explained in a bit more detail
what I am trying to do.