Search Postgresql Archives

Yikes: ERROR: out of memory

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 
Now THIS is a new one for me! I have no idea where to even start. Does anyone know how to look for the error? Below is the query and what I believe are the related log entries.
Any help will be rewarded with heartfelt gratitude and praise, or you can just come to Montreal and hit me up for a beer. 

Carlo


ERROR: out of memory
SQL state: 53200
Detail: Failed on request of size 134217728.

SELECT facility_id, street_address, base_zip, COUNT(*) AS provider_count
FROM (
  SELECT DISTINCT
     f.facility_id,
     p.provider_id,
TRIM(COALESCE(a.parsed_number, '')||' '||a.parsed_street) AS street_address, 
     SUBSTR(a.postal_code, 1, 5) AS base_zip
  FROM mdx_core.provider AS p
  JOIN mdx_core.provider_practice AS pp USING (provider_id)
  JOIN mdx_core.facility_address AS fa USING (facility_address_id)
  JOIN mdx_core.facility AS f ON f.facility_id = fa.facility_id
  JOIN mdx_core.address AS a USING (address_id)
  WHERE
     p.provider_status_code = 'A'
     AND pp.practice_tier_code <= '3'
) AS p_per_addr
GROUP BY facility_id, street_address, base_zip
HAVING COUNT(*) > 1

ERROR:  out of memory
DETAIL:  Failed on request of size 134217728.

********** Error **********

TopMemoryContext: 85688 total in 10 blocks; 8952 free (25 chunks); 76736 use
TopTransactionContext 192 total in 1 blocks; 7856 free (1 chunks); 336 used Record information cache 192 total in 1 blocks; 1800 free (0 chunks); 6392 used Local Breakpoint Count Table 192 total in 1 blocks; 4872 free (0 chunks); 3320 used 
 Local Breakpoints: 8192 total in 1 blocks; 5904 free (0 chunks); 2288 use
PL/PgSQL function context 192 total in 1 blocks; 7216 free (3 chunks); 976 used PLpgSQL function cache 4328 total in 2 blocks; 5904 free (0 chunks); 18424 used Type information cache 192 total in 1 blocks; 1800 free (0 chunks); 6392 used 
 CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 use
Operator class cache 192 total in 1 blocks; 3848 free (0 chunks); 4344 used Operator lookup cache 4576 total in 2 blocks; 14072 free (6 chunks); 10504 used MessageContext: 524288 total in 7 blocks; 246336 free (2 chunks); 277952 use 
 smgr relation table: 8192 total in 1 blocks; 744 free (0 chunks); 7448 use
TransactionAbortContext 2768 total in 1 blocks; 32752 free (0 chunks); 16 used 
 Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 use
 PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 use
   PortalHeapMemory: 1024 total in 1 blocks; 880 free (0 chunks); 144 use
ExecutorState: 57344 total in 3 blocks; 3240 free (6 chunks); 54104 use 
       HashTableContext  total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext 66222872 total in 77 blocks; 4824944 free (75 chunks); 561397928 used 
       HashTableContext  total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext 0847768 total in 16 blocks; 3739736 free (9 chunks); 47108032 used 
       HashTableContext  total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext 35258136 total in 26 blocks; 4958688 free (24 chunks); 130299448 used 
       HashTableContext  total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext 20192792 total in 36 blocks; 7649816 free (29 chunks); 212542976 used TupleSort: 369090584 total in 46 blocks; 7648 free (25 chunks); 369082936 use 
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 8192 total in 1 blocks; 8088 free (4 chunks); 104 use
       Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       AggContext: 8192 total in 1 blocks; 8104 free (0 chunks); 88 use
TupleHashTable 186112 total in 9 blocks; 2064088 free (25 chunks); 2122024 used 
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
 Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 use
CacheMemoryContext: 1191760 total in 21 blocks; 588472 free (751 chunks); 603288 use provider_input_resource_id_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used 
   provider_upin_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
provider_provider_staus_code_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_provider_standing_code_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_provider_id_provider_status_code_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used provider_provider_id_master_name_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used 
   provider_npi_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
provider_mid_status_code_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used provider_master_name_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_master_id_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_ln_lower_fn_lower_status_idx 024 total in 1 blocks; 192 free (0 chunks); 832 used provider_ln_lower_fn_lower_standing_status_idx 024 total in 1 blocks; 128 free (0 chunks); 896 used provider_last_name_metaphone_name_initials_lower_idx 024 total in 1 blocks; 192 free (0 chunks); 832 used provider_last_name_lower_first_name_lower_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used provider_last_name_lower_first_initial_lower_birth_data_idx 024 total in 1 blocks; 72 free (0 chunks); 952 used provider_birth_year_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used 
   provider_abms_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
   provider_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
provider_master_name_unique_key 024 total in 1 blocks; 344 free (0 chunks); 680 used pg_type_typname_nsp_index 024 total in 1 blocks; 240 free (0 chunks); 784 used pg_statistic_relid_att_index 024 total in 1 blocks; 240 free (0 chunks); 784 used address_id_country_state_zip_cityzone_idx 024 total in 1 blocks; 128 free (0 chunks); 896 used address_country_zip_cityzone_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used address_country_zip_base_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used address_country_state_postal_code_idx 024 total in 1 blocks; 192 free (0 chunks); 832 used address_country_state_city_idx 024 total in 1 blocks; 192 free (0 chunks); 832 used address_country_postal_code_address_idx 024 total in 1 blocks; 192 free (0 chunks); 832 used address_addres_id_idx 024 total in 1 blocks; 304 free (0 chunks); 720 used 
   address_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
