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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers