Valid query times out when run from bash script - Mailing list pgsql-general

Hello all -

I've been trying to get a bash script set-up that creates a DB, does a
join, writes the join to file, and then cleans itself up afterwards.
Everything within the script runs fine when entered at an actual
prompt, but when I run the script the SELECT query runs indefinitely.
I'm not sure what the cause is—the script is being run on a box
connecting to its own local postgres installation, so it can't be a
connection issue. Any thoughts?

The bash script is included below; postgres version is 9.0.4. Any help
very much appreciated :)

#!/bin/bash

: ${1?"ERROR: Incorrect number of arguments (files have not been
properly specified). Proper format is 'mta-join old_mta_file
new_mta_file desired_output_filename.'"}
: ${2?"ERROR: Incorrect number of arguments (files have not been
properly specified). Proper format is 'mta-join old_mta_file
new_mta_file desired_output_filename.'"}
: ${3?"ERROR: Incorrect number of arguments (files have not been
properly specified). Proper format is 'mta-join old_mta_file
new_mta_file desired_output_filename.'"}

OLD_MTA_FILE=$1
NEW_MTA_FILE=$2
OUTPUT_FILE=$3

# Figure out how many days we need columns for
export NEW_MTA_COLWIDTH=`head -1 $NEW_MTA_FILE | grep ',' -o | wc -l`
NEW_MTA_COLWIDTH=($NEW_MTA_COLWIDTH-7)/4

# Assemble the extra SQL for the above
ADDITIONAL_CREATION_FIELDS=""
ADDITIONAL_SELECTION_FIELDS=""
for (( c=0; c<=$NEW_MTA_COLWIDTH; c++ ))
do
        ADDITIONAL_CREATION_FIELDS=$ADDITIONAL_CREATION_FIELDS"DAY_"$c"
varchar(255), event_count_"$c" numeric(20,10), conversions_"$c"
numeric(20,10), revenue_"$c" numeric(20,10), "
        ADDITIONAL_SELECTION_FIELDS=$ADDITIONAL_SELECTION_FIELDS"new_mta.DAY_"$c",
new_mta.event_count_"$c", new_mta.conversions_"$c",
new_mta.revenue_"$c", "
done

# Let's get rid of that extra comma at the end.

ADDITIONAL_CREATION_FIELDS=${ADDITIONAL_CREATION_FIELDS:0:${#ADDITIONAL_CREATION_FIELDS}-2}
ADDITIONAL_SELECTION_FIELDS=${ADDITIONAL_SELECTION_FIELDS:0:${#ADDITIONAL_SELECTION_FIELDS}-2}

echo -n "Creating database mta-join..."
createdb mta-join
echo -e "Done!\n"

echo "Creating table new_mta..."
# PSQL commands start here
psql mta-join<<EOF
CREATE TABLE new_mta (
report_date             date,
campaign_id             integer,
tracking_campaign_id    integer,
placement_id            integer,
creative_id             integer,
package_id              integer,
audience_id             integer,
$ADDITIONAL_CREATION_FIELDS);
EOF
echo -e "Done!\n"

echo -n "Inserting new mta file into new_mta table..."
psql mta-join<<EOF
\copy new_mta from '$NEW_MTA_FILE' with delimiter ','
\q
EOF
echo -e "Done!\n"

echo "Creating table old_mta..."
# PSQL commands start here
psql mta-join<<EOF
CREATE TABLE old_mta (
report_date_day         date,
report_date_week        date,
report_date_month       date,
campaign_name           varchar(255),
package_name            varchar(255),
audience_name           varchar(255),
inventory_provider_name varchar(255),
placement_name          varchar(255),
creative_size           varchar(255),
creative_name           varchar(255),
impressions             bigint,
data_cost               numeric(20,10),
media_cost              numeric(20,10),
gross_cost              numeric(20,10),
clicks                  integer,
lta_click_actions       integer,
lta_view_actions        integer,
lta_click_revenue       integer,
lta_view_revenue        integer,
mta_actions             numeric(20,10),
mta_revenue             integer,
mta_action_count        integer,
mta_seconds             integer,
campaign_id             integer,
placement_id            bigint,
creative_id             bigint,
package_id              bigint,
audience_id             integer);
\q
EOF
echo -e "Done!\n"

# Upload old MTA file into table old_mta
echo -n "Inserting old mta file into old_mta table..."
psql mta-join<<EOF
\COPY old_mta from '$OLD_MTA_FILE' with delimiter ',' CSV header;
EOF
echo -e "Done!\n"

# Create a bunch of indexes
echo -n "Creating table indexes for faster querying..."
psql mta-join<<EOF
CREATE INDEX campaign_id_index_old ON old_mta (campaign_id);
CREATE INDEX campaign_id_index_new ON new_mta (campaign_id);
CREATE INDEX placement_id_index_old ON old_mta (placement_id);
CREATE INDEX placement_id_index_new ON new_mta (placement_id);
CREATE INDEX creative_id_index_old ON old_mta (creative_id);
CREATE INDEX creative_id_index_new ON new_mta (creative_id);
CREATE INDEX package_id_index_old ON old_mta (package_id);
CREATE INDEX package_id_index_new ON new_mta (package_id);
CREATE INDEX audience_id_index_old ON old_mta (audience_id);
CREATE INDEX audience_id_index_new ON old_mta (audience_id);
\q
EOF
echo -e "Done!\n"

echo "Writing join to file..."
psql -d mta-join -t -A -F ',' -c 'copy (SELECT * FROM new_mta, old_mta
WHERE report_date = report_date_day AND new_mta.campaign_id =
old_mta.campaign_id AND new_mta.placement_id = old_mta.placement_id
AND new_mta.creative_id = old_mta.creative_id AND new_mta.package_id =
old_mta.package_id AND new_mta.audience_id = old_mta.audience_id) to
stdout;' > $OUTPUT_FILE
echo -e "Done!\n"

# Clearing the newly created tables
echo "Cleaning up postgres installation..."
psql mta-join<<EOF
DROP TABLE new_mta;
DROP TABLE old_mta;
EOF

# Drop the mta-join database
dropdb mta-join
echo -e "Done!\r"