Re: crash in plancache with subtransactions

On Oct 29, 2010, at 10:54 AM, Tom Lane wrote:
> Alvaro Herrera <alvherre@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
functionto follow patch dependencies (of course that can go away in 8.4 thanks to WITH). I know we've got some other
recursivecalls 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
havean assert function that's used all over the place; it will definitely be called from multiple places in a call
stack.

FWIW, I definitely run into cases where recursion makes for cleaner code than looping, so it'd be great to avoid making
itslower than it needs to be. But I've always assumed that recursion is slower than looping so I avoid it for anything
Iknow 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
thiscase. The original error report is: 

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 initialization
server closed the connection unexpectedly This probably means the server terminated abnormally before or while
processingthe 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
calledfull_name_table, but I decided to rename it after creating other table functions. In any case, I don't see any
obviousrecursion 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_object_class;                   :                    :
v_table_full text := tools.full_name_table( p_table_name );                   :     v_schema text;                   :
  v_table text;                   :                    : BEGIN                   :     SELECT INTO v_schema, v_table *
FROMtools.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
Nameof 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_us@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_us@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_us@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 text
Volatility          | volatile
Owner               | su
Language            | sql
Source code         |                    : SELECT quote_ident(nspname),  quote_ident(relname)                   :
FROMpg_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                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net