Huge query stalls at PARSE/BIND stage (1) - Mailing list pgsql-bugs

(-resend)

Hi,

I have a nightly process which distills a range of statistics from a
third-party database into a set of temporary tables, and then from those
tables, aggregates and joins these figures into two main tables. Each
temporary table contains a moderate number of rows and few columns.

The main query is a large series of FULL JOINs and has worked perfectly
for a long time.

Yesterday I added 20 more join statements to the query (identical to
existing statements) and now the main query which would run in <30
seconds, runs indefinitely with the process stuck at "PARSE" in version
7.4, or "BIND" in version 8.0.3. The process is using all available CPU
but not a great deal of memory.

I've let this process run for over 50 minutes before it is killed by me
with signal ABRT. (Terminating the client connection does not stop the
process.)

If I remove these additional joins the statement perfectly again in <30
seconds.

I have upgraded Postgresql from version 7.4 to 8.0.3 but this does not
fix the behaviour.

The additional tables being full joined are generated with the same java
function as the other tables and the only variable being the names of
the non-key columns (different temporary tables for different columns of
data in the resulting table).

The data in the source tables of the additional statements I am adding
has an identical size and layout to the source tables from the existing
FULL JOIN statements.

The only difference with these new tables is that not all of the joined
columns are used in the select clause of the main query, some are
ignored, whereas in previous tables all columns available are used.


Here is the main query:


INSERT INTO pws_stats_case (
  --- Destination columns
  batch_id,csid,year,month,case_upload_date,
  num_live,amt_live,fbal_live,bal_live,fees_live,
  num_arrange,amt_arrange,fbal_arrange,bal_arrange,fees_arrange,
  num_fullypaid,amt_fullypaid,fbal_fullypaid,bal_fullypaid,fees_fullypaid,
  num_successful,amt_successful,fbal_successful,bal_successful,fees_successful,
  num_trace,amt_trace,fbal_trace,bal_trace,fees_trace,
  num_expired,amt_expired,fbal_expired,bal_expired,fees_expired,
  num_our_hold,amt_our_hold,fbal_our_hold,bal_our_hold,fees_our_hold,
  num_client_hold,amt_client_hold,fbal_client_hold,bal_client_hold,fees_client_hold,
  num_hold,amt_hold,fbal_hold,bal_hold,fees_hold,
  num_returned,amt_returned,fbal_returned,bal_returned,fees_returned,
  num_returned_err,amt_returned_err,fbal_returned_err,bal_returned_err,fees_returned_err,
  num_30days,amt_30days,fbal_30days,bal_30days,fees_30days,
  num_60days,amt_60days,fbal_60days,bal_60days,fees_60days,
  num_90days,amt_90days,fbal_90days,bal_90days,fees_90days,
  num_365days,amt_365days,fbal_365days,bal_365days,fees_365days,
  total_num_open,total_amt_open,total_fbal_open,total_bal_open,total_fees_open,total_collected_open,
  total_num_closed,total_amt_closed,total_fbal_closed,total_bal_closed,total_fees_closed,total_collected_closed,
  total_num,total_amt,total_fbal,total_bal,total_fees,total_collected,
  num_r_uncollectable,amt_r_uncollectable,fbal_r_uncollectable,bal_r_uncollectable,fees_r_uncollectable,
  num_r_collectable,amt_r_collectable,fbal_r_collectable,bal_r_collectable,fees_r_collectable,
  num_r_requested,amt_r_requested,fbal_r_requested,bal_r_requested,fees_r_requested,
  num_allocated,amt_allocated,fbal_allocated,bal_allocated,fees_allocated,
  num_returned_open,amt_returned_open,fbal_returned_open,bal_returned_open,fees_returned_open,
  num_returned_err_open,bal_returned_err_open,fbal_returned_err_open,amt_returned_err_open,fees_returned_err_open,

num_r_uncollectable_open,amt_r_uncollectable_open,bal_r_uncollectable_open,fbal_r_uncollectable_open,fees_r_uncollectable_open,
  num_r_collectable_open,amt_r_collectable_open,bal_r_collectable_open,fbal_r_collectable_open,fees_r_collectable_open,
  num_r_requested_open,amt_r_requested_open,bal_r_requested_open,fbal_r_requested_open,fees_r_requested_open,
  num_returned_closed,amt_returned_closed,fbal_returned_closed,bal_returned_closed,fees_returned_closed,

num_returned_err_closed,bal_returned_err_closed,fbal_returned_err_closed,amt_returned_err_closed,fees_returned_err_closed,

num_r_uncollectable_closed,amt_r_uncollectable_closed,bal_r_uncollectable_closed,fbal_r_uncollectable_closed,fees_r_uncollectable_closed,

num_r_collectable_closed,amt_r_collectable_closed,bal_r_collectable_closed,fbal_r_collectable_closed,fees_r_collectable_closed,
  num_r_requested_closed,amt_r_requested_closed,bal_r_requested_closed,fbal_r_requested_closed,fees_r_requested_closed,
  num_open_expired,amt_open_expired,fbal_open_expired,bal_open_expired,fees_open_expired,
  num_closed_expired,amt_closed_expired,fbal_closed_expired,bal_closed_expired,fees_closed_expired,
  num_open_successful,amt_open_successful,fbal_open_successful,bal_open_successful,fees_open_successful,
  num_closed_successful,amt_closed_successful,fbal_closed_successful,bal_closed_successful,fees_closed_successful
)

