X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8EF6BD1CD7E for ; Wed, 2 Jun 2004 18:13:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81633-07 for ; Wed, 2 Jun 2004 21:13:42 +0000 (GMT) Received: from ml-hw5.monsterlabs.com (ml-hw5.monsterlabs.com [216.183.105.167]) by svr1.postgresql.org (Postfix) with SMTP id 1A66FD1CCD1 for ; Wed, 2 Jun 2004 18:13:39 -0300 (ADT) Received: (qmail 27878 invoked from network); 2 Jun 2004 21:13:41 -0000 Received: from w080.z064003242.bna-tn.dsl.cnc.net (HELO ?192.168.1.15?) (64.3.242.80) by ml-hw5.monsterlabs.com with SMTP; 2 Jun 2004 21:13:41 -0000 From: Marcus Whitney Reply-To: marcus@coldfeetcreative.com Organization: Coldfeet Creative To: Subject: Re: Pl/Pgsql Functions running simultaneously Date: Wed, 2 Jun 2004 16:08:56 -0500 User-Agent: KMail/1.6.1 MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200406021608.56919.marcus@coldfeetcreative.com> X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/19 X-Sequence-Number: 7077 Hello all, I have an import function that I have been working on for some time now, = and=20 it performed well up until recently. It is doing a lot, and because the=20 queries are not cached, I am not sure if that is what the problem is. If a= =20 function takes a while, does it lock any of the tables it is accessing, eve= n=20 for SELECT? Below is the bulk of the function: -- set sql statement variables create_import_file_sql :=3D ''COPY '' || container_table || '' ('' = ||=20 filtered_container_columns || '') TO '' ||=20 quote_literal(formatted_import_file) || '' WITH NULL AS '' ||=20=20 null_single_quotes; upload_to_import_table_sql :=3D ''COPY '' || import_table || '' (''= ||=20 field_names || '') FROM '' || quote_literal(formatted_import_file) || '' WI= TH=20 NULL AS '' || null_single_quotes; clean_personalization_fields_sql :=3D ''UPDATE '' || import_table |= | ''=20 SET emma_member_email =3D btrim(emma_member_email, '' ||=20 quote_literal(quoted_single_quote) || '') , emma_member_name_first =3D=20 btrim(emma_member_name_first, '' || quote_literal(quoted_single_quote) ||= =20 '') , emma_member_name_last =3D btrim(emma_member_name_last, '' ||=20 quote_literal(quoted_single_quote) || '') ;''; clean_personalization_fields_sql2 :=3D ''UPDATE '' || import_table = || ''=20 SET emma_member_email =3D btrim(emma_member_email) , emma_member_name_first= =3D=20 btrim(emma_member_name_first) , emma_member_name_last =3D=20 btrim(emma_member_name_last) ;''; set_account_id_sql :=3D ''UPDATE '' || import_table || '' SET=20 emma_account_id =3D '' || account_id; set_default_active_status_sql :=3D ''UPDATE '' || import_table || '= ' SET=20 emma_member_status_id =3D 1''; set_errors_for_null_email_sql :=3D ''UPDATE '' || import_table || '= ' SET=20 emma_member_status_id =3D 2 WHERE emma_member_email IS NULL''; record_null_email_count_sql :=3D ''UPDATE '' || import_history_tabl= e ||=20 '' SET emma_import_null_email_count =3D (SELECT COUNT(*) FROM '' ||=20 import_table || '' WHERE emma_member_email IS NULL) WHERE=20 emma_import_history_id =3D'' || import_history_id; set_errors_for_invalid_email_sql :=3D ''UPDATE '' || import_table |= | ''=20=20 SET emma_member_status_id =3D 2 WHERE emma_member_email !~* '' || email_re= gex; record_invalid_email_count_sql :=3D ''UPDATE '' || import_history_t= able=20 || '' SET emma_import_invalid_email_count =3D ( SELECT COUNT(*) FROM '' ||= =20 import_table || '' WHERE emma_member_email !~* '' || email_regex || '' )= =20 WHERE emma_import_history_id =3D'' || import_history_id; get_dupes_in_import_sql :=3D ''SELECT emma_member_email,=20 emma_member_status_id FROM '' || import_table || '' GROUP BY=20 emma_member_email, emma_member_status_id having count(*) > 1''; insert_dupes_sql :=3D ''INSERT INTO '' || dupe_table || '' SELECT = *=20 FROM '' || import_table || '' WHERE LOWER(emma_member_email) =3D LOWER('' |= |=20 member_table || ''.emma_member_email)''; record_table_dupe_count_sql :=3D ''UPDATE '' || import_history_tabl= e ||=20 '' SET emma_import_table_dupe_email_count =3D (SELECT COUNT(*) FROM '' ||= =20 import_table || '' WHERE emma_member_email =3D LOWER('' || member_table ||= =20 ''.emma_member_email)) WHERE emma_import_history_id =3D'' || import_history= _id; remove_dupes_from_import_table_sql :=3D ''DELETE FROM '' || import_= table=20 || '' WHERE LOWER(emma_member_email) =3D LOWER('' || member_table ||=20 ''.emma_member_email)''; create_clean_import_file_sql :=3D ''COPY '' || import_table || '' T= O ''=20 || quote_literal(clean_import_file) || '' WITH NULL AS '' ||=20=20 null_single_quotes; create_members_groups_ids_file_sql :=3D ''COPY '' || import_table |= |=20 '' (emma_member_id) TO '' || quote_literal(members_groups_ids_file) || ''= =20 WITH NULL AS '' || null_single_quotes; empty_import_table_sql :=3D ''TRUNCATE '' || import_table; upload_clean_import_sql :=3D ''COPY '' || member_table || '' FROM '= ' ||=20 quote_literal(clean_import_file) || '' WITH NULL AS '' ||=20=20 null_single_quotes; upload_members_groups_ids_sql :=3D ''COPY '' || members_groups_ids_= table=20 || '' (emma_member_id) FROM '' || quote_literal(members_groups_ids_file) ||= =20 '' WITH NULL AS '' || null_single_quotes; empty_members_groups_ids_sql :=3D ''TRUNCATE '' ||=20 members_groups_ids_table; empty_members_dupes_sql :=3D ''TRUNCATE '' || dupe_table; vacuum_sql :=3D ''VACUUM '' || member_table || ''; VACUUM '' ||=20 import_table || ''; VACUUM '' || container_table || ''; VACUUM '' ||=20 members_groups_table || ''; VACUUM '' || members_groups_ids_table || '';=20 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 '' |= |=20 import_table || '' WHERE emma_member_email =3D '' ||=20 quote_literal(duplicate_record.emma_member_email) || '' ORDER BY=20 emma_member_id LIMIT 1'' LOOP escape_first_name :=3D quote_literal=20 (replacement_record.emma_member_name_first); escape_last_name :=3D quote_literal=20 (replacement_record.emma_member_name_last); escape_email :=3D quote_literal=20 (replacement_record.emma_member_email); escape_status_id :=3D=20 quote_literal(replacement_record.emma_member_status_id); -- Record count of dupes FOR dupe_record_count IN EXECUTE ''SELECT COUNT= (*)=20 AS count FROM '' || import_table || '' WHERE LOWER(emma_member_email) =3D= =20 LOWER('' || escape_email || '')'' LOOP EXECUTE ''UPDATE '' ||=20 import_history_table || '' SET emma_import_file_dupe_email_count =3D'' ||= =20 dupe_record_count.count; END LOOP; FOR primary_dupe_record IN EXECUTE ''SELECT=20 MAX(emma_member_id) AS max_id FROM '' || import_table || '' WHERE=20 LOWER(emma_member_email) =3D LOWER('' || escape_email || '')'' LOOP EXECUTE ''UPDATE '' || import_table || = ''=20=20 SET emma_member_status_id =3D 5 WHERE emma_member_id =3D '' ||=20 primary_dupe_record.max_id; EXECUTE ''DELETE FROM '' || import_tabl= e=20 || '' WHERE emma_member_email =3D '' ||=20 quote_literal(duplicate_record.emma_member_email) || '' AND=20 emma_member_status_id !=3D 5''; EXECUTE ''UPDATE '' || import_table || = ''=20=20 SET emma_member_status_id =3D 1 WHERE emma_member_status_id =3D 5''; END LOOP; import_dupe_count :=3D 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 :=3D 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 |= |=20 '' SELECT '' || member_table || ''.emma_member_id FROM ONLY '' ||=20 member_table || '' WHERE LOWER('' || member_table || ''.emma_member_email) = =3D=20 LOWER('' || dupe_table || ''.emma_member_email) AND '' || member_table ||= =20 ''.emma_member_id NOT IN (SELECT '' || members_groups_table ||=20 ''.emma_member_id FROM '' || members_groups_table || '' WHERE '' ||=20 members_groups_table || ''.emma_group_id =3D '' || groups[i] || '') AND '' = ||=20 member_table || ''.emma_member_id NOT IN (SELECT emma_member_id FROM '' ||= =20 members_groups_ids_table || '')''; EXECUTE ''DELETE FROM '' || members_groups_ids_table |= |=20 '' WHERE emma_member_id IN (SELECT emma_member_id FROM '' ||=20 members_groups_table || '' WHERE emma_group_id =3D '' || groups[i] || '' )'= '; EXECUTE ''INSERT INTO '' || members_groups_table || ''= =20 SELECT DISTINCT '' || groups[i] || '' AS emma_group_id, emma_member_id FRO= M=20 '' || members_groups_ids_table; END LOOP; END IF; Any pointers on large plpgsql operations are appreciated. Especially when= =20 more than one instance is runinng. Thanks. --=20 marcus whitney chief architect : cold feet creative www.coldfeetcreative.com 800.595.4401 =A0 cold feet presents emma email marketing for discriminating organizations everywhere visit www.myemma.com