Thread: 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
Am I on the wrong list to ask this question, or does this list usually have
low activity? Just asking because I am new and I need to know where to ask
this question. Thanks.
On Wednesday 02 June 2004 16:08, Marcus Whitney wrote:
> 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
Uh... I don't think this is necessarily the wrong list, sometimes people
don't have much to chime in. You could try reposting to -sql or -general
I suppose.
As for my take on your questions, I wasn't exactly clear on what the
problem is. If its just that things seem slow, make sure you have done
the appropriate vacuum/analyze/reindex tech and then try adding some
debug info to the function to determine where in the function it is
slowing down.
queries inside plpgsql functions will take locks as needed, but they are
no different than regular statements, just keep in mind that the queries
inside the function will work like an implicit transaction.
Robert Treat
On Thu, 2004-06-03 at 17:38, Marcus Whitney wrote:
> Am I on the wrong list to ask this question, or does this list usually have
> low activity? Just asking because I am new and I need to know where to ask
> this question. Thanks.
>
> On Wednesday 02 June 2004 16:08, Marcus Whitney wrote:
> > 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Thanks for your reply. My comments are below.
On Friday 04 June 2004 16:39, you wrote:
> Uh... I don't think this is necessarily the wrong list, sometimes people
> don't have much to chime in. You could try reposting to -sql or -general
> I suppose.
I'll try one of those.
>
> As for my take on your questions, I wasn't exactly clear on what the
> problem is. If its just that things seem slow, make sure you have done
> the appropriate vacuum/analyze/reindex tech and then try adding some
> debug info to the function to determine where in the function it is
> slowing down.
Yeah, I do a fair amount of vacuum/analyze , but I am unclear as to when I
should run REINDEX. Is their a way to tell that indexes have become corrupt,
or need to be reindexed?
>
> queries inside plpgsql functions will take locks as needed, but they are
> no different than regular statements, just keep in mind that the queries
> inside the function will work like an implicit transaction.
I've noticed. Thanks for the info.
>
> Robert Treat
>
> On Thu, 2004-06-03 at 17:38, Marcus Whitney wrote:
> > Am I on the wrong list to ask this question, or does this list usually
> > have low activity? Just asking because I am new and I need to know where
> > to ask this question. Thanks.
> >
> > On Wednesday 02 June 2004 16:08, Marcus Whitney wrote:
> > > 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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
--
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Marcus Whitney wrote: | Am I on the wrong list to ask this question, or does this list usually have | low activity? Just asking because I am new and I need to know where to ask | this question. Thanks. Your .sig may hold the reason why people are not responding. You seem like an intelligent guy and you asked an interesting question, but... | cold feet presents emma | | email marketing for discriminating | ^^^^^^^^^^^^^^^ | organizations everywhere | | visit www.myemma.com - -- Andrew Hammond 416-673-4138 ahammond@ca.afilias.info Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA1zstgfzn5SevSpoRApgdAKCtjL4qMwNQ9mZN57RHmHJi5Ana0wCggXhb 7HYFtE3S9zQ2hSGR9vYdXYQ= =Kfqd -----END PGP SIGNATURE-----