Query Core Dumping - Mailing list pgsql-performance
| From | Sam Stearns |
|---|---|
| Subject | Query Core Dumping |
| Date | |
| Msg-id | AANLkTinXfe3z5bansRTUyQCZvKRcxWD_rJQbETHL_V6K@mail.gmail.com Whole thread Raw |
| Responses |
Re: Query Core Dumping
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-performance |
Howdy,
Environment:
Postgres 8.3.13
Solaris 10
I have a SELECT query that runs no problem standalone but when running
within a perl script it intermittently core dumps. Random, no pattern
to the timing of the core dumps. The perl script processes the rows
from the query, if the rows satisfy a condition then the perl script
adds the rows to another table. When the script works it runs for
about a minute. If the script fails, it runs for about 5 minutes and
core dumps. The core dump is in the perl error handling routines. We
suspect the bug is related to how the perl postgres libraries interact
with postgres.
The query:
SELECT pa.tag,
pa.name,
pa.notices_sent,
pa.parent,
pa.contact,
pa.adsl_type,
pa.adsl_order_state,
pa.adsl_line,
pa.adsl_site_address,
pa.subnet_addresses,
pa.plan, pa.username,
pa.product_type,
pa.framed_routes,
c.tag,
c.contact,
c.name,
c.customer_type,
pa.technology,
pa.carrier,
pa.dependent_services,
pa.provisioning_email,
pa.provisioning_mobile,
pa.ull_termination_cable,
pa.ull_termination_pair,
pa.ull_termination_terminal_box
FROM personal_adsl pa,
client c
WHERE pa.parent = c.tag
AND pa.adsl_migration_id is null
AND (pa.change_to not ilike '%IBC%' OR pa.change_to is null)
AND pa.adsl_order_state in ('Confirmed', 'Churn-Ordered', 'Provisioned', 'Held')
AND (pa.adsl_type <> 'IBC' OR pa.adsl_type is null)
AND pa.active in ('Active', 'Pending')
AND (c.contact not ilike '%noncontact%' OR c.contact is null)
AND (pa.contact not ilike '%noncontact%' OR pa.contact is null)
AND (pa.notices_sent is null OR
(
(pa.adsl_order_state in ('Confirmed', 'Churn-Ordered') AND
pa.notices_sent not similar to '%(Confirm|Provision)%') OR
(pa.adsl_order_state = 'Provisioned'AND pa.notices_sent not ilike
'%Provision%') OR
(pa.adsl_order_state = 'Held' AND pa.notices_sent not ilike '%Held%')
)
);
The EXPLAIN ANALYZE:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=159798.93..612582.99 rows=17979 width=442) (actual
time=87639.667..90179.888 rows=219 loops=1)
Hash Cond: (pa.parent = c.tag)
-> Bitmap Heap Scan on personal_adsl pa (cost=94326.53..546467.23
rows=46357 width=323) (actual time=85137.720..87676.712 rows=225
loops=1)
Recheck Cond: ((active = ANY ('{Active,Pending}'::text[]))
AND (adsl_order_state = ANY
('{Confirmed,Churn-Ordered,Provisioned,Held}'::text[])))
Filter: ((adsl_migration_id IS NULL) AND ((change_to !~~*
'%IBC%'::text) OR (change_to IS NULL)) AND ((adsl_type <> 'IBC'::text)
OR (adsl_type IS NULL)) AND ((contact !~~* '%noncontact%'::text) OR
(contact IS NULL)) AND ((notices_sent IS NULL) OR ((adsl_order_state =
ANY ('{Confirmed,Churn-Ordered}'::text[])) AND (notices_sent !~
'***:^(?:.*(Confirm|Provision).*)$'::text)) OR ((adsl_order_state =
'Provisioned'::text) AND (notices_sent !~~* '%Provision%'::text)) OR
((adsl_order_state = 'Held'::text) AND (notices_sent !~~*
'%Held%'::text))))
-> BitmapAnd (cost=94326.53..94326.53 rows=185454 width=0)
(actual time=85067.110..85067.110 rows=0 loops=1)
-> Bitmap Index Scan on personal_adsl_t2
(cost=0.00..43679.06 rows=481242 width=0) (actual
time=374.128..374.128 rows=858904 loops=1)
Index Cond: (active = ANY ('{Active,Pending}'::text[]))
-> Bitmap Index Scan on
personal_adsl_dsl_order_state_index (cost=0.00..50624.05 rows=481811
width=0) (actual time=84651.450..84651.450 rows=854106 loops=1)
Index Cond: (adsl_order_state = ANY
('{Confirmed,Churn-Ordered,Provisioned,Held}'::text[]))
-> Hash (cost=60834.43..60834.43 rows=371038 width=119) (actual
time=2501.358..2501.358 rows=337954 loops=1)
-> Seq Scan on client c (cost=0.00..60834.43 rows=371038
width=119) (actual time=0.056..2077.094 rows=337954 loops=1)
Filter: ((contact !~~* '%noncontact%'::text) OR
(contact IS NULL))
Total runtime: 90180.225 ms
(14 rows)
The tables:
sqlsnbs=# \d personal_adsl
Table "public.personal_adsl"
Column | Type | Modifiers
-------------------------------------+---------+-----------
tag | text |
adsl_type | text |
_modified | integer |
subnet_addresses | text |
insidesales | text |
cost_mb | text |
technology | text |
base_hour | text |
charge | text |
_excess_warning | text |
notify | text |
active | text |
adsl_migration_to_id | text |
adsl_order_state | text |
invoice_notes | text |
hibis_timestamp_3 | text |
_created_by | text |
speed_change_date | text |
plan | text |
adsl_exchange | text |
paid_till | text |
hibis_timestamp_2 | text |
old_change_to | text |
retired | text |
unwired_eid | text |
adsl_migration_to_date | text |
adsl_speed | text |
setup_fee | text |
hibis_status | text |
snbs_user | text |
line_loss_estimate | text |
adsl_detail_status | text |
hibis_advice_method | text |
parent | text |
commission_date_paid | text |
annex_mask | text |
gift | text |
changing_to | text |
adsl_layer | text |
line_loss_cpe | text |
base_mb | text |
cca | text |
_next_excess | text |
commission | text |
add_framed_route_auto | text |
outsidesales | text |
gst_exempt | text |
external_snbs_reference | text |
cost_hour | text |
notices_sent | text |
adsl_xpair | text |
name | text |
churn | text |
contact | text |
hibis_cust_id | text |
accesslist | text |
early_termination_end | text |
excess_checked | text |
carrier | text |
status | text |
adsl_line | text |
product_type | text |
change_to | text |
contract_end | text |
adsl_cpair | text |
adsl_migration_id | text |
subnet_addresses_specify | text |
_current_hour | text |
username | text |
adsl_status_detail | text |
adsl_migration_completion_date | text |
early_termination_length | text |
email | text |
adsl_cable_id | text |
sponsored_amount | text |
sla | text |
change_in_progress | text |
hibis_incentive_payment_retail | text |
_created | integer |
service_id | text |
contract_length | text |
priority | text |
report_pending | text |
autoraise_date | text |
framed_routes | text |
adsl_migration_to_completion_date | text |
discount | text |
hibis_incentive_payment_wholesale | text |
sponsored_by | text |
hibis_timestamp_0 | text |
adsl_site_address | text |
dontsendtotelstra | text |
service_state | text |
cidr_group | text |
adsl_esa_code | text |
upfront_commission | text |
commission_to | text |
_current_mb | text |
adsl_profile | text |
adsl_migration_date | text |
billing_interval | text |
add_framed_route_specify | text |
hibis_timestamp_1 | text |
add_framed_route_specify_skip_check | text |
remove_framed_routes | text |
adsl_do_not_migrate | text |
wdsl_rsa | text |
wdsl_mac | text |
wdsl_gps_long | text |
wdsl_gps_lat | text |
paid_to_migrate | text |
wdsl_verified | text |
adsl_paid_to_migrate | text |
lock_profile | text |
extra_address_info | text |
boris_record_id | text |
_boris_record_id | text |
usage_reference | text |
l3exit_category | text |
l3exit_cutoverdate | text |
l3exit_l3serviceid | text |
hibis_contract_expiry_date | text |
l3exit_attributes | text |
l3exit_l2serviceid | text |
ull_ca_signed_date | text |
ull_assurance_category | text |
ull_power_indicator | text |
ull_identifier | text |
external_contract_type | text |
external_contract_expiry_date | text |
ull_call_diversion_number | text |
ull_losing_fnn | text |
existing_equip | text |
ull_cutover_date | text |
ull_sub_request_type | text |
last_check_request | text |
ull_dsl_service_id | text |
campaign_code | text |
reseller | text |
transition_from_date | text |
transition_from_type | text |
transition_from_snbsid | text |
transition_to_snbsid | text |
transition_to_date | text |
transition_to_type | text |
ull_boundary_point_details | text |
dependent_services | text |
parent_service_id | text |
contract_id | text |
retirement_type_code | text |
retirement_reason_code | text |
retirement_date | text |
early_termination_fee | text |
staff_sold_by | text |
provisioning_mobile | text |
provisioning_email | text |
adsl_parent_esa_code | text |
plan_id | text |
users | text |
early_termination_schedule | text |
initial_payment_workflow | text |
provisioning_workflow | text |
external_commission_schedule | text |
adsl_dslam_type | text |
ull_live_fnn_at_address | text |
data_usage_rating_scheme | text |
ull_termination_terminal_box | text |
ull_termination_pair | text |
ull_termination_cable | text |
discount_negotiated_by | text |
no_discounted_status_on_invoice | text |
som_key_list | text |
som_id_list | text |
standalone_narration | text |
netsuite_id | text |
opticomm_ref | text |
previous_charge | text |
multicast_enabled | text |
addon_pack | text |
Indexes:
"personal_adsl_adsl_carrier_index" btree (carrier)
"personal_adsl_adsl_cidr_group_index" btree (cidr_group)
"personal_adsl_adsl_parent_index" btree (parent)
"personal_adsl_adsl_plan_index" btree (plan)
"personal_adsl_adsl_retired_index" btree (retired)
"personal_adsl_adsl_snbs_user_index" btree (snbs_user)
"personal_adsl_adsl_subnet_addresses_index" btree (subnet_addresses)
"personal_adsl_adsl_technology_index" btree (technology)
"personal_adsl_change_to_index" btree (change_to)
"personal_adsl_dsl_order_state_index" btree (adsl_order_state)
"personal_adsl_exchange_index" btree (adsl_exchange)
"personal_adsl_framed_routes" btree (framed_routes)
"personal_adsl_layer_index" btree (adsl_layer)
"personal_adsl_line_index" btree (adsl_line)
"personal_adsl_migration_id_index" btree (adsl_migration_id NULLS FIRST)
"personal_adsl_profile_index" btree (adsl_profile)
"personal_adsl_speed_index" btree (adsl_speed)
"personal_adsl_t1" btree (parent)
"personal_adsl_t2" btree (active)
"personal_adsl_type_index" btree (adsl_type)
"personal_adsl_usage_ref" btree (usage_reference)
"personal_adsl_username_simple_idx" btree (username)
"tag_personal_adsl_adsl" btree (tag)
sqlsnbs=#
sqlsnbs=# \d client
Table "public.client"
Column | Type | Modifiers
---------------------------------+---------+-----------
tag | text |
contact | text |
_modified | integer |
status | text |
insidesales | text |
transaction_gst_exempt | text |
resold_by | text |
capricorn_id | text |
dd_name | text |
card_name | text |
shipping_address | text |
card_4 | text |
billing_date_change | text |
password | text |
username | text |
notify | text |
card_3 | text |
_card_debit_fail_warning | text |
billing_address | text |
credit_status | text |
billing_via | text |
card_2 | text |
referral | text |
dd_account | text |
_ccexpiry_impending_warning | text |
transaction_module | text |
_created_by | text |
transaction_type | text |
transaction_amount | text |
_created | integer |
payment_method | text |
extended_off | text |
homepop | text |
card_expiry | text |
customer_type | text |
priority | text |
card_1 | text |
autoraise_date | text |
pending_suspension | text |
sandl_member | text |
rollover_balance | text |
snbs_user | text |
_last_statement_time | text |
billing_dest | text |
discount | text |
dd_bsb | text |
billing_as | text |
transaction_service | text |
card_amount | text |
balance | text |
notes | text |
_age_balance | text |
last_statement | text |
commission | text |
billing_date | text |
outsidesales | text |
commission_to | text |
bal | text |
transaction_comment | text |
name | text |
qdsnbs | text |
_last_direct_debit | text |
billing_date_lock | text |
invoicing_style | text |
_order_service | text |
adsl_line | text |
job_type | text |
_order_client | text |
staff_sponsorship | text |
ndbm_sucks | text |
allocation_method | text |
inside_sales | text |
exclude_from_promotional_emails | text |
address_sub_address_type | text |
address_address_type | text |
address_street_name | text |
address_validation_info | text |
address_validation_status | text |
address_street_type | text |
active | text |
address_state | text |
address_sub_address_number | text |
address_locality | text |
address_postcode | text |
address_street_number | text |
address_parent_updated | text |
parent | text |
adsl_type | text |
excess_checked | text |
carrier | text |
adsl_speed | text |
setup_fee | text |
change_to | text |
charge | text |
adsl_site_address | text |
early_termination_length | text |
email | text |
base_mb | text |
cca | text |
adsl_order_state | text |
plan | text |
billing_interval | text |
transition_to_snbsid | text |
accesslist | text |
early_termination_end | text |
_kill_sessions | text |
product_type | text |
adsl_migration_id | text |
technology | text |
_excess_warning | text |
transition_from_date | text |
sla | text |
adsl_migration_to_id | text |
adsl_exchange | text |
paid_till | text |
old_change_to | text |
report_pending | text |
hibis_status | text |
transition_from_type | text |
transition_from_snbsid | text |
service_state | text |
cidr_group | text |
_next_excess | text |
early_termination_fee | text |
adsl_esa_code | text |
transition_to_date | text |
transition_to_type | text |
adsl_status_detail | text |
usage_reference | text |
retired | text |
outside_sales | text |
contact_backup | text |
sales_zone | text |
billing_destination | text |
card_type | text |
statement_hold | text |
transaction_id | text |
_use_cba | text |
_cba_cc_token | text |
_pci_card_pan | text |
netsuite_id | text |
pom_id | text |
Indexes:
"client_credit_status_index" btree (credit_status)
"client_customer_type_index" btree (customer_type)
"tag_client" btree (tag)
sqlsnbs=#
Anyone have any ideas?
Thanks,
Sam
pgsql-performance by date: