Thread: Why are stored procedures looked on so negatively?

Why are stored procedures looked on so negatively?

From
Some Developer
Date:
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 don't understand this argument. 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.

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?


Re: Why are stored procedures looked on so negatively?

From
hidayat365@gmail.com
Date:
I presume you're refering to trigger. Since trigger often do something automagically :) and it sometime make developer
hardto debug when something wrong since they they do not aware that there are triggers exist in database.
 

Stored procedure is OK.

CIIMW
Sent from my BlackBerry®
powered by Sinyal Kuat INDOSAT

-----Original Message-----
From: Some Developer <someukdeveloper@gmail.com>
Sender: pgsql-general-owner@postgresql.orgDate: Wed, 24 Jul 2013 01:29:14 
To: <pgsql-general@postgresql.org>
Subject: [GENERAL] Why are stored procedures looked on so negatively?

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 don't understand this argument. 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.

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?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Why are stored procedures looked on so negatively?

From
Adrian Klaver
Date:
On 07/23/2013 05:29 PM, Some Developer 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 don't understand this argument. 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.
>
> 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?

Personally I figure the arguments for and against are closely correlated
with where on the development chain you are, and are tied in with job
security. If you are an app developer than it is in your interest to
have code in the app, if you are a database developer in the database.
Me, I am tend to go with your argument about keeping procedures, where
appropriate, in the database for the reasons you state. In other words
an API in the database.

>
>


--
Adrian Klaver
adrian.klaver@gmail.com


Re: Why are stored procedures looked on so negatively?

From
Kevin Goess
Date:

On Tue, Jul 23, 2013 at 5:40 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 07/23/2013 05:29 PM, Some Developer wrote:
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?

Personally I figure the arguments for and against are closely correlated with where on the development chain you are, and are tied in with job security. If you are an app developer than it is in your interest to have code in the app, if you are a database developer in the database. 

What he says is very true. But make sure to think about things that may already be set up to manage the application code: versioning, testing, unit testing, packaging, release process, and documentation--how much of that is in place for your stored procedures and triggers?  If a developer makes a change to application code, it gets checked in to source control, unit tested, code reviewed, goes through some QA, and is staged for the next roll to production--will that all happen for your stored procedures?  And consider, there is already logic in the application, now some of the logic will be in the application and some of it will be in the database--does it make sense to have it in two places?  

I think those are the kind of concerns that make people shy about putting too much logic in the database.  None of them are insurmountable, but you should at least think about them.

Re: Why are stored procedures looked on so negatively?

From
John Meyer
Date:
Taking an absolutist position either way is pretty blind.   What is the
purpose of the procedure?  Is it enforcing business rules?  Are these
rules that must be enforced against already existing data or are they
more akin to validation of a credit card.  How many people are accessing
your database at one time?  And most importantly, what are you best at?

Adrian Klaver wrote:
> On 07/23/2013 05:29 PM, Some Developer 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 don't understand this argument. 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.
>>
>> 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?
>
> Personally I figure the arguments for and against are closely
> correlated with where on the development chain you are, and are tied
> in with job security. If you are an app developer than it is in your
> interest to have code in the app, if you are a database developer in
> the database. Me, I am tend to go with your argument about keeping
> procedures, where appropriate, in the database for the reasons you
> state. In other words an API in the database.
>
>>
>>
>
>



Re: Why are stored procedures looked on so negatively?

From
Some Developer
Date:
On 24/07/13 01:55, John Meyer wrote:
> Taking an absolutist position either way is pretty blind.   What is the
> purpose of the procedure?  Is it enforcing business rules?  Are these
> rules that must be enforced against already existing data or are they
> more akin to validation of a credit card.  How many people are accessing
> your database at one time?  And most importantly, what are you best at?

Basically what happens is an object is created in the application and
saved to the database. When the insert has completed I need to start a
process immediately based on the information in the object on another
server (Amazon Simple Message Queue to be precise).

So basically I'll have a trigger function that fires on INSERTs and does
this work. That way the action will only be performed on INSERTs that
have successfully completed and I can be sure that the trigger will
always fire.

On top of that there are a few common data structures that need to be
written to the database that would be perfect for stored procedures
since there is a little logic involved in saving them which shouldn't
really be exposed to the application developers.



Re: Why are stored procedures looked on so negatively?

From
Andrew Sullivan
Date:
On Tue, Jul 23, 2013 at 06:55:56PM -0600, John Meyer wrote:
> are accessing your database at one time?  And most importantly, what
> are you best at?

That is one of the most important questions, for sure, but there's a
close second that I'd suggest: what are the scaling properties?

For practical purposes, if you're going to do complicated data
validation and business logic in the application, you have any
significant degree of contention, and you need to write some data, the
use pattern is going to look something like this (A is application, D
is database):

    A: get some data
    D: here you go, optimistic lock value L
    A: do some work
    A: given this value, get some more data
    D: here you go, optimistic lock value L2
    A: INS/UPD/DEL data, optimistic lock value L, RETURNING data
    D: ok, here you go, optimistic lock value L3
    A: do some work
    A: INS/UPD/DEL data, optimistic lock value L3
    D: ok

And that's if none of the optimistic locks fails.  That's a lot of
round trips.  If you have 20 transactions a minute, this is just fine.
If you have 2000 transactions per second, it totally sucks: you're
buried in round trips.

In my experience, if you want your application to scale to large
numbers of users, you need to avoid application<->database round
trips.

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca


Re: Why are stored procedures looked on so negatively?

From
Some Developer
Date:
On 24/07/13 02:56, Andrew Sullivan wrote:
> On Tue, Jul 23, 2013 at 06:55:56PM -0600, John Meyer wrote:
>> are accessing your database at one time?  And most importantly, what
>> are you best at?
>
> That is one of the most important questions, for sure, but there's a
> close second that I'd suggest: what are the scaling properties?
>
> For practical purposes, if you're going to do complicated data
> validation and business logic in the application, you have any
> significant degree of contention, and you need to write some data, the
> use pattern is going to look something like this (A is application, D
> is database):
>
>      A: get some data
>      D: here you go, optimistic lock value L
>      A: do some work
>      A: given this value, get some more data
>      D: here you go, optimistic lock value L2
>      A: INS/UPD/DEL data, optimistic lock value L, RETURNING data
>      D: ok, here you go, optimistic lock value L3
>      A: do some work
>      A: INS/UPD/DEL data, optimistic lock value L3
>      D: ok
>
> And that's if none of the optimistic locks fails.  That's a lot of
> round trips.  If you have 20 transactions a minute, this is just fine.
> If you have 2000 transactions per second, it totally sucks: you're
> buried in round trips.
>
> In my experience, if you want your application to scale to large
> numbers of users, you need to avoid application<->database round
> trips.
>
> Best,
>
> A
>

Thanks for the response. Obviously since I am still in the development
stage I have no idea of the number of transactions I will need to handle
but the business has the potential to be quite popular so I'd rather be
safe than sorry and be able to handle large amounts of traffic from day one.

I think ultimately it'll be simpler this way because the system I am
developing is a quasi distributed system with lots of independent parts
that need to be able to communicate and to share data with each other.


Re: Why are stored procedures looked on so negatively?

From
Albe Laurenz
Date:
Some Developer wrote:
> On 24/07/13 01:55, John Meyer wrote:
> > Taking an absolutist position either way is pretty blind.   What is the
> > purpose of the procedure?  Is it enforcing business rules?  Are these
> > rules that must be enforced against already existing data or are they
> > more akin to validation of a credit card.  How many people are accessing
> > your database at one time?  And most importantly, what are you best at?
> 
> Basically what happens is an object is created in the application and
> saved to the database. When the insert has completed I need to start a
> process immediately based on the information in the object on another
> server (Amazon Simple Message Queue to be precise).
> 
> So basically I'll have a trigger function that fires on INSERTs and does
> this work. That way the action will only be performed on INSERTs that
> have successfully completed and I can be sure that the trigger will
> always fire.

If you want to write a (trigger) function that starts a process on
a remote machine, there are a few points to think about:

- Should the INSERT fail if the remote process cannot be started?
  If yes, then a trigger is a good idea.
- If you code it as a trigger, be aware that the transaction
  is not complete until the remote process has been started.
  That might be a noticable delay and might affect concurrency
  negatively.

Yours,
Laurenz Albe

Re: Why are stored procedures looked on so negatively?

From
Luca Ferrari
Date:
On Wed, Jul 24, 2013 at 2:29 AM, 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 believe because most developers are not DBAs, and therefore are
scared about something they cannot control.
Placing as much logic as possible in the database is, in my opinion,
good since it will prevent any accidental (?) connection to the
database to corrupt your data. By accidental connection I mean a
developer/dba connecting to the database to change some value and
corrupting some constraint (that reside in the application) or by an
aside application or a refactoring of the application (e.g., in order
to change the application technology).
Thanks to the PostgreSQL support to many pl languages, you can even
reuse some existing application logic into the database, but it does
not mean this is the smarter choice (performance? OOP vs procedural?).
Of course, as placing business logic into the database makes the
database "code" more complex, it is required to do unit testing on the
code itself (e.g. pgtap).
Finally, another point in being "scared" of using stored procedure is
portability: a lot of frameworks claim to be portable across database
because they use a minimal survival subset of SQL features that are
almost supported on any decent database. Using a stored procedure will
make more complex the portability, since pl procedures need to be
translated from one database to another.

Luca


Re: Why are stored procedures looked on so negatively?

From
Aaron Abreu
Date:
a NON-technical version...

st.procedures and automation are great...

but...
sounds like everybody is dancing around the main theme..
so lets say it....
that dreaded word that developers and DBA's cring to hear...
the one part of our job that we all hate...

DOCUMENTATION !!!!!

My worst fear is simply this...
having to fix something somebody else wrote.. and they
are not there anymore..... and the only documentation is the code itself..
been there... on a few occasions just had to write something new...






On Tue, Jul 23, 2013 at 7: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 don't understand this argument. 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.

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?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
####
Aaron Abreu, Systems Consultant
Bay District Schools, Panama City,  FL
Office:  (850) 767-4288
>>FOCUS Student system support
>>IRIS phone alert system support
ABREUAL@bay.k12.fl.us
The information contained in this message may be privileged and confidential and protected
from disclosure. If the reader of this message is not the intended recipient, or an 
employee or agent responsible for delivering this message to the intended recipient, 
you are hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication in error, 
please notify us immediately by replying to the message and deleting it from your 
computer. Under Florida law, e-mail addresses are public records. If you do not want 
your e-mail address released in response to a public-records request, do not send 
electronic mail to this entity. Instead, contact this office by phone or in writing.

Re: Why are stored procedures looked on so negatively?

From
Vincenzo Romano
Date:
2013/7/24 Aaron Abreu <abreual@bay.k12.fl.us>:
> a NON-technical version...
>
> st.procedures and automation are great...
>
> but...
> sounds like everybody is dancing around the main theme..
> so lets say it....
> that dreaded word that developers and DBA's cring to hear...
> the one part of our job that we all hate...
>
> DOCUMENTATION !!!!!
>
> My worst fear is simply this...
> having to fix something somebody else wrote.. and they
> are not there anymore..... and the only documentation is the code itself..
> been there... on a few occasions just had to write something new...
>
>
>
>
>
>
> On Tue, Jul 23, 2013 at 7: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 don't understand this argument. 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.
>>
>> 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?


The same applies to tables, triggers and so on.
The point is that you need to spend time in any case, only that most
of us feel more comfortable with tables than with procedures.
But a (modern) database is a mix of tables, grants, schemas and, of
course, stored procedures.
We need to cope with all of them.

And, please, don't top-post.


Re: Why are stored procedures looked on so negatively?

From
Vincenzo Romano
Date:
2013/7/24 Aaron Abreu <abreual@bay.k12.fl.us>:
> a NON-technical version...
>
> st.procedures and automation are great...
>
> but...
> sounds like everybody is dancing around the main theme..
> so lets say it....
> that dreaded word that developers and DBA's cring to hear...
> the one part of our job that we all hate...
>
> DOCUMENTATION !!!!!
>
> My worst fear is simply this...
> having to fix something somebody else wrote.. and they
> are not there anymore..... and the only documentation is the code itself..
> been there... on a few occasions just had to write something new...
>
>
>
>
>
>
> On Tue, Jul 23, 2013 at 7: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 don't understand this argument. 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.
>>
>> 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?


The same applies to tables, triggers and so on.
The point is that you need to spend time in any case, only that most
of us feel more comfortable with tables than with procedures.
But a (modern) database is a mix of tables, grants, schemas and, of
course, stored procedures.
We need to cope with all of them.

And, please, don't top-post.


Re: Why are stored procedures looked on so negatively?

From
Bèrto ëd Sèra
Date:
Hi,

> In other words an API in the database.
+1. People code apps and then disappear, because once the development is over they are not available in the company any more. And each thing you hardwire in the app becomes a stopper. Meanwhile, every company will have at least one DBA, who can manage/upgrade stuff in the DB. This is especially true now that most stuff gets done for phones, and each phone family needs the same stuff to be redeveloped and maintained over and over again, with an extremely huge risk of inconsistent behaviours.

Coding in the app is simply not cost-effective.

My 2 p.

Bèrto


On 24 July 2013 01:40, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 07/23/2013 05:29 PM, Some Developer 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 don't understand this argument. 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.

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?

Personally I figure the arguments for and against are closely correlated with where on the development chain you are, and are tied in with job security. If you are an app developer than it is in your interest to have code in the app, if you are a database developer in the database. Me, I am tend to go with your argument about keeping procedures, where appropriate, in the database for the reasons you state. In other words an API in the database.





--
Adrian Klaver
adrian.klaver@gmail.com



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

Re: Why are stored procedures looked on so negatively?

From
Merlin Moncure
Date:
On Wed, Jul 24, 2013 at 7:52 AM, Aaron Abreu <abreual@bay.k12.fl.us> wrote:
> a NON-technical version...
>
> st.procedures and automation are great...
>
> but...
> sounds like everybody is dancing around the main theme..
> so lets say it....
> that dreaded word that developers and DBA's cring to hear...
> the one part of our job that we all hate...
>
> DOCUMENTATION !!!!!

urk.  your typical java programmer isn't any more likely to write
documentation and unit tests than your typical database developer.
sql is very at least somewhat self documenting; I'd rather trawl
through someone else's sql than just about any other language.

stored procedures also tend to be very robust, especially if you avoid
excessive use of variables and loops; they are tightly coupled with
the database transaction environment: errors roll back ALL DATA
STRUCTURES as well as the execution point to a known good place.  also
the mvcc locking model is very clean vs your typical threaded drek.

merlin


Re: Why are stored procedures looked on so negatively?

From
"Gauthier, Dave"
Date:
I find stored procedures to be a God-send.  The alternative, external code, is the risky, difficult and often poorer
performingapproach to the problems sp's solve.   What better way to interact programatically with your database than
WITHyour database? 

The only people that I see frown upon them don't understand them, are afraid of them, and so find ways to justify their
viewsabout them in negative terms.  I suppose that's human nature.  But once they get "turned on" to stored procedures,
theirviews change.   

As for selling sp's to them, especially if they are management, there's nothing more convincing than a demo.  And a
realgood way to demo their effectiveness is through a remote connection, preferrably across a time zone or two, where
thetask involves many (hundreds of thousands) of queries that the external script would have to do one at a time, over
thenet.  The sp would just run them inside as part of the sp call, locally, in a tiny fraction of the time.   



-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Some Developer
Sent: Tuesday, July 23, 2013 8:29 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Why are stored procedures looked on so negatively?

I've done quite a bit of reading on stored procedures recently and the consensus seems to be that you shouldn't use
themunless you really must. 

I don't understand this argument. If you implement all of your logic in the application then you need to make a network
requestto the database server, return the required data from the database to the app server, do the processing and then
returnthe results. A stored procedure is going to be a lot faster than that even if you just take away network latency
/transfer time. 

I'm in the middle of building a database and was going to make extensive use of stored procedures and trigger functions
becauseit makes more sense for the actions to happen at the database layer rather than in the app layer. 

Should I use them or not?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Why are stored procedures looked on so negatively?

From
Some Developer
Date:
On 24/07/13 14:21, Gauthier, Dave wrote:
> I find stored procedures to be a God-send.  The alternative, external code, is the risky, difficult and often poorer
performingapproach to the problems sp's solve.   What better way to interact programatically with your database than
WITHyour database? 
>
> The only people that I see frown upon them don't understand them, are afraid of them, and so find ways to justify
theirviews about them in negative terms.  I suppose that's human nature.  But once they get "turned on" to stored
procedures,their views change. 
>
> As for selling sp's to them, especially if they are management, there's nothing more convincing than a demo.  And a
realgood way to demo their effectiveness is through a remote connection, preferrably across a time zone or two, where
thetask involves many (hundreds of thousands) of queries that the external script would have to do one at a time, over
thenet.  The sp would just run them inside as part of the sp call, locally, in a tiny fraction of the time. 
>
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Some Developer
> Sent: Tuesday, July 23, 2013 8:29 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Why are stored procedures looked on so negatively?
>
> I've done quite a bit of reading on stored procedures recently and the consensus seems to be that you shouldn't use
themunless you really must. 
>
> I don't understand this argument. If you implement all of your logic in the application then you need to make a
networkrequest to the database server, return the required data from the database to the app server, do the processing
andthen 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. 
>
> I'm in the middle of building a database and was going to make extensive use of stored procedures and trigger
functionsbecause 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?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Thank you all for the responses. I feel better about making use of them now.

Now for one final question: I was planning on using plpython2u to write
my stored procedures since Python is a language I am very familiar with.
I understand that many people would want to use plpgsql instead but
it'll be quicker for me to do it in Python.

Will there be much of a performance difference between the two at all?
Are there any very convincing arguments that will make me use plpgsql
instead or does it not really matter?


Re: Why are stored procedures looked on so negatively?

From
Adrian Klaver
Date:
On 07/24/2013 06:31 AM, Some Developer wrote:

>>
>
> Thank you all for the responses. I feel better about making use of them
> now.
>
> Now for one final question: I was planning on using plpython2u to write
> my stored procedures since Python is a language I am very familiar with.
> I understand that many people would want to use plpgsql instead but
> it'll be quicker for me to do it in Python.
>
> Will there be much of a performance difference between the two at all?
> Are there any very convincing arguments that will make me use plpgsql
> instead or does it not really matter?

I have faced this choice also. What I found is that plpgsql tends to be
more succinct for doing database operations, probably by virtue of being
an 'extended' sql. plpythonu has developed more capabilities over time
but there is still a translation portion, Python --> SQL --> Python. You
will find that you will end up using both.

>
>


--
Adrian Klaver
adrian.klaver@gmail.com


Re: Why are stored procedures looked on so negatively?

From
Merlin Moncure
Date:
On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
<someukdeveloper@gmail.com> wrote:
> On 24/07/13 14:21, Gauthier, Dave wrote:
>>
>> I find stored procedures to be a God-send.  The alternative, external
>> code, is the risky, difficult and often poorer performing approach to the
>> problems sp's solve.   What better way to interact programatically with your
>> database than WITH your database?
>>
>> The only people that I see frown upon them don't understand them, are
>> afraid of them, and so find ways to justify their views about them in
>> negative terms.  I suppose that's human nature.  But once they get "turned
>> on" to stored procedures, their views change.
>>
>> As for selling sp's to them, especially if they are management, there's
>> nothing more convincing than a demo.  And a real good way to demo their
>> effectiveness is through a remote connection, preferrably across a time zone
>> or two, where the task involves many (hundreds of thousands) of queries that
>> the external script would have to do one at a time, over the net.  The sp
>> would just run them inside as part of the sp call, locally, in a tiny
>> fraction of the time.
>>
>>
>>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org
>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Some Developer
>> Sent: Tuesday, July 23, 2013 8:29 PM
>> To: pgsql-general@postgresql.org
>> Subject: [GENERAL] Why are stored procedures looked on so negatively?
>>
>> 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 don't understand this argument. 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.
>>
>> 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?
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
>> changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> Thank you all for the responses. I feel better about making use of them now.
>
> Now for one final question: I was planning on using plpython2u to write my
> stored procedures since Python is a language I am very familiar with. I
> understand that many people would want to use plpgsql instead but it'll be
> quicker for me to do it in Python.
>
> Will there be much of a performance difference between the two at all? Are
> there any very convincing arguments that will make me use plpgsql instead or
> does it not really matter?

plpgsql is generally the fastest/easiest language for a certain (but
important) class of operations. it runs closer to the SQL execution
engine and automatically plans all your queries (which can be a pretty
big deal for certain types of coding).  all error handling is native
(so that you don't have to catch a python exception and peek into the
sql aspects of it for appropriate handling) which is a bigger deal
than it appears on the surface.  also it's good to exercise your SQL
skills.

whichever way you go, good procedure practices generally involve
approximating scripted SQL to the extent possible.   also you should
separate routines that read from and write to the database (and try to
keep as much code as possible in the read side).  make sure to mark
routines immutable/stable as appropriate.   another underutilized
function decoration is STRICT -- it's very fast when it fires and can
save you a lot of debugging headaches.

merlin


Re: Why are stored procedures looked on so negatively?

From
Some Developer
Date:
On 24/07/2013 14:58, Merlin Moncure wrote:
> On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
> <someukdeveloper@gmail.com> wrote:
>> On 24/07/13 14:21, Gauthier, Dave wrote:
>>>
>>> I find stored procedures to be a God-send.  The alternative, external
>>> code, is the risky, difficult and often poorer performing approach to the
>>> problems sp's solve.   What better way to interact programatically with your
>>> database than WITH your database?
>>>
>>> The only people that I see frown upon them don't understand them, are
>>> afraid of them, and so find ways to justify their views about them in
>>> negative terms.  I suppose that's human nature.  But once they get "turned
>>> on" to stored procedures, their views change.
>>>
>>> As for selling sp's to them, especially if they are management, there's
>>> nothing more convincing than a demo.  And a real good way to demo their
>>> effectiveness is through a remote connection, preferrably across a time zone
>>> or two, where the task involves many (hundreds of thousands) of queries that
>>> the external script would have to do one at a time, over the net.  The sp
>>> would just run them inside as part of the sp call, locally, in a tiny
>>> fraction of the time.
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: pgsql-general-owner@postgresql.org
>>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Some Developer
>>> Sent: Tuesday, July 23, 2013 8:29 PM
>>> To: pgsql-general@postgresql.org
>>> Subject: [GENERAL] Why are stored procedures looked on so negatively?
>>>
>>> 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 don't understand this argument. 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.
>>>
>>> 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?
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
>>> changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>> Thank you all for the responses. I feel better about making use of them now.
>>
>> Now for one final question: I was planning on using plpython2u to write my
>> stored procedures since Python is a language I am very familiar with. I
>> understand that many people would want to use plpgsql instead but it'll be
>> quicker for me to do it in Python.
>>
>> Will there be much of a performance difference between the two at all? Are
>> there any very convincing arguments that will make me use plpgsql instead or
>> does it not really matter?
>
> plpgsql is generally the fastest/easiest language for a certain (but
> important) class of operations. it runs closer to the SQL execution
> engine and automatically plans all your queries (which can be a pretty
> big deal for certain types of coding).  all error handling is native
> (so that you don't have to catch a python exception and peek into the
> sql aspects of it for appropriate handling) which is a bigger deal
> than it appears on the surface.  also it's good to exercise your SQL
> skills.
>
> whichever way you go, good procedure practices generally involve
> approximating scripted SQL to the extent possible.   also you should
> separate routines that read from and write to the database (and try to
> keep as much code as possible in the read side).  make sure to mark
> routines immutable/stable as appropriate.   another underutilized
> function decoration is STRICT -- it's very fast when it fires and can
> save you a lot of debugging headaches.
>
> merlin
>

Thanks. I'll be sure to bear that information in mind.



Re: Why are stored procedures looked on so negatively?

From
Jeff Janes
Date:
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).

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'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


Re: Why are stored procedures looked on so negatively?

From
Some Developer
Date:
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.


Re: Why are stored procedures looked on so negatively?

From
Luca Ferrari
Date:
On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
<someukdeveloper@gmail.com> wrote:
> The added advantage of removing load from the app servers so they can
> actually deal with serving the app is a bonus.

Uhm...I don't know what application you are developing, but I don't
buy your explaination.
While it is true that you are moving CPU cycles from the application
server to the database server, you will probably end with the
application server waiting for the database to acknowledge (and
therefore not serving requests) and usually the computation is not
that heavy for an online transaction (it would be better to do it as
batch if that is really heavy). Therefore this is not an advantage for
me.
Again, the only reason to use database facilities (like stored
procedures) is to arm the database so that even a different
application/connection/user will interact following as much business
rules as possible.

Moreover, please also note that one reason developers tend to avoid
database facilities is that they are using some kind of
stack/orm/automagical library that does not allow the usage of deep
features in sake of portability.



>
> 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.
>

I don't understand the "time saving" argument: you have to implement
the logic either in the application or the database, so let's say the
time of the implementation is the same. The only advantage of the
database is the code reuse. But take into account that there are
drawbacks, like debugging that is not always so simple.

Luca


Re: Why are stored procedures looked on so negatively?

From
Vincenzo Romano
Date:
2013/7/25 Luca Ferrari <fluca1978@infinito.it>:
> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
> <someukdeveloper@gmail.com> wrote:
>> The added advantage of removing load from the app servers so they can
>> actually deal with serving the app is a bonus.
>
> Uhm...I don't know what application you are developing, but I don't
> buy your explaination.
> While it is true that you are moving CPU cycles from the application
> server to the database server, you will probably end with the
> application server waiting for the database to acknowledge (and
> therefore not serving requests) and usually the computation is not
> that heavy for an online transaction (it would be better to do it as
> batch if that is really heavy). Therefore this is not an advantage for
> me.
> Again, the only reason to use database facilities (like stored
> procedures) is to arm the database so that even a different
> application/connection/user will interact following as much business
> rules as possible.
>
> Moreover, please also note that one reason developers tend to avoid
> database facilities is that they are using some kind of
> stack/orm/automagical library that does not allow the usage of deep
> features in sake of portability.
>
>
>
>>
>> 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.
>>
>
> I don't understand the "time saving" argument: you have to implement
> the logic either in the application or the database, so let's say the
> time of the implementation is the same. The only advantage of the
> database is the code reuse. But take into account that there are
> drawbacks, like debugging that is not always so simple.
>
> Luca

I could be wrong, but the main advantage you gain by using stored
procedures is what Luca says: unique data access interface.
Just that.
I don't think you'll save a single CPU cycle by moving logic from
"application" to "DB" (or the other way around).
That logic need to be implemented (and run) on either part.
The only saving would happen if you push the logic straight to the client.
And keep in mind than not all PLs are the same and have the same effectiveness.
So, for example, instead of INSERTing rows from program, you could
SELECT from a stored procedure which will do the INSERT possibly with
the very same checks you would do in the application. Only put
together in a single place. The stored procedure.

Finally, I fear this is kind of "religion" war. So feel free to follow
any or establish your own.

The bottom line here is: PLs are OK. It just depends on what you do and how.


Re: Why are stored procedures looked on so negatively?

