On 24/07/13 20:33, Jeff Janes wrote:
On Tue, Jul 23, 2013 at 5:29 PM, Some Developer
<someukdeveloper@xxxxxxxxx> 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
