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