Re: PL/pgSQL 1.2

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: PL/pgSQL 1.2
Дата
Msg-id CAFj8pRAeqwj6xK3jve5oC4tY45Qqva+-J7_HzwNs+Pt_+ANjxA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: PL/pgSQL 1.2  (Marko Tiikkaja <marko@joh.to>)
Ответы Re: PL/pgSQL 1.2  (Marko Tiikkaja <marko@joh.to>)
Re: PL/pgSQL 1.2  (Marko Tiikkaja <marko@joh.to>)
Re: PL/pgSQL 1.2  (Joel Jacobson <joel@trustly.com>)
Список 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).

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 по дате отправления:

Предыдущее
От: Peter Geoghegan
Дата:
Сообщение: Re: B-Tree support function number 3 (strxfrm() optimization)
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: Re: [PATCH] parser: optionally warn about missing AS for column and table aliases