memory_leak_test.sql

create or replace function test_mem_query(iterations int) returns bigint
as $python$
    example_json = '{"name":"John Smith","age":30,"isStudent":false,"hobbies":["reading","hiking","photography"],"address":{"street":"123 Main St","city":"Boston","state":"MA","zipCode":"02108"},"phoneNumbers":[{"type":"home","number":"555-1234"},{"type":"work","number":"555-5678"}]}'
   
    # 100 repetitions of example_json as an array
    example_json_100 = [example_json] * 100
    render_json_100 = '$${'+','.join("\""+str(x).replace("\"", "\\\"")+"\"" for x in example_json_100)+'}$$::jsonb[]'
    
    plpy.execute("DROP TABLE IF EXISTS public.test1")
    plpy.execute("CREATE TABLE public.test1(col1 jsonb)")
    
    peak_memory = 0
    for i in range(iterations):            
        import psutil
        process = psutil.Process()
        plpy.debug(f"memory usage: {process.memory_info().rss / (1024*1024)}")
        peak_memory = max(peak_memory, process.memory_info().rss)
        insert_sql = f"""
            INSERT INTO public.test1(col1)
            SELECT * FROM unnest({render_json_100})
        """
        plpy.execute(insert_sql)
    
    plpy.debug(f"peak memory usage: {peak_memory / (1024*1024)}")
    return peak_memory
$python$
language plpython3u volatile security invoker
set search_path to pg_catalog, pg_temp;


create or replace function test_mem(iterations int) returns bigint
as $python$
    example_json = '{"name":"John Smith","age":30,"isStudent":false,"hobbies":["reading","hiking","photography"],"address":{"street":"123 Main St","city":"Boston","state":"MA","zipCode":"02108"},"phoneNumbers":[{"type":"home","number":"555-1234"},{"type":"work","number":"555-5678"}]}'
   
    # 100 repetitions of example_json as an array
    example_json_100 = [example_json] * 100
    
    plpy.execute("DROP TABLE IF EXISTS public.test1")
    plpy.execute("CREATE TABLE public.test1(col1 jsonb)")
    
    types = []
    types.append("jsonb[]")
    insert_plan = plpy.prepare("INSERT INTO public.test1(col1) SELECT * FROM unnest($1)", types)
    
    peak_memory = 0
    for i in range(iterations):            
        import psutil
        process = psutil.Process()
        plpy.debug(f"memory usage: {process.memory_info().rss / (1024*1024)}")
        peak_memory = max(peak_memory, process.memory_info().rss)
        plpy.execute(insert_plan, [example_json_100])
    
    plpy.debug(f"peak memory usage: {peak_memory / (1024*1024)}")
    return peak_memory
$python$
language plpython3u volatile security invoker
set search_path to pg_catalog, pg_temp;