-- Select query, huge union in two parts.
-- part one's tables are keyed on (csid=?,year=?,month=?,case_upload_date=null),
-- part two's tables are keyed on (csid=?,year=0,month=0,case_upload_date=?)

   SELECT ?,csid,year,month,NULL::date AS case_upload_date,
   -- Same list of destination columns, but all coalesced with 0 to prevent NULLs
COALESCE(num_live, 0), COALESCE(amt_live, 0), COALESCE(fbal_live, 0), COALESCE(bal_live, 0), COALESCE(fees_live, 0),
COALESCE(num_arrange, 0), COALESCE(amt_arrange, 0), COALESCE(fbal_arrange, 0), COALESCE(bal_arrange, 0),
COALESCE(fees_arrange, 0), COALESCE(num_fullypaid, 0), COALESCE(amt_fullypaid, 0), COALESCE(fbal_fullypaid, 0),
COALESCE(bal_fullypaid, 0), COALESCE(fees_fullypaid, 0), COALESCE(num_successful, 0), COALESCE(amt_successful, 0),
COALESCE(fbal_successful, 0), COALESCE(bal_successful, 0), COALESCE(fees_successful, 0), COALESCE(num_trace, 0),
COALESCE(amt_trace, 0), COALESCE(fbal_trace, 0), COALESCE(bal_trace, 0), COALESCE(fees_trace, 0), COALESCE(num_expired,
0), 
COALESCE(amt_expired, 0), COALESCE(fbal_expired, 0), COALESCE(bal_expired, 0), COALESCE(fees_expired, 0),
COALESCE(num_our_hold, 0), COALESCE(amt_our_hold, 0), COALESCE(fbal_our_hold, 0), COALESCE(bal_our_hold, 0),
COALESCE(fees_our_hold, 0), COALESCE(num_client_hold, 0), COALESCE(amt_client_hold, 0), COALESCE(fbal_client_hold, 0),
COALESCE(bal_client_hold, 0), COALESCE(fees_client_hold, 0), COALESCE(num_hold, 0), COALESCE(amt_hold, 0),
COALESCE(fbal_hold, 0), COALESCE(bal_hold, 0), COALESCE(fees_hold, 0), COALESCE(num_returned, 0),
COALESCE(amt_returned, 0), COALESCE(fbal_returned, 0), COALESCE(bal_returned, 0), COALESCE(fees_returned, 0),
COALESCE(num_returned_err, 0), COALESCE(amt_returned_err, 0), COALESCE(fbal_returned_err, 0),
COALESCE(bal_returned_err, 0), COALESCE(fees_returned_err, 0), COALESCE(num_30days, 0), COALESCE(amt_30days, 0),
COALESCE(fbal_30days, 0), COALESCE(bal_30days, 0), COALESCE(fees_30days, 0), COALESCE(num_60days, 0),
COALESCE(amt_60days, 0), COALESCE(fbal_60days, 0), COALESCE(bal_60days, 0), COALESCE(fees_60days, 0),
COALESCE(num_90days, 0), COALESCE(amt_90days, 0), COALESCE(fbal_90days, 0), COALESCE(bal_90days, 0),
COALESCE(fees_90days, 0), COALESCE(num_365days, 0), COALESCE(amt_365days, 0), COALESCE(fbal_365days, 0),
COALESCE(bal_365days, 0), COALESCE(fees_365days, 0), COALESCE(total_num_open, 0), COALESCE(total_amt_open, 0),
COALESCE(total_fbal_open, 0), COALESCE(total_bal_open, 0), COALESCE(total_fees_open, 0), COALESCE(total_collected_open,
0), 
COALESCE(total_num_closed, 0), COALESCE(total_amt_closed, 0), COALESCE(total_fbal_closed, 0),
COALESCE(total_bal_closed,0),  
COALESCE(total_fees_closed, 0), COALESCE(total_collected_closed, 0), COALESCE(total_num, 0), COALESCE(total_amt, 0),
COALESCE(total_fbal, 0), COALESCE(total_bal, 0), COALESCE(total_fees, 0), COALESCE(total_collected, 0),
COALESCE(num_r_uncollectable, 0), COALESCE(amt_r_uncollectable, 0), COALESCE(fbal_r_uncollectable, 0),
COALESCE(bal_r_uncollectable, 0), COALESCE(fees_r_uncollectable, 0), COALESCE(num_r_collectable, 0),
COALESCE(amt_r_collectable, 0), COALESCE(fbal_r_collectable, 0), COALESCE(bal_r_collectable, 0),
COALESCE(fees_r_collectable, 0), COALESCE(num_r_requested, 0), COALESCE(amt_r_requested, 0), COALESCE(fbal_r_requested,
0), 
COALESCE(bal_r_requested, 0), COALESCE(fees_r_requested, 0), COALESCE(num_allocated, 0), COALESCE(amt_allocated, 0),
COALESCE(fbal_allocated, 0), COALESCE(bal_allocated, 0), COALESCE(fees_allocated, 0), COALESCE(num_returned_open, 0),
COALESCE(amt_returned_open, 0), COALESCE(fbal_returned_open, 0), COALESCE(bal_returned_open, 0),
COALESCE(fees_returned_open, 0), COALESCE(num_returned_err_open, 0), COALESCE(bal_returned_err_open, 0),
COALESCE(fbal_returned_err_open, 0), COALESCE(amt_returned_err_open, 0), COALESCE(fees_returned_err_open, 0),
COALESCE(num_r_uncollectable_open, 0), COALESCE(amt_r_uncollectable_open, 0), COALESCE(bal_r_uncollectable_open, 0),
COALESCE(fbal_r_uncollectable_open, 0), COALESCE(fees_r_uncollectable_open, 0), COALESCE(num_r_collectable_open, 0),
COALESCE(amt_r_collectable_open, 0), COALESCE(bal_r_collectable_open, 0), COALESCE(fbal_r_collectable_open, 0),
COALESCE(fees_r_collectable_open, 0), COALESCE(num_r_requested_open, 0), COALESCE(amt_r_requested_open, 0),
COALESCE(bal_r_requested_open, 0), COALESCE(fbal_r_requested_open, 0), COALESCE(fees_r_requested_open, 0),
COALESCE(num_returned_closed, 0), COALESCE(amt_returned_closed, 0), COALESCE(fbal_returned_closed, 0),
COALESCE(bal_returned_closed, 0), COALESCE(fees_returned_closed, 0), COALESCE(num_returned_err_closed, 0),
COALESCE(bal_returned_err_closed, 0), COALESCE(fbal_returned_err_closed, 0), COALESCE(amt_returned_err_closed, 0),
COALESCE(fees_returned_err_closed, 0), COALESCE(num_r_uncollectable_closed, 0), COALESCE(amt_r_uncollectable_closed,
0),COALESCE(bal_r_uncollectable_closed, 0), COALESCE(fbal_r_uncollectable_closed, 0),
COALESCE(fees_r_uncollectable_closed,0),  
COALESCE(num_r_collectable_closed, 0), COALESCE(amt_r_collectable_closed, 0), COALESCE(bal_r_collectable_closed, 0),
COALESCE(fbal_r_collectable_closed, 0), COALESCE(fees_r_collectable_closed, 0), COALESCE(num_r_requested_closed, 0),
COALESCE(amt_r_requested_closed, 0), COALESCE(bal_r_requested_closed, 0), COALESCE(fbal_r_requested_closed, 0),
COALESCE(fees_r_requested_closed, 0), COALESCE(num_open_expired, 0), COALESCE(amt_open_expired, 0),
COALESCE(fbal_open_expired, 0), COALESCE(bal_open_expired, 0), COALESCE(fees_open_expired, 0),
COALESCE(num_closed_expired, 0), COALESCE(amt_closed_expired, 0), COALESCE(fbal_closed_expired, 0),
COALESCE(bal_closed_expired, 0), COALESCE(fees_closed_expired, 0), COALESCE(num_open_successful, 0),
COALESCE(amt_open_successful, 0), COALESCE(fbal_open_successful, 0), COALESCE(bal_open_successful, 0),
COALESCE(fees_open_successful, 0), COALESCE(num_closed_successful, 0), COALESCE(amt_closed_successful, 0),
COALESCE(fbal_closed_successful, 0), COALESCE(bal_closed_successful, 0), COALESCE(fees_closed_successful, 0)

   --- Join account status crosstabs
   FROM      tmp_stats_dsm_crosstab_num s

