Complex structure storage, better in temp table or array?

Поиск
Список
Период
Сортировка
От Ioannis Anagnostopoulos
Тема Complex structure storage, better in temp table or array?
Дата
Msg-id 4F0D7C61.9010103@anatec.com
обсуждение исходный текст
Ответ на Too much RAM allocated by webserver when executing an Insert-Statement (npgsql)  (Christian Tonhäuser <christian.tonhaeuser@gmx.net>)
Список pgsql-novice
Hello all,

I have a complex structure as follows:

CREATE TYPE ais_analyzer.schema_dates AS (
   schema_name  varchar(30),
   dates        integer[],
   cursor_name  "refcursor"
);

This type is used in a stored procedure to temporary hold some data that
are used to execute some secondary stored procedures that are different
only be schema as follows:

Declare
         m_schema_array ais_analyzer.schema_dates[];
BEGIN
.........<some actions to populate the m_schema_array>.......
FOR i IN 1..array_upper(m_schema_array, 1) LOOP
         RAISE NOTICE 'Schemas in array %', m_schema_array[i];
         m_acursor := m_schema_array[i].cursor_name;
         OPEN m_acursor  FOR
             EXECUTE 'SELECT * from ' || m_schema_array[i].schema_name
|| '.georef_stats($1,$2,$3)' USING p_feed_ids, m_schema_array[i].dates,
p_georef;
             RETURN next m_acursor;
END LOOP;
RETURN;
END;

Having declared the m_schema_array as ais_analyzer.schema_dates[] seems
to require more complex actions to access and modify the "items" of the
type per array element while storing the type at a temporary table with
only the field "dates" as integer[] makes things easier. However I am
concerned about speed degradation if I use temporary tables  since my
array can have the most 12 rows and every row can have maximum 31
integers at the type.dates item. Furthermore the stored procedure that
uses this custom type is called thousands of times (if not a few million
times) so in the "temporary table solution" the temp table will be
created millions of times. Any advice will be greatly appreciated.

Kind Regards
Yiannis


В списке pgsql-novice по дате отправления:

Предыдущее
От: Christian Tonhäuser
Дата:
Сообщение: Too much RAM allocated by webserver when executing an Insert-Statement (npgsql)
Следующее
От: Bob Branch
Дата:
Сообщение: Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename