Re: LONG delete with LOTS of FK's - Mailing list pgsql-general

On 2013-05-09 15:50, Larry Rosenman wrote:
> On 2013-05-02 10:08, Tom Lane wrote:
> Larry Rosenman <ler@lerctr.org> writes:
> Question: Do all these need to have a bare index just on the account_id
> column, or is a multicolumn index with account_id first
> sufficient for the check to be reasonably quick?
>
> I would think that such an index would be sufficient, but you could
> check for yourself by doing something like
>
>     explain select 1 from <table> where account_id = <something>
>
> and verifying that you get an indexscan not a seqscan, for each
> dependent table.
>
>             regards, tom lane
> even having done that, and with a SMALL list, we still are getting
> VERY LONG deletes:
> druckerdb=> select * from pg_stat_activity where procpid=17795;
> datid |  datname  | procpid | usesysid | usename | application_name |
> client_addr   | client_hostname | client_port |         backend_start
> |          xact_start           |          query_start
> | waiting |
> current_query
>
>
-------+-----------+---------+----------+---------+------------------+-----------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------
>
--------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> -------------------
> 16407 | druckerdb |   17795 |    16385 | drucker |                  |
> 192.168.100.230 | blueprint-app1  |       44191 | 2013-05-09
> 15:07:56.070267-05 | 2013-05-09 15:07:59.111114-05 | 2013-05-09
> 15:08:20.59
> 7237-05 | f       | DELETE FROM account WHERE id IN
>
(248512512,9310573878,588120064,643694592,255393792,512884736,440139776,47448064,324337664,311361536,459276288,5013159782,5009475335,637796352,355794944,48
> 6866944,692846592)
> (1 row)
>
> druckerdb=> \d account
> Table "public.account"
> Column                 |           Type           |
> Modifiers
>
---------------------------------------+--------------------------+-------------------------------------------------------
> id                                    | bigint                   | not
> null
> name                                  | character varying(64)    | not
> null
> value_table_name                      | character varying(32)    | not
> null
> version_item_id                       | bigint                   | not
> null
> blob_table_name                       | character varying(32)    | not
> null default 'blobs'::character varying
> account_type                          | smallint                 | not
> null default 1
> account_status                        | smallint                 | not
> null default 1
> editor_licenses                       | integer                  | not
> null default 1
> expire_date                           | date                     |
> appserver                             | text                     | not
> null default 'appserver1'::text
> appport                               | text                     | not
> null default '8080'::text
> file_space                            | bigint                   |
> security_policy                       | integer                  | not
> null
> expiry_processed                      | boolean                  |
> default false
> contributor_licenses                  | integer                  | not
> null default 0
> valid_invite_email_domains            | character varying        |
> allow_api_calls                       | boolean                  |
> default false
> allow_chat                            | boolean                  |
> default true
> is_template_account                   | boolean                  | not
> null default false
> billing_type                          | integer                  | not
> null default 0
> epayment_profile_id                   | text                     | not
> null
> instance_value_table_name             | character varying(32)    | not
> null default 'instance_values'::character varying
> show_public_stream                    | boolean                  | not
> null
> enable_posting                        | boolean                  |
> default true
> cbn_type                              | smallint                 | not
> null default 0
> account_roles                         | integer                  | not
> null
> sap_id                                | text                     |
> performance_logging                   | smallint                 |
> default 4
> admins_access_glossary_and_all_spaces | boolean                  | not
> null default false
> signup_country_code                   | text                     |
> viewer_licenses                       | integer                  | not
> null default 0
> glossary_id                           | bigint                   |
> terms_of_use_version_accepted         | integer                  |
> terms_of_use_accepted_date            | timestamp with time zone |
> terms_of_use_admin_id                 | bigint                   |
> terms_of_use_accepted_by_id           | bigint                   |
> eval_agreement_accepted_date          | timestamp with time zone |
> eval_agreement_accepted_by_id         | bigint                   |
> previous_load_duration                | bigint                   | not
> null default 0
> Indexes:
> "account_id_key" UNIQUE CONSTRAINT, btree (id) CLUSTER
> "account_name_key" UNIQUE CONSTRAINT, btree (name)
> "expire_date_idx" btree (expire_date)
> Referenced by:
> TABLE "account_activity" CONSTRAINT "account_activity_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "account_billing_info" CONSTRAINT
> "account_billing_info_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "account_cleaving" CONSTRAINT "account_cleaving_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "account_locked_by" CONSTRAINT
> "account_locked_by_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "account_logo" CONSTRAINT "account_logo_account_id_fkey" FOREIGN
> KEY (account_id) REFERENCES account(id)
> TABLE "account_main_admin" CONSTRAINT
> "account_main_admin_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "account_organization" CONSTRAINT
> "account_organization_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "preferences" CONSTRAINT "account_preferences_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "account_properties" CONSTRAINT
> "account_properties_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "avatars" CONSTRAINT "avatars_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "billing_address" CONSTRAINT "billing_address_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "billing_transaction" CONSTRAINT
> "billing_transaction_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "billing_transaction_item" CONSTRAINT
> "billing_transaction_item_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "blobs" CONSTRAINT "blobs_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "bnymellon1_values" CONSTRAINT
> "bnymellon1_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "capgemini8_values" CONSTRAINT
> "capgemini8_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "cityofcalgary_values" CONSTRAINT
> "cityofcalgary_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "comments" CONSTRAINT "comments_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "comments_history" CONSTRAINT "comments_history_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "continued_business_need" CONSTRAINT
> "continued_business_need_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "css_fro_values" CONSTRAINT "css_fro_values_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "cvscaremarkadp_values" CONSTRAINT
> "cvscaremarkadp_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "epayment_capture_response" CONSTRAINT
> "epayment_capture_response_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "ericsson2_values" CONSTRAINT "ericsson2_values_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "file_attachment" CONSTRAINT
> "file_attachment_info_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "frozen_values" CONSTRAINT "frozen_values_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "future_transaction" CONSTRAINT
> "future_transaction_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "ibm_values" CONSTRAINT "ibm_values_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "ibmbpmandrules_values" CONSTRAINT
> "ibmbpmandrules_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "ibmbwlteam_values" CONSTRAINT
> "ibmbwlteam_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "ibmgbs_values" CONSTRAINT "ibmgbs_values_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "ibmtechsales_values" CONSTRAINT
> "ibmtechsales_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "instance" CONSTRAINT "instance_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "instance_values" CONSTRAINT "instance_values_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "jmffamilyent_values" CONSTRAINT
> "jmffamilyent_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "johnsoncontrols5_values" CONSTRAINT
> "johnsoncontrols5_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "keybank3_values" CONSTRAINT "keybank3_values_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "mondialassistancegroup_values" CONSTRAINT
> "mondialassistancegroup_values_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "names" CONSTRAINT "names_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "permitted_ips" CONSTRAINT "permitted_ips_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "presby_health_serv_values" CONSTRAINT
> "presby_health_serv_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "principal_fin_grp4_values" CONSTRAINT
> "principal_fin_grp4_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "item_publications" CONSTRAINT
> "process_publications_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "processdoc_tc_nz_values" CONSTRAINT
> "processdoc_tc_nz_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "property_descriptions" CONSTRAINT
> "property_descriptions_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "saml2_idp_account_junction" CONSTRAINT
> "saml2_idp_account_junction_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "task" CONSTRAINT "task_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "template_category" CONSTRAINT
> "template_category_template_account_id_fkey" FOREIGN KEY
> (template_account_id) REFERENCES account(id)
> TABLE "template" CONSTRAINT "template_template_account_id_fkey"
> FOREIGN KEY (template_account_id) REFERENCES account(id)
> TABLE "text_search_data" CONSTRAINT "text_search_data_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "troweprice2_values" CONSTRAINT
> "troweprice2_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "usage" CONSTRAINT "usage_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "user_expanded_subs" CONSTRAINT
> "user_expanded_subs_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
> TABLE "user_favorites" CONSTRAINT "user_favorites_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "user_followed" CONSTRAINT "user_favorites_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "user_groups" CONSTRAINT "user_groups_account_id_fkey" FOREIGN
> KEY (account_id) REFERENCES account(id)
> TABLE "user_permissions" CONSTRAINT "user_permissions_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
> TABLE "user_prefs" CONSTRAINT "user_prefs_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE "user_viewed" CONSTRAINT "user_viewed_account_id_fkey" FOREIGN
> KEY (account_id) REFERENCES account(id)
> TABLE "userid" CONSTRAINT "userid_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
> TABLE ""values"" CONSTRAINT "values_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
>
> druckerdb=>
>
>
> Ideas on how to debug?
>
> this is with 9.1.9 on RHEL5 from the PGDG RPM's.
When I did the cancel, the backtrace:
2013-05-09 15:54:32,618 ERROR [pool-1-thread-5]
spring.LonSQLExceptionTranslator      - Exception executing SQL "DELETE
FROM account WHERE id IN

(248512512,9310573878,588120064,643694592,255393792,512884736,440139776,47448064,324337664,311361536,459276288,5013159782,5009475335,637796352,355794944,486866944,692846592)"

for task "StatementCallback". SQLState is "57014".
org.postgresql.util.PSQLException: ERROR: canceling statement due to
user request
Where: SQL statement "SELECT 1 FROM ONLY "public"."ibmgbs_values" x
WHERE $1 OPERATOR(pg_catalog.=) "account_id" FOR SHARE OF x"
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
at
org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at
org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at
org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at
org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:368)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:342)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:375)
at
com.lombardi.online.spring.MonitoringJdbcTemplate.execute(MonitoringJdbcTemplate.java:118)
at

com.lombardi.online.billing.process.DeleteExpiredAccountsProcess$1.doInTransactionWithoutResult(DeleteExpiredAccountsProcess.java:96)
at

org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
at

com.lombardi.online.repository.db.DeadlockRetryTransactionTemplate$TransactionCallbackWrapper.doInTransaction(DeadlockRetryTransactionTemplate.java:90)
at
org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:127)
at
com.lombardi.online.repository.db.DeadlockRetryTransactionTemplate.execute(DeadlockRetryTransactionTemplate.java:58)
at
com.lombardi.online.billing.process.DeleteExpiredAccountsProcess.execute(DeleteExpiredAccountsProcess.java:93)
at
com.lombardi.online.billing.AbstractBillingEngine.deleteExpiredAccounts(AbstractBillingEngine.java:1285)
at
com.lombardi.online.web.rest.StartRunAccountDeletion$2.run(StartRunAccountDeletion.java:116)
at
com.lombardi.online.utility.RunnableWithLogging.run(RunnableWithLogging.java:21)
at
com.lombardi.online.utility.ExecutorPool$2.call(ExecutorPool.java:164)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:206)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Is there any way to know what it's doing while it's running?


--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640 (c)     E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893