Re: "stored procedures"

On Fri, Apr 22, 2011 at 9:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> It wouldn't bother me in the lest that if in plpgsql procedures if you
>> had to set up and tear down a transaction on every line.
>
> It would once you noticed the performance impact ...

I'm aware of the impact. It would suck, but you perhaps it's not
*quite* as bad as you think, considering:
*) faster performance is only an explicit transaction/function away
*) perhaps some optimizations are possible...x := x +1; can be
directly evaluated?
*) simple logic (IF <variable>) can be directly evaluated?
*) how bad is it really? from my measurements in queries/sec:

6.7k selects single client,
12k selects piped through single user backend,
13.5k piped through single user backend, one transaction
23k in plpgsql 'execute' in loop (which is really two queries, one to
build the query and one to execute),
100k in non dynamic query plpgsql in loop.

even if our plpgsql lines/sec dropped from 100k to 10k, maybe that's acceptable?

Point being, procedures aren't trying to meet the same set of use
cases that functions meet. I see them doing things you currently can't
do with functions (point's 1-4 above, plus any syntax sugar/salt CALL
brings to the table). You have tons of tools to deal with performance
problems should they occur.

merlin