Re: [GENERAL] Why are stored procedures looked on so negatively?

[Chris Travers]

On Sun, Aug 4, 2013 at 7:01 PM, Craig Ringer  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.
http://www.efficito.com/learn_more.shtml

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Craig Ringer]

On 08/02/2013 09:18 PM, Merlin Moncure wrote:
> On Fri, Aug 2, 2013 at 1:49 AM, Chris Travers  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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Merlin Moncure]

On Fri, Aug 2, 2013 at 1:49 AM, Chris Travers  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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Chris Travers]

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.
http://www.efficito.com/learn_more.shtml

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Gavin Flower]

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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Kevin Grittner]

Neil Tiffin  wrote:
> 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.
>
> 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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Neil Tiffin]

On Jul 23, 2013, at 7: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.

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.

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?

The next reason is performance.  Stored procedures can in certain circumstances 
dramatically increase or decrease performance of both the client and the 
server, network traffic, and application response time.  But which one is most 
important in your application?  The consensus does not know.  The best practice 
depends on the type of multitasking the application is performing, the type of 
client, the client coding environment, the locations of the data being 
processed, the locking requirements, the concurrency requirements, the capacity 
of the servers and clients, the network topology, the expected response time 
for the activity, etc.  It is not at all uncommon to think that a stored 
procedure should be in the database server and to have performance testing show 
that it is better in the application and vice versa.  Keep in mind that as the 
database becomes loaded, these performance issues may change and any decisions 
you make on a development database 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 your 
support 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 servers is very common in the environments 
I work in.  Since the update server is not burdened by providing all of the 
read only data, it has much more capacity to handle stored procedures.  Some of 
our environments see 80 or 90% of the load as 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 it faster to do it on the server and 
only put one resulting number back on the network, or should the system get all 
15 columns times 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 that comes 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 not appropriate to generalize that all 
the code should go exclusively into the database or the app.

Neil

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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Sébastien Lorion]

On Thu, Jul 25, 2013 at 4:41 AM, Some Developer
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: [GENERAL] Why are stored procedures looked on so negatively?

[Gauthier, Dave]

I have a DB that relies heavily on recursive stored procedures that tap 
reflexive tables that store hierarchical data.  These procedures are called 
from queries and return record streams.  Temp tables are used to store 
collected data as the procedure runs up/down the hierarchy.  And many other 
stored procedurea are called by the recursive procedure along the way to 
identify the correct hier path(s) to follow.  These things run amazingly fast.  
Attempts others haev made to do this in 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/deleting records 
along the way.  Again, fast as compared with the external "competition", but 
the matter of data integrity is another winner here because it makes it 
impossible for users at the SQL prompt to screw up the hierarchies with 
singular DML calls.  The hierarchies end up being correct by construction.  
Ironically, a different group tried 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) inability to leave 
cursors open in recursive calls (globally vs locally scoped cursors), and no 
support for "record" data types.

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

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@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 :
>
> On Jul 25, 2013, at 1:44 AM, Some Developer  wrote:
>>>
>>
>> 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.
>>
>
> 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 (increasingly expensive) 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 the 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


--
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: [GENERAL] Why are stored procedures looked on so negatively?

[Pavel Stehule]

2013/7/25 Steve Atkins :
>
> On Jul 25, 2013, at 1:44 AM, Some Developer  wrote:
>>>
>>
>> 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.
>>
>
> 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 (increasingly expensive) 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 the 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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Steve Atkins]

On Jul 25, 2013, at 1:44 AM, Some Developer  wrote:
>> 
> 
> 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.
> 

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 (increasingly expensive) 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 the database server for reasons of CPU 
cost (as opposed to, say, data or business rule consistency) seems an odd 
approach.

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: [GENERAL] Why are stored procedures looked on so negatively?

[V S P]

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 <[1]someukdevelo...@gmail.com>
wrote:

On 25/07/13 08:14, Vincenzo Romano wrote:

2013/7/25 Luca Ferrari <[2]fluca1...@infinito.it>:



On Thu, Jul 25, 2013 at 2:57 AM, Some Developer

<[3]someukdevelo...@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 ([4]pgsql-general@postgresql.org)
To make changes to your subscription:
[5]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.

References

1. mailto:someukdevelo...@gmail.com
2. mailto:fluca1...@infinito.it
3. mailto:someukdevelo...@

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Bèrto ëd Sèra]

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  wrote:

> On 25/07/13 08:14, Vincenzo Romano wrote:
>
>> 2013/7/25 Luca Ferrari :
>>
>>> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
>>>  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: [GENERAL] Why are stored procedures looked on so negatively?

[Some Developer]

On 25/07/13 08:14, Vincenzo Romano wrote:

2013/7/25 Luca Ferrari :

On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
 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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Some Developer]

On 25/07/13 07:57, Luca Ferrari wrote:

On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
 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.




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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Pavel Stehule]

2013/7/25 Vincenzo Romano :
> 2013/7/25 Luca Ferrari :
>> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
>>  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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Vincenzo Romano]

2013/7/25 Luca Ferrari :
> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
>  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.


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Luca Ferrari]

On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
 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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Some Developer]

On 24/07/13 20:33, Jeff Janes wrote:

On Tue, Jul 23, 2013 at 5: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 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Jeff Janes]

On Tue, Jul 23, 2013 at 5: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 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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Some Developer]

On 24/07/2013 14:58, Merlin Moncure wrote:

On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
 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-ow...@postgresql.org
[mailto:pgsql-general-ow...@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.



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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Merlin Moncure]

On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
 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-ow...@postgresql.org
>> [mailto:pgsql-general-ow...@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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Adrian Klaver]

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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Gauthier, Dave]

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-ow...@postgresql.org 
[mailto:pgsql-general-ow...@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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Merlin Moncure]

On Wed, Jul 24, 2013 at 7:52 AM, Aaron Abreu  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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Bèrto ëd Sèra]

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  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.kla...@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: [GENERAL] Why are stored procedures looked on so negatively?

[Vincenzo Romano]

2013/7/24 Aaron Abreu :
> 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 
> 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.


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Vincenzo Romano]

2013/7/24 Aaron Abreu :
> 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 
> 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.


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Aaron Abreu]

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
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
abre...@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: [GENERAL] Why are stored procedures looked on so negatively?

[Luca Ferrari]

On Wed, Jul 24, 2013 at 2:29 AM, 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 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


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Albe Laurenz]

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

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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Some Developer]

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.



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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Andrew Sullivan]

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
a...@crankycanuck.ca


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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Some Developer]

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.




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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[John Meyer]

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.












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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[Kevin Goess]

On Tue, Jul 23, 2013 at 5:40 PM, Adrian Klaver 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: [GENERAL] Why are stored procedures looked on so negatively?

[Adrian Klaver]

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

Re: [GENERAL] Why are stored procedures looked on so negatively?

[hidayat365]

I presume you're refering to trigger. Since trigger often do something 
automagically :) and it sometime make developer hard to 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 
Sender: pgsql-general-owner@postgresql.orgDate: Wed, 24 Jul 2013 01:29:14 
To: 
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

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

[GENERAL] Why are stored procedures looked on so negatively?

[Some Developer]

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