Why are stored procedures looked on so negatively?

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 <someukdeveloper@gmail.com>
Sender: pgsql-general-owner@postgresql.orgDate: Wed, 24 Jul 2013 01:29:14
To: <pgsql-general@postgresql.org>
Subject: [GENERAL] Why are stored procedures looked on so negatively?

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

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

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

Should I use them or not?

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

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

On 07/23/2013 05:29 PM, Some Developer wrote:

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

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

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

Should I use them or not?

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

--
Adrian Klaver
adrian.klaver@gmail.com

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

On Tue, Jul 23, 2013 at 5:40 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On 07/23/2013 05:29 PM, Some Developer wrote:

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

Should I use them or not?

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

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

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

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

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

On Tue, Jul 23, 2013 at 06:55:56PM -0600, John Meyer wrote:

are accessing your database at one time? And most importantly, what
are you best at?

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

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

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

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

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

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

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

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

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

On Wed, Jul 24, 2013 at 2:29 AM, Some Developer
<someukdeveloper@gmail.com> wrote:

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

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

Luca

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

a NON-technical version...

st.procedures and automation are great...

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

DOCUMENTATION !!!!!

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

On Tue, Jul 23, 2013 at 7:29 PM, Some Developer
<someukdeveloper@gmail.com>wrote:

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

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

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

Should I use them or not?

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

--
*####*
Aaron Abreu, Systems Consultant
Bay District Schools, Panama City, FL
Office: * (850) 767-4288
*>>FOCUS Student system support

IRIS phone alert system support

ABREUAL@bay.k12.fl.us

The information contained in this message may be privileged and confidential and protected
from disclosure. If the reader of this message is not the intended recipient, or an
employee or agent responsible for delivering this message to the intended recipient,
you are hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and deleting it from your
computer. Under Florida law, e-mail addresses are public records. If you do not want
your e-mail address released in response to a public-records request, do not send
electronic mail to this entity. Instead, contact this office by phone or in writing.

2013/7/24 Aaron Abreu <abreual@bay.k12.fl.us>:

a NON-technical version...

st.procedures and automation are great...

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

DOCUMENTATION !!!!!

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

On Tue, Jul 23, 2013 at 7:29 PM, Some Developer <someukdeveloper@gmail.com>
wrote:

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

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

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

Should I use them or not?

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

And, please, don't top-post.

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

2013/7/24 Aaron Abreu <abreual@bay.k12.fl.us>:

a NON-technical version...

st.procedures and automation are great...

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

DOCUMENTATION !!!!!

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

On Tue, Jul 23, 2013 at 7:29 PM, Some Developer <someukdeveloper@gmail.com>
wrote:

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

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

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

Should I use them or not?

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

And, please, don't top-post.

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

Hi,

In other words an API in the database.

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

Coding in the app is simply not cost-effective.

My 2 p.

Bèrto

On 24 July 2013 01:40, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 07/23/2013 05:29 PM, Some Developer wrote:

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

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

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

Should I use them or not?

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

--
Adrian Klaver
adrian.klaver@gmail.com

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

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

On Wed, Jul 24, 2013 at 7:52 AM, Aaron Abreu <abreual@bay.k12.fl.us> wrote:

a NON-technical version...

st.procedures and automation are great...

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

DOCUMENTATION !!!!!

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

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

merlin

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

I find stored procedures to be a God-send. The alternative, external code, is the risky, difficult and often poorer performing approach to the problems sp's solve. What better way to interact programatically with your database than WITH your database?

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

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

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

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

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

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

Should I use them or not?

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

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

On 24/07/13 14:21, Gauthier, Dave wrote:

I find stored procedures to be a God-send. The alternative, external code, is the risky, difficult and often poorer performing approach to the problems sp's solve. What better way to interact programatically with your database than WITH your database?

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

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

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

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

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

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

Should I use them or not?

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

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

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

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

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

On 07/24/2013 06:31 AM, Some Developer wrote:

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

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

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

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

--
Adrian Klaver
adrian.klaver@gmail.com

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

On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
<someukdeveloper@gmail.com> wrote:

On 24/07/13 14:21, Gauthier, Dave wrote:

I find stored procedures to be a God-send. The alternative, external
code, is the risky, difficult and often poorer performing approach to the
problems sp's solve. What better way to interact programatically with your
database than WITH your database?

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

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

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

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

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

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

Should I use them or not?

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

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

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

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

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

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

merlin

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

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

On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
<someukdeveloper@gmail.com> wrote:

On 24/07/13 14:21, Gauthier, Dave wrote:

I find stored procedures to be a God-send. The alternative, external
code, is the risky, difficult and often poorer performing approach to the
problems sp's solve. What better way to interact programatically with your
database than WITH your database?

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

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

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

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

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

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

Should I use them or not?

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

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

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

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

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

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

merlin

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

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