On Oct 29, 2010, at 10:54 AM, Tom Lane wrote:
> Alvaro Herrera <alvhe...@commandprompt.com> writes:
>> Excerpts from Tom Lane's message of miƩ oct 27 18:18:06 -0300 2010:
>>> I spent quite a bit of time trying to deal with the memory-leakage
>>> problem without adding still more bookkeeping overhead.  It wasn't
>>> looking good, and then I had a sudden insight: if we see that the in-use
>>> flag is set, we can simply return FALSE from exec_eval_simple_expr.
>> I tried the original test cases that were handed to me (quite different
>> from what I submitted here) and they are fixed also.  Thanks.
> It'd be interesting to know if there's any noticeable slowdown on
> affected real-world cases.  (Of course, if they invariably crashed
> before, there might not be a way to measure their previous speed...)

I should be able to get Alvaro something he can use to test the performance. 
Our patch framework uses a recursive function to follow patch dependencies (of 
course that can go away in 8.4 thanks to WITH). I know we've got some other 
recursive calls but I don't think any are critical (it'd be nice if there was a 
way to find out if a function was recursive, I guess theoretically that could 
be discovered during compilation but I don't know how hairy it would be).

One question: What happens if you have multiple paths to the same function 
within another function? For example, we have an assert function that's used 
all over the place; it will definitely be called from multiple places in a call 

FWIW, I definitely run into cases where recursion makes for cleaner code than 
looping, so it'd be great to avoid making it slower than it needs to be. But 
I've always assumed that recursion is slower than looping so I avoid it for 
anything I know could be performance sensitive.

(looking at original case)... the original bug wasn't actually recursive. It's 
not clear to me how it actually got into this case. The original error report 

psql:sql/code.lookup_table_dynamic.sql:23: ERROR:  buffer 2682 is not owned by 
resource owner Portal
CONTEXT:  SQL function "table_schema_and_name" statement 1
SQL function "table_full_name" statement 1
PL/pgSQL function "getsert" line 9 during statement block local variable 
server closed the connection unexpectedly
  This probably means the server terminated abnormally
  before or while processing the request.

Line 23 is:

    SELECT code.getsert( 'test.stuffs', 'stuff' );

The functions are below. The duplicity of full_name_table and table_full_name 
is because the function was originally called full_name_table, but I decided to 
rename it after creating other table functions. In any case, I don't see any 
obvious recursion or re-entry, unless perhaps tools.table_schema_and_name ends 
up getting called twice by tools.table_full_name?

-[ RECORD 1 
Schema              | code
Name                | getsert
Result data type    | void
Argument data types | p_table_name text, p_lookup text
Volatility          | volatile
Owner               | cnuadmin
Language            | plpgsql
Source code         | 
                    : DECLARE
                    :     v_object_class text := 'getsert';
                    :     v_function_name text := p_table_name || '__' || 
                    :     v_table_full text := tools.full_name_table( 
p_table_name );
                    :     v_schema text;
                    :     v_table text;
                    : BEGIN
                    :     SELECT INTO v_schema, v_table * FROM 
tools.split_schema( v_table_full );
                    :     PERFORM code_internal.create_object( v_function_name, 
'FUNCTION', v_object_class, array[ ['schema', v_schema], ['table', v_table], 
['lookup', p_lookup] ] );
                    : END;
Description         | Creates a function that will lookup an ID based on a text 
lookup value (p_lookup). If no record exists, one will be created.
                    : Parameters:
                    :     p_table_name Name of the table to lookup the value in
                    :     p_lookup Name of the field to use for the lookup value
                    : Results:
                    : Creates function %p_table_name%__getsert( %p_lookup% with 
a type matching the p_lookup field in p_table_name ). The function returns an 
ID as an int.
                    : Revokes all on the function from public and grants 
execute to cnuapp_role.

test...@workbook.local=# \df+ tools.full_name_table 
List of functions
-[ RECORD 1 ]-------+-----------------------------------
Schema              | tools
Name                | full_name_table
Result data type    | text
Argument data types | p_table_name text
Volatility          | volatile
Owner               | cnuadmin
Language            | sql
Source code         | SELECT tools.table_full_name( $1 )
Description         | 

test...@workbook.local=# \df+ tools.table_full_name
List of functions
-[ RECORD 1 
Schema              | tools
Name                | table_full_name
Result data type    | text
Argument data types | p_table_name text
Volatility          | volatile
Owner               | su
Language            | sql
Source code         | SELECT schema_name || '.' || table_name FROM 
tools.table_schema_and_name( $1 )
Description         | 

test...@workbook.local=# \df+ tools.table_schema_and_name
List of functions
-[ RECORD 1 
Schema              | tools
Name                | table_schema_and_name
Result data type    | record
Argument data types | p_table_name text, OUT schema_name text, OUT table_name 
Volatility          | volatile
Owner               | su
Language            | sql
Source code         | 
                    : SELECT quote_ident(nspname),  quote_ident(relname)
                    :   FROM pg_class c
                    :     JOIN pg_namespace n ON n.oid = c.relnamespace
                    :   WHERE c.oid = $1::regclass
                    :     AND tools.assert( relkind = 'r', 'Relation ' || $1 || 
' is not a table' )
Description         | 

Jim C. Nasby, Database Architect                   j...@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to