From
Pavel Stehule
Date:
2013/7/25 Vincenzo Romano <vincenzo.romano@notorand.it>:
> 2013/7/25 Luca Ferrari <fluca1978@infinito.it>:
>> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
>> <someukdeveloper@gmail.com> wrote:
>>> The added advantage of removing load from the app servers so they can
>>> actually deal with serving the app is a bonus.
>>
>> Uhm...I don't know what application you are developing, but I don't
>> buy your explaination.
>> While it is true that you are moving CPU cycles from the application
>> server to the database server, you will probably end with the
>> application server waiting for the database to acknowledge (and
>> therefore not serving requests) and usually the computation is not
>> that heavy for an online transaction (it would be better to do it as
>> batch if that is really heavy). Therefore this is not an advantage for
>> me.
>> Again, the only reason to use database facilities (like stored
>> procedures) is to arm the database so that even a different
>> application/connection/user will interact following as much business
>> rules as possible.
>>
>> Moreover, please also note that one reason developers tend to avoid
>> database facilities is that they are using some kind of
>> stack/orm/automagical library that does not allow the usage of deep
>> features in sake of portability.
>>
>>
>>
>>>
>>> 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.
>>>
>>
>> I don't understand the "time saving" argument: you have to implement
>> the logic either in the application or the database, so let's say the
>> time of the implementation is the same. The only advantage of the
>> database is the code reuse. But take into account that there are
>> drawbacks, like debugging that is not always so simple.
>>
>> Luca
>
> I could be wrong, but the main advantage you gain by using stored
> procedures is what Luca says: unique data access interface.
> Just that.
> I don't think you'll save a single CPU cycle by moving logic from
> "application" to "DB" (or the other way around).
> That logic need to be implemented (and run) on either part.
> The only saving would happen if you push the logic straight to the client.
> And keep in mind than not all PLs are the same and have the same effectiveness.
> So, for example, instead of INSERTing rows from program, you could
> SELECT from a stored procedure which will do the INSERT possibly with
> the very same checks you would do in the application. Only put
> together in a single place. The stored procedure.
>
> Finally, I fear this is kind of "religion" war. So feel free to follow
> any or establish your own.
>
> The bottom line here is: PLs are OK. It just depends on what you do and how.

+1

exactly

Pavel
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: Why are stored procedures looked on so negatively?

From
Some Developer
Date:
On 25/07/13 07:57, Luca Ferrari wrote:
> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
> <someukdeveloper@gmail.com> wrote:
>> The added advantage of removing load from the app servers so they can
>> actually deal with serving the app is a bonus.
>
> Uhm...I don't know what application you are developing, but I don't
> buy your explaination.
> While it is true that you are moving CPU cycles from the application
> server to the database server, you will probably end with the
> application server waiting for the database to acknowledge (and
> therefore not serving requests) and usually the computation is not
> that heavy for an online transaction (it would be better to do it as
> batch if that is really heavy). Therefore this is not an advantage for
> me.
> Again, the only reason to use database facilities (like stored
> procedures) is to arm the database so that even a different
> application/connection/user will interact following as much business
> rules as possible.

You are forgetting that you can execute a query asynchronously using
libpq therefore the app server can continue serving requests whilst the
database server chugs away on its work. You just poll the server every
now and again to see if the work has finished.

> Moreover, please also note that one reason developers tend to avoid
> database facilities is that they are using some kind of
> stack/orm/automagical library that does not allow the usage of deep
> features in sake of portability.
>
>
>
>>
>> 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.
>>
>
> I don't understand the "time saving" argument: you have to implement
> the logic either in the application or the database, so let's say the
> time of the implementation is the same. The only advantage of the
> database is the code reuse. But take into account that there are
> drawbacks, like debugging that is not always so simple.
>
> Luca
>

Time saving was probably the wrong expression to use. The application
has parts written in different languages running on different servers.
It saves time to have the main work done using stored procedures since
they can be shared between the different systems regardless of the
language that particular service was written or the operating system
that it is running on.



Re: Why are stored procedures looked on so negatively?

From
Some Developer
Date:
On 25/07/13 08:14, Vincenzo Romano wrote:
> 2013/7/25 Luca Ferrari <fluca1978@infinito.it>:
>> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
>> <someukdeveloper@gmail.com> wrote:
>>> The added advantage of removing load from the app servers so they can
>>> actually deal with serving the app is a bonus.
>>
>> Uhm...I don't know what application you are developing, but I don't
>> buy your explaination.
>> While it is true that you are moving CPU cycles from the application
>> server to the database server, you will probably end with the
>> application server waiting for the database to acknowledge (and
>> therefore not serving requests) and usually the computation is not
>> that heavy for an online transaction (it would be better to do it as
>> batch if that is really heavy). Therefore this is not an advantage for
>> me.
>> Again, the only reason to use database facilities (like stored
>> procedures) is to arm the database so that even a different
>> application/connection/user will interact following as much business
>> rules as possible.
>>
>> Moreover, please also note that one reason developers tend to avoid
>> database facilities is that they are using some kind of
>> stack/orm/automagical library that does not allow the usage of deep
>> features in sake of portability.
>>
>>
>>
>>>
>>> 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.
>>>
>>
>> I don't understand the "time saving" argument: you have to implement
>> the logic either in the application or the database, so let's say the
>> time of the implementation is the same. The only advantage of the
>> database is the code reuse. But take into account that there are
>> drawbacks, like debugging that is not always so simple.
>>
>> Luca
>
> I could be wrong, but the main advantage you gain by using stored
> procedures is what Luca says: unique data access interface.
> Just that.
> I don't think you'll save a single CPU cycle by moving logic from
> "application" to "DB" (or the other way around).
> That logic need to be implemented (and run) on either part.
> The only saving would happen if you push the logic straight to the client.
> And keep in mind than not all PLs are the same and have the same effectiveness.
> So, for example, instead of INSERTing rows from program, you could
> SELECT from a stored procedure which will do the INSERT possibly with
> the very same checks you would do in the application. Only put
> together in a single place. The stored procedure.
>
> Finally, I fear this is kind of "religion" war. So feel free to follow
> any or establish your own.
>
> The bottom line here is: PLs are OK. It just depends on what you do and how.
>

When I was talking about improving speed I was talking about reducing
load on the app servers by putting more of the work load on the database
server. I know that it won't actually save CPU cycles (one of the
machines has to do it) but it will save load on the app servers. As I
said above using the asynchronous abilities of libpq helps keep the app
servers serving requests whilst the database gets on with its tasks.

In fact the whole design of this application is asynchronous in nature.


Re: Why are stored procedures looked on so negatively?

From
Bèrto ëd Sèra
Date:
Hi,

>the whole design of this application is asynchronous in nature.
Then you'll be MUCH better off with SPs, from an architectural POV, as you can basically design "building blocks" by initially just making SPs that deliver a mock result, and have the entire development of the app server being in dependent on the SQL development. This way none of the branches blocks the other (provided that you can actually freeze the design).

Cheers
Bèrto


On 25 July 2013 09:44, Some Developer <someukdeveloper@gmail.com> wrote:
On 25/07/13 08:14, Vincenzo Romano wrote:
2013/7/25 Luca Ferrari <fluca1978@infinito.it>:
On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
<someukdeveloper@gmail.com> wrote:
The added advantage of removing load from the app servers so they can
actually deal with serving the app is a bonus.

