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

run the script with bash -v or
-vvv for extra detailed vebose logging.

see whats wrong, most of the times a
matter using the right closure of the statements with ' or "

Henk Bronk



On 27 mrt. 2012, at 20:37, "W. David Jarvis" <william.d.jarvis@gmail.com> wrote:

> 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"
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>