facility_name_lower_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used facility_facility_type_code_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used facility_facility_country_state_postal_code_idx 024 total in 1 blocks; 128 free (0 chunks); 896 used facility_facility_country_state_city_idx 024 total in 1 blocks; 128 free (0 chunks); 896 used facility_country_zip_cityzone_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used facility_country_zip_base_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used facility_country_state_postal_code_idx 024 total in 1 blocks; 192 free (0 chunks); 832 used facility_country_state_city_idx 024 total in 1 blocks; 192 free (0 chunks); 832 used facility_country_postal_code_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used 
   facility_pkey: 1024 total in 1 blocks; 304 free (0 chunks); 720 use
facility_address_facility_address_id_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used facility_address_facility_address_address_type_idx 024 total in 1 blocks; 152 free (0 chunks); 872 used facility_address_address_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used facility_address_pkey 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_practice_faid_record_status_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used provider_practice_state_code_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_practice_record_status_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_practice_provider_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_practice_practice_tier_code_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_practice_pid_record_status_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used provider_practice_is_principal_record_status_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used provider_practice_is_principal_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used provider_practice_is_primary_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used provider_practice_facility_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_practice_facility_address_idx 024 total in 1 blocks; 304 free (0 chunks); 720 used provider_practice_default_state_country_idx 024 total in 1 blocks; 280 free (0 chunks); 744 used provider_practice_default_city_state_country_idx 024 total in 1 blocks; 192 free (0 chunks); 832 used provider_practice_default_base_zip_country_idx 024 total in 1 blocks; 192 free (0 chunks); 832 used provider_practice_dea_number_idx 024 total in 1 blocks; 344 free (0 chunks); 680 used provider_practice_pkey 024 total in 1 blocks; 344 free (0 chunks); 680 used pg_index_indrelid_index 024 total in 1 blocks; 304 free (0 chunks); 720 used pg_aggregate_fnoid_index 024 total in 1 blocks; 304 free (0 chunks); 720 used pg_amop_opr_fam_index 024 total in 1 blocks; 240 free (0 chunks); 784 used pg_proc_proname_args_nsp_index 024 total in 1 blocks; 152 free (0 chunks); 872 used pg_cast_source_target_index 024 total in 1 blocks; 240 free (0 chunks); 784 used 
   pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 use
   pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 use
pg_operator_oprname_l_r_n_index 024 total in 1 blocks; 88 free (0 chunks); 936 used pg_attrdef_adrelid_adnum_index 024 total in 1 blocks; 240 free (0 chunks); 784 used pg_class_relname_nsp_index 024 total in 1 blocks; 240 free (0 chunks); 784 used 
   pg_authid_oid_index 024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_namespace_nspname_index 024 total in 1 blocks; 304 free (0 chunks); 720 used 
   CachedPlan: 1024 total in 1 blocks; 40 free (0 chunks); 984 use
   CachedPlanSource: 3072 total in 2 blocks; 1688 free (0 chunks); 1384 use
   SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 use
pg_opclass_oid_index 024 total in 1 blocks; 304 free (0 chunks); 720 used pg_amop_fam_strat_index 024 total in 1 blocks; 88 free (0 chunks); 936 used pg_trigger_tgrelid_tgname_index 024 total in 1 blocks; 240 free (0 chunks); 784 used pg_attribute_relid_attnum_index 024 total in 1 blocks; 240 free (0 chunks); 784 used 
   pg_class_oid_index 024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_amproc_fam_proc_index 024 total in 1 blocks; 88 free (0 chunks); 936 used pg_index_indexrelid_index 024 total in 1 blocks; 304 free (0 chunks); 720 used pg_rewrite_rel_rulename_index 024 total in 1 blocks; 280 free (0 chunks); 744 used pg_operator_oid_index 024 total in 1 blocks; 304 free (0 chunks); 720 used 
 MdSmgr: 8192 total in 1 blocks; 5488 free (164 chunks); 2704 use
 LOCALLOCK hash: 24576 total in 2 blocks; 16168 free (4 chunks); 8408 use
Rendezvous variable hash 192 total in 1 blocks; 3848 free (0 chunks); 4344 used 
 Timezones: 49432 total in 2 blocks; 5968 free (0 chunks); 43464 use
 ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 use
2010-03-14 19:51:42 EDT ERROR out of memory
2010-03-14 19:51:42 EDT DETAIL Failed on request of size 134217728.
2010-03-14 19:51:42 EDT STATEMENT SELECT facility_id, street_address, base_zip, COUNT(*) AS provider_count 
  FROM (
     SELECT DISTINCT
        f.facility_id,
        p.provider_id,
TRIM(COALESCE(a.parsed_number, '')||' '||a.parsed_street) AS street_address, 
        SUBSTR(a.postal_code, 1, 5) AS base_zip
     FROM mdx_core.provider AS p
     JOIN mdx_core.provider_practice AS pp USING (provider_id)
     JOIN mdx_core.facility_address AS fa USING (facility_address_id)
     JOIN mdx_core.facility AS f ON f.facility_id = fa.facility_id
     JOIN mdx_core.address AS a USING (address_id)
     WHERE
        p.provider_status_code = 'A'
        AND pp.practice_tier_code <= '3'
  ) AS p_per_addr
  GROUP BY facility_id, street_address, base_zip
  HAVING COUNT(*) > 1


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux