Re: Pl/Pgsql Functions running simultaneously - Mailing list pgsql-performance

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