{(2) ...
   FULL JOIN tmp_stats_dsm_crosstab_amt USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_fbal USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_bal USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_fees USING (csid,year,month)
... }

{(1) ...
   FULL JOIN tmp_stats_dsm_crosstab_num_open USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_amt_open USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_fbal_open USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_bal_open USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_fees_open USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_num_closed USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_amt_closed USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_fbal_closed USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_bal_closed USING (csid,year,month)
   FULL JOIN tmp_stats_dsm_crosstab_fees_closed USING (csid,year,month)
... }

   -- Join account hold crosstabs
   FULL JOIN tmp_stats_dhm_crosstab_num USING (csid,year,month)
   FULL JOIN tmp_stats_dhm_crosstab_amt USING (csid,year,month)
   FULL JOIN tmp_stats_dhm_crosstab_fbal USING (csid,year,month)
   FULL JOIN tmp_stats_dhm_crosstab_bal USING (csid,year,month)
   FULL JOIN tmp_stats_dhm_crosstab_fees USING (csid,year,month)
   -- Join account return totals
   FULL JOIN (
     SELECT csid,year,month,
     sum(num) AS num_returned,sum(amt) AS amt_returned,sum(fbal) AS fbal_returned, sum(bal) AS bal_returned,sum(fees)
ASfees_returned, 
     sum(num_open) AS num_returned_open,sum(amt_open) AS amt_returned_open,sum(fbal_open) AS fbal_returned_open,
sum(bal)AS bal_returned_open,sum(fees_open) AS fees_returned_open, 
     sum(num_closed) AS num_returned_closed,sum(amt_closed) AS amt_returned_closed,sum(fbal_closed) AS
fbal_returned_closed,sum(bal) AS bal_returned_closed,sum(fees_closed) AS fees_returned_closed  
     FROM pws_stats_returns WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL AND return_code != 20
GROUPBY csid,year,month  
   ) total_drm USING (csid,year,month)
   FULL JOIN (
     SELECT csid,year,month,
     sum(num) AS num_returned_err,sum(amt) AS amt_returned_err,sum(fbal) AS fbal_returned_err, sum(bal) AS
bal_returned_err,sum(fees)AS fees_returned_err, 
     sum(num_open) AS num_returned_err_open,sum(amt_open) AS amt_returned_err_open,sum(fbal_open) AS
fbal_returned_err_open,sum(bal) AS bal_returned_err_open,sum(fees_open) AS fees_returned_err_open, 
     sum(num_closed) AS num_returned_err_closed,sum(amt_closed) AS amt_returned_err_closed,sum(fbal_closed) AS
fbal_returned_err_closed,sum(bal) AS bal_returned_err_closed,sum(fees_closed) AS fees_returned_err_closed  
     FROM pws_stats_returns WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL AND return_code = 20
GROUPBY csid,year,month  
   ) total_drme USING (csid,year,month)
   -- Join account uncollectable/collectable/requested
   FULL JOIN (
     SELECT csid,year,month,
     sum(num) AS num_r_uncollectable,sum(amt) AS amt_r_uncollectable,sum(fbal) AS fbal_r_uncollectable, sum(bal) AS
bal_r_uncollectable,sum(fees)AS fees_r_uncollectable, 
     sum(num_open) AS num_r_uncollectable_open,sum(amt_open) AS amt_r_uncollectable_open,sum(fbal_open) AS
fbal_r_uncollectable_open,sum(bal) AS bal_r_uncollectable_open,sum(fees_open) AS fees_r_uncollectable_open, 
     sum(num_closed) AS num_r_uncollectable_closed,sum(amt_closed) AS amt_r_uncollectable_closed,sum(fbal_closed) AS
fbal_r_uncollectable_closed,sum(bal) AS bal_r_uncollectable_closed,sum(fees_closed) AS fees_r_uncollectable_closed  
     FROM pws_stats_returns, pws_return_codes rc WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL
   AND return_code = rc.id AND rc.nature = 'U' AND return_code != 20 GROUP BY csid,year,month
   ) total_rum USING (csid,year,month)
   FULL JOIN (
     SELECT csid,year,month,
     sum(num) AS num_r_collectable,sum(amt) AS amt_r_collectable,sum(fbal) AS fbal_r_collectable, sum(bal) AS
bal_r_collectable,sum(fees)AS fees_r_collectable, 
     sum(num_open) AS num_r_collectable_open,sum(amt_open) AS amt_r_collectable_open,sum(fbal_open) AS
fbal_r_collectable_open,sum(bal) AS bal_r_collectable_open,sum(fees_open) AS fees_r_collectable_open, 
     sum(num_closed) AS num_r_collectable_closed,sum(amt_closed) AS amt_r_collectable_closed,sum(fbal_closed) AS
fbal_r_collectable_closed,sum(bal) AS bal_r_collectable_closed,sum(fees_closed) AS fees_r_collectable_closed  
     FROM pws_stats_returns, pws_return_codes rc WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL
   AND return_code = rc.id AND rc.nature = 'F' AND return_code != 20 GROUP BY csid,year,month
   ) total_rcm USING (csid,year,month)
   FULL JOIN (
     SELECT csid,year,month,
     sum(num) AS num_r_requested,sum(amt) AS amt_r_requested,sum(fbal) AS fbal_r_requested, sum(bal) AS
bal_r_requested,sum(fees)AS fees_r_requested, 
     sum(num_open) AS num_r_requested_open,sum(amt_open) AS amt_r_requested_open,sum(fbal_open) AS
fbal_r_requested_open,sum(bal) AS bal_r_requested_open,sum(fees_open) AS fees_r_requested_open, 
     sum(num_closed) AS num_r_requested_closed,sum(amt_closed) AS amt_r_requested_closed,sum(fbal_closed) AS
fbal_r_requested_closed,sum(bal) AS bal_r_requested_closed,sum(fees_closed) AS fees_r_requested_closed  
     FROM pws_stats_returns, pws_return_codes rc WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL
   AND return_code = rc.id AND rc.req_by_client = true AND return_code != 20 GROUP BY csid,year,month
   ) total_rrm USING (csid,year,month)
   -- Join account age bands
   FULL JOIN (
     SELECT csid,year,month,
     num AS num_30days,amt AS amt_30days,fbal AS fbal_30days,bal AS bal_30days,fees AS fees_30days
     FROM tmp_stats_dam30
   ) total_dam30 USING (csid,year,month)
   FULL JOIN (
     SELECT csid,year,month,
     num AS num_60days,amt AS amt_60days,fbal AS fbal_60days,bal AS bal_60days,fees AS fees_60days
     FROM tmp_stats_dam60
   ) total_dam60 USING (csid,year,month)
   FULL JOIN (
     SELECT csid,year,month,
     num AS num_90days,amt AS amt_90days,fbal AS fbal_90days,bal AS bal_90days,fees AS fees_90days
     FROM tmp_stats_dam90
   ) total_dam90 USING (csid,year,month)
   FULL JOIN (
     SELECT csid,year,month,
     num AS num_365days,amt AS amt_365days,fbal AS fbal_365days,bal AS bal_365days,fees AS fees_365days
     FROM tmp_stats_dam365
   ) total_dam365 USING (csid,year,month)
   -- Join summary totals
   FULL JOIN (
     SELECT csid,year,month,
     num AS total_num_open,amt AS total_amt_open,fbal AS total_fbal_open,bal AS total_bal_open,fees AS
total_fees_open,(amt-bal)AS total_collected_open  
     FROM tmp_stats_zom
   ) zom USING (csid,year,month)
   FULL JOIN (
     SELECT csid,year,month,
     num AS total_num_closed,amt AS total_amt_closed,fbal AS total_fbal_closed,bal AS total_bal_closed,fees AS
total_fees_closed,(amt-bal)AS total_collected_closed  
     FROM tmp_stats_zcm
   ) zcm USING (csid,year,month)
   FULL JOIN (
     SELECT csid,year,month,
     num AS total_num,amt AS total_amt,fbal AS total_fbal,bal AS total_bal,fees AS total_fees,(amt-bal) AS
total_collected 
     FROM tmp_stats_zam
   ) zam USING (csid,year,month)
   -- Join agent totals
   FULL JOIN (
     SELECT csid,year,month,
     num AS num_allocated,amt AS amt_allocated,fbal AS fbal_allocated,bal AS bal_allocated,fees AS fees_allocated
     FROM tmp_stats_bam
   ) bam USING (csid,year,month)
   UNION  -- ---- End per-month/year/total stats, begin per-batch ----
   SELECT ?,csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,

