Re: PL/pgSQL 1.2 - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: PL/pgSQL 1.2 |
| Date | |
| Msg-id | CAFj8pRAeqwj6xK3jve5oC4tY45Qqva+-J7_HzwNs+Pt_+ANjxA@mail.gmail.com Whole thread Raw |
| In response to | Re: PL/pgSQL 1.2 (Marko Tiikkaja <marko@joh.to>) |
| Responses |
Re: PL/pgSQL 1.2
Re: PL/pgSQL 1.2 Re: PL/pgSQL 1.2 |
| List | pgsql-hackers |
2014-09-06 4:25 GMT+02:00 Marko Tiikkaja <marko@joh.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
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
pgsql-hackers by date: