Re: PL/pgSQL 1.2

On 2014-09-06 06:59, Pavel Stehule wrote:
> People can prepare a simple functions like you did:
>
> ...
>
> And then  use it in mass operations:
>
> BEGIN
>    FOR company IN SELECT * FROM company_list()
>    LOOP
>      FOR id IN SELECT * FROM user_list(company)
>      LOOP
>        update_user(id);
>      END LOOP;
>
> Or use it in application same style.

Yes, someone *could* do that, people are dumb.  But that's sort of 
*exactly* why we do it.

We wrap these things into (sometimes) simple-looking function so that 
none of the application developers ever run any SQL.  We define an 
interface between the application and the database, and that interface 
is implemented using PL/PgSQL functions.  Sure, sometimes one function 
will just fire off a single UPDATE .. RETURNING, or a SELECT, but that 
doesn't matter.  The trick is to be consistent everywhere.

>> But further, even if we did follow every single one of the above points
>> perfectly, it wouldn't change the point we're trying to make.  What we're
>> doing is following what the book dedicated an entire chapter to: Defensive
>> Programming.  Enforcing that that UPDATE affected exactly one row?
>> Defensive Programming.
>>
>
> Your strategy is defensive. 100%. But then I don't understand to your
> resistant  to verbosity. It is one basic stone of Ada design
>
> The problem of defensive strategy in stored procedures is possibility to
> block optimizer and result can be terrible slow. On the end, it needs a
> complex clustering solution, complex HA24 solution and higher complexity ~
> less safety.
>
> This is not problem on low load or low data applications.
>
> Banking applications are safe (and I accept, so there it is necessary), but
> they are not famous by speed.

Right.  We deal with money.  In general, I'll take slow over buggy any day.


.marko