-- Same destination column list as above, coalesced with 0
COALESCE(num_live, 0), COALESCE(amt_live, 0), COALESCE(fbal_live, 0), COALESCE(bal_live, 0), COALESCE(fees_live, 0),
COALESCE(num_arrange, 0), COALESCE(amt_arrange, 0), COALESCE(fbal_arrange, 0), COALESCE(bal_arrange, 0),
COALESCE(fees_arrange, 0), COALESCE(num_fullypaid, 0), COALESCE(amt_fullypaid, 0), COALESCE(fbal_fullypaid, 0),
COALESCE(bal_fullypaid, 0), COALESCE(fees_fullypaid, 0), COALESCE(num_successful, 0), COALESCE(amt_successful, 0),
COALESCE(fbal_successful, 0), COALESCE(bal_successful, 0), COALESCE(fees_successful, 0), COALESCE(num_trace, 0),
COALESCE(amt_trace, 0), COALESCE(fbal_trace, 0), COALESCE(bal_trace, 0), COALESCE(fees_trace, 0), COALESCE(num_expired,
0), 
COALESCE(amt_expired, 0), COALESCE(fbal_expired, 0), COALESCE(bal_expired, 0), COALESCE(fees_expired, 0),
COALESCE(num_our_hold, 0), COALESCE(amt_our_hold, 0), COALESCE(fbal_our_hold, 0), COALESCE(bal_our_hold, 0),
COALESCE(fees_our_hold, 0), COALESCE(num_client_hold, 0), COALESCE(amt_client_hold, 0), COALESCE(fbal_client_hold, 0),
COALESCE(bal_client_hold, 0), COALESCE(fees_client_hold, 0), COALESCE(num_hold, 0), COALESCE(amt_hold, 0),
COALESCE(fbal_hold, 0), COALESCE(bal_hold, 0), COALESCE(fees_hold, 0), COALESCE(num_returned, 0),
COALESCE(amt_returned, 0), COALESCE(fbal_returned, 0), COALESCE(bal_returned, 0), COALESCE(fees_returned, 0),
COALESCE(num_returned_err, 0), COALESCE(amt_returned_err, 0), COALESCE(fbal_returned_err, 0),
COALESCE(bal_returned_err, 0), COALESCE(fees_returned_err, 0), COALESCE(num_30days, 0), COALESCE(amt_30days, 0),
COALESCE(fbal_30days, 0), COALESCE(bal_30days, 0), COALESCE(fees_30days, 0), COALESCE(num_60days, 0),
COALESCE(amt_60days, 0), COALESCE(fbal_60days, 0), COALESCE(bal_60days, 0), COALESCE(fees_60days, 0),
COALESCE(num_90days, 0), COALESCE(amt_90days, 0), COALESCE(fbal_90days, 0), COALESCE(bal_90days, 0),
COALESCE(fees_90days, 0), COALESCE(num_365days, 0), COALESCE(amt_365days, 0), COALESCE(fbal_365days, 0),
COALESCE(bal_365days, 0), COALESCE(fees_365days, 0), COALESCE(total_num_open, 0), COALESCE(total_amt_open, 0),
COALESCE(total_fbal_open, 0), COALESCE(total_bal_open, 0), COALESCE(total_fees_open, 0), COALESCE(total_collected_open,
0), 
COALESCE(total_num_closed, 0), COALESCE(total_amt_closed, 0), COALESCE(total_fbal_closed, 0),
COALESCE(total_bal_closed,0),  
COALESCE(total_fees_closed, 0), COALESCE(total_collected_closed, 0), COALESCE(total_num, 0), COALESCE(total_amt, 0),
COALESCE(total_fbal, 0), COALESCE(total_bal, 0), COALESCE(total_fees, 0), COALESCE(total_collected, 0),
COALESCE(num_r_uncollectable, 0), COALESCE(amt_r_uncollectable, 0), COALESCE(fbal_r_uncollectable, 0),
COALESCE(bal_r_uncollectable, 0), COALESCE(fees_r_uncollectable, 0), COALESCE(num_r_collectable, 0),
COALESCE(amt_r_collectable, 0), COALESCE(fbal_r_collectable, 0), COALESCE(bal_r_collectable, 0),
COALESCE(fees_r_collectable, 0), COALESCE(num_r_requested, 0), COALESCE(amt_r_requested, 0), COALESCE(fbal_r_requested,
0), 
COALESCE(bal_r_requested, 0), COALESCE(fees_r_requested, 0), COALESCE(num_allocated, 0), COALESCE(amt_allocated, 0),
COALESCE(fbal_allocated, 0), COALESCE(bal_allocated, 0), COALESCE(fees_allocated, 0), COALESCE(num_returned_open, 0),
COALESCE(amt_returned_open, 0), COALESCE(fbal_returned_open, 0), COALESCE(bal_returned_open, 0),
COALESCE(fees_returned_open, 0), COALESCE(num_returned_err_open, 0), COALESCE(bal_returned_err_open, 0),
COALESCE(fbal_returned_err_open, 0), COALESCE(amt_returned_err_open, 0), COALESCE(fees_returned_err_open, 0),
COALESCE(num_r_uncollectable_open, 0), COALESCE(amt_r_uncollectable_open, 0), COALESCE(bal_r_uncollectable_open, 0),
COALESCE(fbal_r_uncollectable_open, 0), COALESCE(fees_r_uncollectable_open, 0), COALESCE(num_r_collectable_open, 0),
COALESCE(amt_r_collectable_open, 0), COALESCE(bal_r_collectable_open, 0), COALESCE(fbal_r_collectable_open, 0),
COALESCE(fees_r_collectable_open, 0), COALESCE(num_r_requested_open, 0), COALESCE(amt_r_requested_open, 0),
COALESCE(bal_r_requested_open, 0), COALESCE(fbal_r_requested_open, 0), COALESCE(fees_r_requested_open, 0),
COALESCE(num_returned_closed, 0), COALESCE(amt_returned_closed, 0), COALESCE(fbal_returned_closed, 0),
COALESCE(bal_returned_closed, 0), COALESCE(fees_returned_closed, 0), COALESCE(num_returned_err_closed, 0),
COALESCE(bal_returned_err_closed, 0), COALESCE(fbal_returned_err_closed, 0), COALESCE(amt_returned_err_closed, 0),
COALESCE(fees_returned_err_closed, 0), COALESCE(num_r_uncollectable_closed, 0), COALESCE(amt_r_uncollectable_closed,
0),COALESCE(bal_r_uncollectable_closed, 0), COALESCE(fbal_r_uncollectable_closed, 0),
COALESCE(fees_r_uncollectable_closed,0),  
COALESCE(num_r_collectable_closed, 0), COALESCE(amt_r_collectable_closed, 0), COALESCE(bal_r_collectable_closed, 0),
COALESCE(fbal_r_collectable_closed, 0), COALESCE(fees_r_collectable_closed, 0), COALESCE(num_r_requested_closed, 0),
COALESCE(amt_r_requested_closed, 0), COALESCE(bal_r_requested_closed, 0), COALESCE(fbal_r_requested_closed, 0),
COALESCE(fees_r_requested_closed, 0), COALESCE(num_open_expired, 0), COALESCE(amt_open_expired, 0),
COALESCE(fbal_open_expired, 0), COALESCE(bal_open_expired, 0), COALESCE(fees_open_expired, 0),
COALESCE(num_closed_expired, 0), COALESCE(amt_closed_expired, 0), COALESCE(fbal_closed_expired, 0),
COALESCE(bal_closed_expired, 0), COALESCE(fees_closed_expired, 0), COALESCE(num_open_successful, 0),
COALESCE(amt_open_successful, 0), COALESCE(fbal_open_successful, 0), COALESCE(bal_open_successful, 0),
COALESCE(fees_open_successful, 0), COALESCE(num_closed_successful, 0), COALESCE(amt_closed_successful, 0),
COALESCE(fbal_closed_successful, 0), COALESCE(bal_closed_successful, 0), COALESCE(fees_closed_successful, 0)

   -- Join account status crosstabs
    FROM     tmp_stats_dsb_crosstab_num s

{(2) ...
   FULL JOIN tmp_stats_dsb_crosstab_amt USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_fbal USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_bal USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_fees USING (csid,case_upload_date)
... }

{(1) ...
   FULL JOIN tmp_stats_dsb_crosstab_num_open USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_amt_open USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_fbal_open USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_bal_open USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_fees_open USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_num_closed USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_amt_closed USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_fbal_closed USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_bal_closed USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dsb_crosstab_fees_closed USING (csid,case_upload_date)
... }


.. Please see email #2 (MAIL TOO LONG for pg-bugs LIST).........