Re: Pl/Pgsql Functions running simultaneously

Hello all,

I have an import function that I have been working on for some time now, and
it performed well up until recently. It is doing a lot, and because the
queries are not cached, I am not sure if that is what the problem is. If a
function takes a while, does it lock any of the tables it is accessing, even
for SELECT?

Below is the bulk of the function:

-- set sql statement variables
create_import_file_sql := ''COPY '' || container_table || '' ('' ||
filtered_container_columns || '') TO '' ||
quote_literal(formatted_import_file) || '' WITH NULL AS '' ||
null_single_quotes;
upload_to_import_table_sql := ''COPY '' || import_table || '' ('' ||
field_names || '') FROM '' || quote_literal(formatted_import_file) || '' WITH
NULL AS '' || null_single_quotes;
clean_personalization_fields_sql := ''UPDATE '' || import_table || ''
SET emma_member_email = btrim(emma_member_email, '' ||
quote_literal(quoted_single_quote) || '') , emma_member_name_first =
btrim(emma_member_name_first, '' || quote_literal(quoted_single_quote) ||
'') , emma_member_name_last = btrim(emma_member_name_last, '' ||
quote_literal(quoted_single_quote) || '') ;'';
clean_personalization_fields_sql2 := ''UPDATE '' || import_table || ''
SET emma_member_email = btrim(emma_member_email) , emma_member_name_first =
btrim(emma_member_name_first) , emma_member_name_last =
btrim(emma_member_name_last) ;'';
set_account_id_sql := ''UPDATE '' || import_table || '' SET
emma_account_id = '' || account_id;
set_default_active_status_sql := ''UPDATE '' || import_table || '' SET
emma_member_status_id = 1'';
set_errors_for_null_email_sql := ''UPDATE '' || import_table || '' SET
emma_member_status_id = 2 WHERE emma_member_email IS NULL'';
record_null_email_count_sql := ''UPDATE '' || import_history_table ||
'' SET emma_import_null_email_count = (SELECT COUNT(*) FROM '' ||
import_table || '' WHERE emma_member_email IS NULL) WHERE
emma_import_history_id ='' || import_history_id;
set_errors_for_invalid_email_sql := ''UPDATE '' || import_table || ''
SET emma_member_status_id = 2 WHERE emma_member_email !~* '' || email_regex;
record_invalid_email_count_sql := ''UPDATE '' || import_history_table
|| '' SET emma_import_invalid_email_count = ( SELECT COUNT(*) FROM '' ||
import_table || '' WHERE emma_member_email !~* '' || email_regex || '' )
WHERE emma_import_history_id ='' || import_history_id;
get_dupes_in_import_sql := ''SELECT emma_member_email,
emma_member_status_id FROM '' || import_table || '' GROUP BY
emma_member_email, emma_member_status_id having count(*) > 1'';
insert_dupes_sql := ''INSERT INTO '' || dupe_table || '' SELECT *
FROM '' || import_table || '' WHERE LOWER(emma_member_email) = LOWER('' ||
member_table || ''.emma_member_email)'';
record_table_dupe_count_sql := ''UPDATE '' || import_history_table ||
'' SET emma_import_table_dupe_email_count = (SELECT COUNT(*) FROM '' ||
import_table || '' WHERE emma_member_email = LOWER('' || member_table ||
''.emma_member_email)) WHERE emma_import_history_id ='' || import_history_id;
remove_dupes_from_import_table_sql := ''DELETE FROM '' || import_table
|| '' WHERE LOWER(emma_member_email) = LOWER('' || member_table ||
''.emma_member_email)'';
create_clean_import_file_sql := ''COPY '' || import_table || '' TO ''
|| quote_literal(clean_import_file) || '' WITH NULL AS '' ||
null_single_quotes;
create_members_groups_ids_file_sql := ''COPY '' || import_table ||
'' (emma_member_id) TO '' || quote_literal(members_groups_ids_file) || ''
WITH NULL AS '' || null_single_quotes;
empty_import_table_sql := ''TRUNCATE '' || import_table;
upload_clean_import_sql := ''COPY '' || member_table || '' FROM '' ||
quote_literal(clean_import_file) || '' WITH NULL AS '' ||
null_single_quotes;
upload_members_groups_ids_sql := ''COPY '' || members_groups_ids_table
|| '' (emma_member_id) FROM '' || quote_literal(members_groups_ids_file) ||
'' WITH NULL AS '' || null_single_quotes;
empty_members_groups_ids_sql := ''TRUNCATE '' ||
members_groups_ids_table;
empty_members_dupes_sql := ''TRUNCATE '' || dupe_table;
vacuum_sql := ''VACUUM '' || member_table || ''; VACUUM '' ||
import_table || ''; VACUUM '' || container_table || ''; VACUUM '' ||
members_groups_table || ''; VACUUM '' || members_groups_ids_table || '';
VACUUM '' || dupe_table;

-- BEGIN ACTIVITY
-- Create the filtered import file with the
EXECUTE create_import_file_sql;
-- Load data from the filtered file to the import table
EXECUTE upload_to_import_table_sql;
-- Set account id in import table
EXECUTE set_account_id_sql;
-- Set the status of all the records to 1
EXECUTE set_default_active_status_sql;
-- Clean personalization data
EXECUTE clean_personalization_fields_sql;
EXECUTE clean_personalization_fields_sql2;
-- Set the status to error for all NULL emails
EXECUTE set_errors_for_null_email_sql;
-- Record the count of null emails
EXECUTE record_null_email_count_sql;
-- Set the status to error for all invalid emails
EXECUTE set_errors_for_invalid_email_sql;
-- Record the count of invalid emails
EXECUTE record_invalid_email_count_sql;

-- Remove duplicates in import table (originally in file)
FOR duplicate_record IN EXECUTE get_dupes_in_import_sql LOOP
IF duplicate_record.emma_member_email IS NOT NULL THEN
FOR replacement_record IN EXECUTE '' SELECT * FROM '' ||
import_table || '' WHERE emma_member_email = '' ||
quote_literal(duplicate_record.emma_member_email) || '' ORDER BY
emma_member_id LIMIT 1'' LOOP
escape_first_name := quote_literal
(replacement_record.emma_member_name_first);
escape_last_name := quote_literal
(replacement_record.emma_member_name_last);
escape_email := quote_literal
(replacement_record.emma_member_email);
escape_status_id :=
quote_literal(replacement_record.emma_member_status_id);
-- Record count of dupes
FOR dupe_record_count IN EXECUTE ''SELECT COUNT(*)
AS count FROM '' || import_table || '' WHERE LOWER(emma_member_email) =
LOWER('' || escape_email || '')'' LOOP
EXECUTE ''UPDATE '' ||
import_history_table || '' SET emma_import_file_dupe_email_count ='' ||
dupe_record_count.count;
END LOOP;
FOR primary_dupe_record IN EXECUTE ''SELECT
MAX(emma_member_id) AS max_id FROM '' || import_table || '' WHERE
LOWER(emma_member_email) = LOWER('' || escape_email || '')'' LOOP
EXECUTE ''UPDATE '' || import_table || ''
SET emma_member_status_id = 5 WHERE emma_member_id = '' ||
primary_dupe_record.max_id;
EXECUTE ''DELETE FROM '' || import_table
|| '' WHERE emma_member_email = '' ||
quote_literal(duplicate_record.emma_member_email) || '' AND
emma_member_status_id != 5'';
EXECUTE ''UPDATE '' || import_table || ''
SET emma_member_status_id = 1 WHERE emma_member_status_id = 5'';
END LOOP;
import_dupe_count := import_dupe_count + 1;
END LOOP;
END IF;
END LOOP;

-- Move dupes over to the dupe table
EXECUTE insert_dupes_sql;
-- Record the count of dupes from import to members
EXECUTE record_table_dupe_count_sql;
-- Delete the dupes from the import table
EXECUTE remove_dupes_from_import_table_sql;
-- Create clean import file
EXECUTE create_clean_import_file_sql;
-- Create groups_id file
EXECUTE create_members_groups_ids_file_sql;
-- Empty import table
EXECUTE empty_import_table_sql;
-- Upload clean members from import
EXECUTE upload_clean_import_sql;
-- Upload group ids
EXECUTE upload_members_groups_ids_sql;

-- Associate to groups
groups := string_to_array(group_list, '','');
if array_lower(groups, 1) IS NOT NULL THEN
FOR i IN array_lower(groups, 1)..array_upper(groups, 1) LOOP
EXECUTE ''INSERT INTO '' || members_groups_ids_table ||
'' SELECT '' || member_table || ''.emma_member_id FROM ONLY '' ||
member_table || '' WHERE LOWER('' || member_table || ''.emma_member_email) =
LOWER('' || dupe_table || ''.emma_member_email) AND '' || member_table ||
''.emma_member_id NOT IN (SELECT '' || members_groups_table ||
''.emma_member_id FROM '' || members_groups_table || '' WHERE '' ||
members_groups_table || ''.emma_group_id = '' || groups[i] || '') AND '' ||
member_table || ''.emma_member_id NOT IN (SELECT emma_member_id FROM '' ||
members_groups_ids_table || '')'';
EXECUTE ''DELETE FROM '' || members_groups_ids_table ||
'' WHERE emma_member_id IN (SELECT emma_member_id FROM '' ||
members_groups_table || '' WHERE emma_group_id = '' || groups[i] || '' )'';
EXECUTE ''INSERT INTO '' || members_groups_table || ''
SELECT DISTINCT '' || groups[i] || '' AS emma_group_id, emma_member_id FROM
'' || members_groups_ids_table;
END LOOP;
END IF;

Any pointers on large plpgsql operations are appreciated. Especially when
more than one instance is runinng. Thanks.

--
marcus whitney

chief architect : cold feet creative

www.coldfeetcreative.com

800.595.4401
 

cold feet presents emma

email marketing for discriminating

organizations everywhere

visit www.myemma.com