Skip site navigation (1) Skip section navigation (2)

Re: Stored Procedures

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Stored Procedures
Date: 2011-02-28 21:16:36
Message-ID: 20110228221636.7404f6f3@anubis.defcon1 (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On Mon, 28 Feb 2011 14:00:24 -0600, JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>

> I am starting to develop an application and I am considering to use stored
> procedures. Is there a performance penalty for implementing many DB
> operations with stored procedures? For example, if my app has 50 tables, is
> it a good idea to write store procedures for inserting, deleting and
> updating data for all of them?

External orders have more overhead: time of transmission, then
compilation and execution (AFAIK, both possibilities are left in cache after
1st processing), stored procs jump the 1st step.

The main advantages of stored procs are (IMHO):
* With good permissions, stored procs rights & users management, nobody can
  issue external orders to do the same,
* Users can't override your security (in order to avoid OID dependency, I
  use my own users system in which I can revoke one in 2 clicks - by disabling
  the CONNECT permission.)

> What if, additionally, I include stored
> procedures for information retrieval (SELECT statements)?

For this one, I'm not sure: I kept complicated SELECTs that return a single
row as a result stored, while keeping simple SELECTs on the client side.

> What would be a
> good criteria to decide how far to go with stored procedures?

The complexity of what can be done with them and the invisibility of sensitive
procedures because of user's rights (or not) upon them, tables & columns.
Every big proprietaries databases also have them.


In response to

pgsql-novice by date

Next:From: JoelDate: 2011-02-28 21:30:03
Subject: Not accepting passwords for remote logins Mac OS X Server 10.6.5
Previous:From: JORGE MALDONADODate: 2011-02-28 20:00:24
Subject: Stored Procedures

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group