Uhm...I don't know what application you are developing, but I don't
buy your explaination.
While it is true that you are moving CPU cycles from the application
server to the database server, you will probably end with the
application server waiting for the database to acknowledge (and
therefore not serving requests) and usually the computation is not
that heavy for an online transaction (it would be better to do it as
batch if that is really heavy). Therefore this is not an advantage for
me.
Again, the only reason to use database facilities (like stored
procedures) is to arm the database so that even a different
application/connection/user will interact following as much business
rules as possible.

Moreover, please also note that one reason developers tend to avoid
database facilities is that they are using some kind of
stack/orm/automagical library that does not allow the usage of deep
features in sake of portability.




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.


I don't understand the "time saving" argument: you have to implement
the logic either in the application or the database, so let's say the
time of the implementation is the same. The only advantage of the
database is the code reuse. But take into account that there are
drawbacks, like debugging that is not always so simple.

Luca

I could be wrong, but the main advantage you gain by using stored
procedures is what Luca says: unique data access interface.
Just that.
I don't think you'll save a single CPU cycle by moving logic from
"application" to "DB" (or the other way around).
That logic need to be implemented (and run) on either part.
The only saving would happen if you push the logic straight to the client.
And keep in mind than not all PLs are the same and have the same effectiveness.
So, for example, instead of INSERTing rows from program, you could
SELECT from a stored procedure which will do the INSERT possibly with
the very same checks you would do in the application. Only put
together in a single place. The stored procedure.

Finally, I fear this is kind of "religion" war. So feel free to follow
any or establish your own.

The bottom line here is: PLs are OK. It just depends on what you do and how.


When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the work load on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but it will save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app servers serving requests whilst the database gets on with its tasks.

In fact the whole design of this application is asynchronous in nature.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

Re: Why are stored procedures looked on so negatively?

From
V S P
Date:
I do not see why stored procedures are particular better for asynchronous application design.  this can be done, as some pointed before, using standard libraries.
 
Furthermore, while this does not apply to databases that do not burden users with heavy per-cpu costs, for many companies that build software to sell, it is a selling point that your system is light on database CPU utilization. So that your clients are not required to buy grotesquely overpowered DB servers just because your application had put its logic there.
 
Also framework, libraries and general community contributions of source code, code coverage tools --  are much more accessible in general purpose programming languages.
 
 
 
 
 
 
 
On Thu, Jul 25, 2013, at 04:51 AM, Bèrto ëd Sèra wrote:
Hi,
 
>the whole design of this application is asynchronous in nature.
Then you'll be MUCH better off with SPs, from an architectural POV, as you can basically design "building blocks" by initially just making SPs that deliver a mock result, and have the entire development of the app server being in dependent on the SQL development. This way none of the branches blocks the other (provided that you can actually freeze the design).
 
Cheers
Bèrto
 
 
On 25 July 2013 09:44, Some Developer <someukdeveloper@gmail.com> wrote:
 
On 25/07/13 08:14, Vincenzo Romano wrote:
 
2013/7/25 Luca Ferrari <fluca1978@infinito.it>:
 
On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
 
The added advantage of removing load from the app servers so they can
actually deal with serving the app is a bonus.
 
 
Uhm...I don't know what application you are developing, but I don't
buy your explaination.
While it is true that you are moving CPU cycles from the application
server to the database server, you will probably end with the
application server waiting for the database to acknowledge (and
therefore not serving requests) and usually the computation is not
that heavy for an online transaction (it would be better to do it as
batch if that is really heavy). Therefore this is not an advantage for
me.
Again, the only reason to use database facilities (like stored
procedures) is to arm the database so that even a different
application/connection/user will interact following as much business
rules as possible.
 
Moreover, please also note that one reason developers tend to avoid
database facilities is that they are using some kind of
stack/orm/automagical library that does not allow the usage of deep
features in sake of portability.
 
 
 
 
 
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.
 
 
 
I don't understand the "time saving" argument: you have to implement
the logic either in the application or the database, so let's say the
time of the implementation is the same. The only advantage of the
database is the code reuse. But take into account that there are
drawbacks, like debugging that is not always so simple.
 
Luca
 
 
I could be wrong, but the main advantage you gain by using stored
procedures is what Luca says: unique data access interface.
Just that.
I don't think you'll save a single CPU cycle by moving logic from
"application" to "DB" (or the other way around).
That logic need to be implemented (and run) on either part.
The only saving would happen if you push the logic straight to the client.
And keep in mind than not all PLs are the same and have the same effectiveness.
So, for example, instead of INSERTing rows from program, you could
SELECT from a stored procedure which will do the INSERT possibly with
the very same checks you would do in the application. Only put
together in a single place. The stored procedure.
 
Finally, I fear this is kind of "religion" war. So feel free to follow
any or establish your own.
 
The bottom line here is: PLs are OK. It just depends on what you do and how.
 
 
 
When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the work load on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but it will save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app servers serving requests whilst the database gets on with its tasks.
 
In fact the whole design of this application is asynchronous in nature.
 
 
 
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
 
 
 
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
 
 
 
-- 
http://www.fastmail.fm - Faster than the air-speed velocity of an
                          unladen european swallow

Re: Why are stored procedures looked on so negatively?

From
Steve Atkins
Date:
On Jul 25, 2013, at 1:44 AM, Some Developer <someukdeveloper@gmail.com> wrote:
>>
>
> When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the
workload on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but
itwill save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app
serversserving requests whilst the database gets on with its tasks. 
>

App servers don't tend to maintain much global state, so are almost perfectly parallelizable. If you run out of CPU
there,drop another cheap box in the rack. 

Database servers aren't. Once you top out a database server your main options are to replace it with a bigger box
(increasinglyexpensive) or rearchitect the application (even more expensive). 

I'll always put more work on the cheaply scalable app servers if I can reduce the load on the database. Moving code to
thedatabase server for reasons of CPU cost (as opposed to, say, data or business rule consistency) seems an odd
approach.

Cheers,
  Steve



Re: Why are stored procedures looked on so negatively?

From
Pavel Stehule
Date:
2013/7/25 Steve Atkins <steve@blighty.com>:
>
> On Jul 25, 2013, at 1:44 AM, Some Developer <someukdeveloper@gmail.com> wrote:
>>>
>>
>> When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the
workload on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but
itwill save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app
serversserving requests whilst the database gets on with its tasks. 
>>
>
> App servers don't tend to maintain much global state, so are almost perfectly parallelizable. If you run out of CPU
there,drop another cheap box in the rack. 
>
> Database servers aren't. Once you top out a database server your main options are to replace it with a bigger box
(increasinglyexpensive) or rearchitect the application (even more expensive). 
>
> I'll always put more work on the cheaply scalable app servers if I can reduce the load on the database. Moving code
tothe database server for reasons of CPU cost (as opposed to, say, data or business rule consistency) seems an odd
approach.

It is false idea.

What is a stored procedure? A few procedural construct and lot of SQL
queries. A procedural code is +/- zero overhead - significantly more
expensive are SQL queries - and these queries you will send from
procedures and from application server too. I can say so good written
stored procedures has zero negative effect on server performance -
more it has positive effect due elimination network latency it
decrease lock times.

