Re: PL/pgSQL 1.2

2014-09-06 4:25 GMT+02:00 Marko Tiikkaja <marko(at)joh(dot)to>:

> On 2014-09-04 2:28 PM, I wrote:
>
>> On 9/4/14 2:04 PM, Pavel Stehule wrote:
>>
>>> for example best practices for PL/SQL by Steven Feuerstein
>>>
>>
>> I'll spend some time with that book to have a better idea on where
>> you're coming from.
>>
>
> I've read through this book twice now. Some observations on things we
> don't follow:
>
> - We don't use the exact hungarian notation -ish convention for naming
> stuff. I don't see that as a bad thing.
> - Granted, we could be using the myfield tablename.columnname%TYPE;
> probably more. On the other hand, sometimes you would prefer to not have
> all your types in your functions change transparently after an ALTER TABLE.
> - The book takes the "single exit point" thinking to an extreme. I
> don't agree with that, regardless of the language (and thus I might not
> necessarily always follow it).
> - The book says "Encapsulate INSERT, UPDATE, and DELETE statements
> behind procedure calls", which quite directly contradicts what you said
> earlier.
>

Not necessary -- It say -- complex SQL should not be used more times in
code, but there is not specified, so they must by stored in trivial
functions. Complex queries should be wrapped by views instead - it doesn't
block a optimizer

There is a strong warning to not break optimizer.

>
> The rest of the stuff we follow in our codebase as far as I can tell
> (except the Oracle-specific stuff, obviously).
>

Ten years ago I wrote article
http://postgres.cz/wiki/PL/pgSQL_%28en%29#Recommendation_for_design_of_stored_procedures_in_PL.2FpqSQL_language
based on Steve F, Joe Celko and others presentations and books

http://postgres.cz/wiki/PL/pgSQL_%28en%29#Recommendation_for_design_of_stored_procedures_in_PL.2FpqSQL_language

There is point: "Don't enclose SQL commands to simply functions uselessly."

Where is a problem.

People can prepare a simple functions like you did:

...

CREATE OR REPLACE FUNCTION user_list ()
RETURNS SETOF id AS $$
BEGIN
RETURN QUERY SELECT id FROM user WHERE .. some = $1
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_user(int)
RETURNS void AS $$
BEGIN
UPDATE user SET .. WHERE id = $1
END;
$$ LANGUAGE;

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.

It is safe .. sure, and I accept it. But It is terrible slow.

If you are lucky and have some knowledges, you can use a SQL function in
Postgres. It is a macros, so it is not a black bock for optimizer, but I am
not sure, if postgres optimizer can do well work in this case too.

This is Joe Celko lovely theme.

> 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.

Pavel

>
>
> .marko
>