Postgres Pro
Downloads
Home   >   mailing lists

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  (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>)
List pgsql-hackers
Tree view



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 by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: B-Tree support function number 3 (strxfrm() optimization)
Next
From: Pavel Stehule
Date:
Subject: Re: Re: [PATCH] parser: optionally warn about missing AS for column and table aliases