Stored procedures is good environment for business logic
implementation or workflow implementation and bad for expensive
numeric calculations - and if you respect this rule, then stored
procedures will be faster always with less server load.

Regards

Pavel


>
> Cheers,
>   Steve
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: Why are stored procedures looked on so negatively?

From
"Gauthier, Dave"
Date:
I have a DB that relies heavily on recursive stored procedures that tap reflexive tables that store hierarchical data.
Theseprocedures are called from queries and return record streams.  Temp tables are used to store collected data as the
procedureruns up/down the hierarchy.  And many other stored procedurea are called by the recursive procedure along the
wayto identify the correct hier path(s) to follow.  These things run amazingly fast.  Attempts others haev made to do
thisin perl-DBI were shaping up to be so slow that they were discarded early on, merely on that issue alone.  
 

In another DB, I use stored procedures in triggers, again, recursive and working with hierarchy, only this time,
inserting/updating/deletingrecords along the way.  Again, fast as compared with the external "competition", but the
matterof data integrity is another winner here because it makes it impossible for users at the SQL prompt to screw up
thehierarchies with singular DML calls.  The hierarchies end up being correct by construction.  Ironically, a different
grouptried to implement this without triggers, sps or even PG (they used MySQL).  And it's been nothing but
headaches...poor performance and broken hierarchies all the time.  When they asked me to port my PG triggers/sps to
MySQL,I hit walls that involved... 1) inability to defer constraint checking (for foreign key constraints),  2)
inabilityto leave cursors open in recursive calls (globally vs locally scoped cursors), and no support for "record"
datatypes.    
 

For me, the question is more along the lines of why I can't or shouldn't use stored procedures over external code, the
defaultbeing sps.
 

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Pavel Stehule
Sent: Thursday, July 25, 2013 11:09 AM
To: Steve Atkins
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why are stored procedures looked on so negatively?

2013/7/25 Steve Atkins <steve@blighty.com>:
>
> On Jul 25, 2013, at 1:44 AM, Some Developer <someukdeveloper@gmail.com> wrote:
>>>
>>
>> When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the
workload on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but
itwill save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app
serversserving requests whilst the database gets on with its tasks.
 
>>
>
> App servers don't tend to maintain much global state, so are almost perfectly parallelizable. If you run out of CPU
there,drop another cheap box in the rack.
 
>
> Database servers aren't. Once you top out a database server your main options are to replace it with a bigger box
(increasinglyexpensive) or rearchitect the application (even more expensive).
 
>
> I'll always put more work on the cheaply scalable app servers if I can reduce the load on the database. Moving code
tothe database server for reasons of CPU cost (as opposed to, say, data or business rule consistency) seems an odd
approach.

It is false idea.

What is a stored procedure? A few procedural construct and lot of SQL queries. A procedural code is +/- zero overhead -
significantlymore expensive are SQL queries - and these queries you will send from procedures and from application
servertoo. I can say so good written stored procedures has zero negative effect on server performance - more it has
positiveeffect due elimination network latency it decrease lock times.
 

Stored procedures is good environment for business logic implementation or workflow implementation and bad for
expensivenumeric calculations - and if you respect this rule, then stored procedures will be faster always with less
serverload.
 

Regards

Pavel


>
> Cheers,
>   Steve
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To 
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Why are stored procedures looked on so negatively?

From
Sébastien Lorion
Date:
On Thu, Jul 25, 2013 at 4:41 AM, Some Developer <someukdeveloper@gmail.com> wrote:
You are forgetting that you can execute a query asynchronously using libpq therefore the app server can continue serving requests whilst the database server chugs away on its work. You just poll the server every now and again to see if the work has finished.

I think another option is to use some sort of pub/sub architecture using a messaging server such as RabbitMQ. It would at least allow you to avoid/reduce the locking in the database caused by the remote calls in triggers. It would also allow you to scale out the app servers instead of scaling up the database. That said, depending on your load, it might be overkill.

Sébastien

Re: Why are stored procedures looked on so negatively?

From
Neil Tiffin
Date:
On Jul 23, 2013, at 7: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
themunless you really must. 

Application architecture is a specific software engineering discipline.  These types of generalizations come from
coderswho don't really understand application architecture and/or databases.  There are specific reasons to put code in
thedatabase server, application middleware, or the application.  To make this decision, much more must be known that
whathas been presented in this thread. 

For example, if you want to maintain data integrity, then you really want to use very specific table definitions with
foreignkeys, defaults, and constraints.  While this is not related to stored procedures, application coders try to shy
awayfrom these (like they do stored procedures) because it makes working with the database harder.  It forces the data
tobe correct before it comes into the database. When foreign keys, defaults, and constraints are not enough to ensure
dataintegrity then stored procedures should be used.  The question is, how important is your data and how much time do
youwant to spend correcting it after it enters the database? 

The next reason is performance.  Stored procedures can in certain circumstances dramatically increase or decrease
performanceof both the client and the server, network traffic, and application response time.  But which one is most
importantin your application?  The consensus does not know.  The best practice depends on the type of multitasking the
applicationis performing, the type of client, the client coding environment, the locations of the data being processed,
thelocking requirements, the concurrency requirements, the capacity of the servers and clients, the network topology,
theexpected response time for the activity, etc.  It is not at all uncommon to think that a stored procedure should be
inthe database server and to have performance testing show that it is better in the application and vice versa.  Keep
inmind that as the database becomes loaded, these performance issues may change and any decisions you make on a
developmentdatabase with only partial data may not prove out in the final application. 

There may also be reasons to normalize/denormalize data in the database, but present a different view to the
application. This should, if done correctly, make the application code simpler to maintain and understand.  What is
yoursupport experience level?  No experienced DBAs, this is probably a bad idea.  Relatively inexperienced application
coders,this is probably a really good idea. 

Sophisticated applications may even have more than one database server.  One update server and multiple read only
serversis very common in the environments I work in.  Since the update server is not burdened by providing all of the
readonly data, it has much more capacity to handle stored procedures.  Some of our environments see 80 or 90% of the
loadas read only.  This is the network topology part. 

Another example, if the result of a procedure is one number, but requires 15 columns, from 200 rows the question is, is
itfaster to do it on the server and only put one resulting number back on the network, or should the system get all 15
columnstimes 200 rows worth of data and put that on the network for the client to analyze?  The answer is, well it
depends? Well, maybe not for this example, but hopefully you get the point.  Now if part of the procedure requires data
thatcomes from a GUI table or user entered data that only resides in the application, then the situation changes. 

Wherever you put the code, you should have specific reasons for doing so and for high performance applications it is
notappropriate to generalize that all the code should go exclusively into the database or the app. 

Neil

Re: Why are stored procedures looked on so negatively?

From
Kevin Grittner
Date:
Neil Tiffin <neilt@neiltiffin.com> wrote:
> 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.
>
> Application architecture is a specific software engineering discipline.  These
> types of generalizations come from coders who don't really understand
> application architecture and/or databases.  There are specific reasons to put
> code in the database server, application middleware, or the application.  To
> make this decision, much more must be known that what has been presented in this
> thread.

+1

> For example, if you want to maintain data integrity, then you really want to use
> very specific table definitions with foreign keys, defaults, and constraints.
> While this is not related to stored procedures, application coders try to shy
> away from these (like they do stored procedures) because it makes working with
> the database harder.  It forces the data to be correct before it comes into the
> database. When foreign keys, defaults, and constraints are not enough to ensure
> data integrity then stored procedures should be used.  The question is, how
> important is your data and how much time do you want to spend correcting it
> after it enters the database?

Agreed.

> The next reason is performance.

I'm going to skip the rest of this well-reasoned and well-written
response to give just a couple data points on this.

When working as a consultant, one client was doing everything
client-side and engaged me to fix some performance problems.  In
one case a frequently run query was taking two minutes.  As a
stored procedure the correct results were returned in two seconds.
This same client had a report which ran for 72 hours.  A stored
procedure was able to return the correct data in 2.5 minutes,
although it took another 10 minutes for the client side to process
it into the output format.

Stored procedures are not a panacea, however.  Writing in a
declarative format is, in my experience, much more important.  I
saw one case where a SQL procedure written in imperative form,
navigating through linkages a row at a time, was on pace to
complete in over a year.  Rewritten in declarative form it ran in a
few minutes.  As a side benefit, the declarative form is usually
10% to 20% the number of lines of code, and less buggy.  For
retrieval of complex data sets, the big thing is to learn to write
SQL which specifies *what you want* rather then trying to specify
*how to get it*.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Why are stored procedures looked on so negatively?

From
Gavin Flower
Date:
On 02/08/13 08:24, Kevin Grittner wrote:
[...]
> When working as a consultant, one client was doing everything
> client-side and engaged me to fix some performance problems.  In one
> case a frequently run query was taking two minutes.  As a stored
> procedure the correct results were returned in two seconds. This same
> client had a report which ran for 72 hours.  A stored procedure was
> able to return the correct data in 2.5 minutes, although it took
> another 10 minutes for the client side to process it into the output
> format. Stored procedures are not a panacea, however.  Writing in a
> declarative format is, in my experience, much more important.  I saw
> one case where a SQL procedure written in imperative form, navigating
> through linkages a row at a time, was on pace to complete in over a
> year.  Rewritten in declarative form it ran in a few minutes.  As a
> side benefit, the declarative form is usually 10% to 20% the number of
> lines of code, and less buggy.  For retrieval of complex data sets,
> the big thing is to learn to write SQL which specifies *what you want*
> rather then trying to specify *how to get it*. -- Kevin Grittner EDB:
> http://www.enterprisedb.com The Enterprise PostgreSQL Company

Trust the Planner, Luke!
(Apologies to Star Wars)

Very informative, learnt more in the above, and omitted text, than I
have for a long while - certainly clarified my ideas on the subject.


Cheers,
Gavin


Re: Why are stored procedures looked on so negatively?

From
Chris Travers
Date:
Here's my $0.02

Stored procedures have a bunch of problems historically.  Part of this is because the interface traditionally is pretty spartan, and partly because some people take them too far.

The first issue is that if you have a stored procedure which takes 2 arguments and you need to extend it to three, then you have to change every call in the calling application.  This can create a maintenance problem. Variadic functions help somewhat but there are limits to what a variadic function can do here.  The programs and frameworks I write rely very heavily on argument name and data type detection to rewrite calls dynamically, but that has tradeoffs as well.   In general though I think that those tradeoffs are worth it and stored procedures are very, very useful.

The second issue is simply,  just because something can go in the database doesn't mean it should.  In general people start doing things like sending email from the backend and this usually creates more problems than it solves.  The best approach is to see stored procedures as a way to encapsulate the data behind a service-oriented API (like the NoSQL folks advocate ;-) ).  Hope this helps.


--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

Re: Why are stored procedures looked on so negatively?

From
Merlin Moncure
Date:
On Fri, Aug 2, 2013 at 1:49 AM, Chris Travers <chris.travers@gmail.com> wrote:
> Here's my $0.02
>
> Stored procedures have a bunch of problems historically.  Part of this is
> because the interface traditionally is pretty spartan, and partly because
> some people take them too far.
>
> The first issue is that if you have a stored procedure which takes 2
> arguments and you need to extend it to three, then you have to change every
> call in the calling application.  This can create a maintenance problem.
> Variadic functions help somewhat but there are limits to what a variadic
> function can do here.

This is true of most popular languages.  The other defenses are
default arguments (use very sparingly), overloading, and named
parameter arguments.  If you're writing library routines that need to
accommodate a lot of behaviors, named arguments + use of defaults is a
pretty neat way to go.

merlin


Re: Why are stored procedures looked on so negatively?

From
Craig Ringer
Date:
On 08/02/2013 09:18 PM, Merlin Moncure wrote:
> On Fri, Aug 2, 2013 at 1:49 AM, Chris Travers <chris.travers@gmail.com> wrote:
>> Here's my $0.02
>>
>> Stored procedures have a bunch of problems historically.  Part of this is
>> because the interface traditionally is pretty spartan, and partly because
>> some people take them too far.
>>
>> The first issue is that if you have a stored procedure which takes 2
>> arguments and you need to extend it to three, then you have to change every
>> call in the calling application.  This can create a maintenance problem.
>> Variadic functions help somewhat but there are limits to what a variadic
>> function can do here.
>
> This is true of most popular languages.

I think part of the issue is that people tend to consider stored
procedures part of the application's internal implementation where you
just change all the call sites when you change the function.

Normally stored proc are really more like a library API - something
that's a bit of a pain to change due to asynchronous updates of apps and
interface, multiple interface users, etc.

If you think about them that way the question "should this be done in
apps or in a stored proc" must be asked for each individual procedure.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Re: Why are stored procedures looked on so negatively?

From
Chris Travers
Date:



On Sun, Aug 4, 2013 at 7:01 PM, Craig Ringer <craig@2ndquadrant.com> wrote:


I think part of the issue is that people tend to consider stored
procedures part of the application's internal implementation where you
just change all the call sites when you change the function.

Normally stored proc are really more like a library API - something
that's a bit of a pain to change due to asynchronous updates of apps and
interface, multiple interface users, etc.

I think the above is just about exactly right.  Also the fact is that since this is communication across a network usually, asynchronous updates of apps can be more or less a given. 

If you think about them that way the question "should this be done in
apps or in a stored proc" must be asked for each individual procedure.

I would actually say it is worth stepping back from that and asking "what do I want to get out of stored procedures anyway?" and building logic in the application to make sure that happens.

For example, in LedgerSMB, we adopted a stored procedure-centric approach.  We decided to follow certain conventions in argument naming, and have the application look up the arguments before the procedure call.  Thus if the function is redefined, the new version is used, and the API discovered at call time.

Each approach has tradeoffs however.  Our approach works great for what we do with it, but it has some significant costs including the fact that this approach is incompatible with function overloading since the name is the discovery criteria.

If you have other needs, a different approach may be helpful.   However it really is imperative to sit down and  look at the design questions.
--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.