function crashes backend - Mailing list pgsql-hackers

Hello,

I am writing a SPI function to run maintenance tasks on my auction
system but it keeps crashing the backend after running only one loop.
Now, I am not a C programmer, nor do I have any formal training in CS. I
thought I might run this function by you guys so that a cursory look
might reveal some obvious coding mistake? 

Thanks in advance for your insight.

int4 auction_maintenance(void) {
char * query, * default_locale = getenv("LC_ALL");bool current, isnull;int i;
/* Connect to SPI manager */if (SPI_connect() != SPI_OK_CONNECT)    elog(ERROR, "bid_control.c: SPI_connect failed");

/*    asprintf(&query, "BEGIN");SPI_exec(query, 0);free(query);*/
/* check if last modification time of special user id 0 is less than 15 * minutes ago */asprintf(&query, "SELECT
((now()::abstime::int4- modified::abstime::int4) \        / 60) < 15 AS current FROM person WHERE id = 0 FOR
UPDATE");SPI_exec(query,0);free(query);
 
current = DatumGetChar(SPI_getbinval(        SPI_tuptable->vals[0], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"current"), &isnull));
 
if (current) {    /* maintenance script ran less that 15 minutes ago, do nothing     */
/*        asprintf(&query, "COMMIT");    SPI_exec(query, 0);    free(query);*/
    elog(NOTICE, "auction system still current");
    SPI_finish();    return current;}
/* update modification time now, locking other daemons out */asprintf(&query, "UPDATE person SET modified = now() WHERE
id= 0");SPI_exec(query, 0);free(query);
 

/*    asprintf(&query, "COMMIT");SPI_exec(query, 0);free(query);*/
/* start real mainenance work here */

/*    asprintf(&query, "BEGIN");SPI_exec(query, 0);free(query);*/
/* select all auctions that have expired and have not been notified */asprintf(&query, "SELECT *,auction_status(a.id),
\   seller.mail AS seller_mail, seller.locale  AS seller_locale, \    seller.login AS seller_login \    FROM auction a,
personseller WHERE auction_status(a.id) <= 0 \    AND a.person_id = seller.id \    AND (notified IS FALSE OR notified
ISNULL) FOR UPDATE");SPI_exec(query, 0);free(query);
 
for (i = SPI_processed - 1; i >= 0; i--) {
    int type = DatumGetInt32(SPI_getbinval(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"type"), &isnull));
 
    char * title = SPI_getvalue(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"title"));
 
    char * seller_mail = SPI_getvalue(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"seller_mail"));
 
    char * seller_locale = SPI_getvalue(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"seller_locale"));
 
    char * seller_login = SPI_getvalue(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"seller_login"));
 
    char * stopdate = SPI_getvalue(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"stopdate"));
 
    int auction_id = DatumGetInt32(SPI_getbinval(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"id"), &isnull));
 
    int lot = DatumGetInt32(SPI_getbinval(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"lot"), &isnull));
 
    int auction_status = DatumGetInt32(SPI_getbinval(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
 SPI_fnumber(SPI_tuptable->tupdesc, "auction_status"), &isnull));
 
    int renew_count = DatumGetInt32(SPI_getbinval(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"renew_count"), &isnull));
 

/*        bool auto_renew = DatumGetChar(SPI_getbinval(            SPI_tuptable->vals[i], SPI_tuptable->tupdesc,
   SPI_fnumber(SPI_tuptable->tupdesc, "auto_renew"), &isnull));*/
 
    elog(NOTICE, "Processing auction #%d of %d (\n"            "type: %d\n"            "title: %s\n"
"seller_mail:%s\n"            "seller_locale: %s\n"            "seller_login: %s\n"            "stopdate: %s\n"
  "id: %d\n"            "lot: %d\n"            "status: %d\n"            "renew_count: %d\n"            ")",
SPI_processed- i, SPI_processed,             type,            title,             seller_mail,            seller_locale,
          seller_login,            stopdate,            auction_id,             lot,            auction_status,
  renew_count            );
 
    /* FIRST, store a copy of this auction in the archive, before eventually     * running UPDATE or DELETE on it
*/   asprintf(&query, "INSERT INTO auction_archive SELECT * FROM auction \        WHERE id = %d", auction_id);
SPI_exec(query,0);    free(query);
 
    /* store a copy of all bids into archive     */    asprintf(&query, "INSERT INTO bid_archive SELECT * FROM bid \
   WHERE auction_id = %d", auction_id);    SPI_exec(query, 0);    free(query);
 
/*#if 0*/    /* winner/seller notification     */    if (auction_status != -lot) { /* something was sold */        char
*mess;        char **bidder_login, **bidder_mail, **bidder_locale;        int *bid_lot, *bidder_id, j, l;        double
*bid_price;
        /* get high bidders         */        asprintf(&query, "SELECT max(b.lot) AS bid_lot, \            max(b.price)
ASbid_price,p.login AS bidder_login, \            p.id AS bidder_id, p.mail AS bidder_mail, \            p.locale AS
bidder_locale\            FROM bid b, person p \            WHERE b.auction_id = %d AND p.id = b.person_id \
GROUPBY p.login, p.id, p.mail,p.locale \            ORDER BY max(price)", auction_id);        SPI_exec(query, 0);
free(query);
 
        bid_price = alloca(SPI_processed * sizeof(double));        bid_lot = alloca(SPI_processed * sizeof(int));
bidder_id = alloca(SPI_processed * sizeof(int));
 
        bidder_login = alloca(SPI_processed * sizeof(char*));        bidder_mail = alloca(SPI_processed *
sizeof(char*));       bidder_locale = alloca(SPI_processed * sizeof(char*));
 

/*            elog(NOTICE, "starting winner/seller notification on auction #%d",                auction_id);*/
        /* get winner list         */        for (j = SPI_processed - 1; j >= 0; j--) {            bid_price[j] =
*DatumGetFloat64(SPI_getbinval(                   SPI_tuptable->vals[j], SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"bid_price"),                    &isnull));
 
            bid_lot[j] = DatumGetInt32(SPI_getbinval(                    SPI_tuptable->vals[j], SPI_tuptable->tupdesc,
                 SPI_fnumber(SPI_tuptable->tupdesc, "bid_lot"),                    &isnull));
 
            bidder_id[j] = DatumGetInt32(SPI_getbinval(                    SPI_tuptable->vals[j],
SPI_tuptable->tupdesc,                   SPI_fnumber(SPI_tuptable->tupdesc, "bidder_id"),
&isnull));
            bidder_login[j] = SPI_getvalue(                    SPI_tuptable->vals[j], SPI_tuptable->tupdesc,
       SPI_fnumber(SPI_tuptable->tupdesc, "bidder_login"));
 
            bidder_mail[j] = SPI_getvalue(                    SPI_tuptable->vals[j], SPI_tuptable->tupdesc,
      SPI_fnumber(SPI_tuptable->tupdesc, "bidder_mail"));
 
            bidder_locale[j] = SPI_getvalue(                    SPI_tuptable->vals[j], SPI_tuptable->tupdesc,
        SPI_fnumber(SPI_tuptable->tupdesc, "bidder_locale"));
 
            elog(NOTICE, "extracting winner %s for price %f and lot %d",                    bidder_login[j],
bid_price[j],bid_lot[j]);            /* decrease available quantity marker until all is sold, dutch             *
auctionsonly             */
 
/*                l -= bid_lot[i];*/
        }
        if (type == AUCTION_CLASSIC) {            char * winner = NULL;            double final_price;
/*                winner = astrcat();*/            /* determine final_price for dutch auction: the lowest of the
    * winning bids             */            for (j = SPI_processed - 1, l = lot; j >= 0 && l > 0;
j--,l -= bid_lot[j]) {                final_price = bid_price[j];            }            for (j = SPI_processed - 1, l
=lot; j >= 0 && l > 0;                    j--, l -= bid_lot[j]) {
 
                /* start building the string listing winners (for dutch)                 * or the only winner (for
normal)                */                setlocale(LC_ALL, seller_locale);                setenv("LC_ALL",
seller_locale,1);
 
                asprintf(&mess, _(                        "* login: %s, \t"                        "e-mail: %s, \t"
                  "bid price: %.2f, \t"                        "bid quantity: %d, \t"                        "final
price:%.2f,\t"                        "alloted quantity: %d,\t"                        ),
bidder_login[j],bidder_mail[j], bid_price[j],                        bid_lot[j], final_price,
(bid_lot[j]< l ? l : bid_lot[j]) );                astrcat(&winner, mess);                free(mess);
 
                elog(NOTICE, "winner #%d is %s", j, winner);
                setlocale(LC_ALL, bidder_locale[j]);                setenv("LC_ALL", bidder_locale[j], 1);
 /* notify winner directly                 */                asprintf(&mess, _(
 
"\tDear %s,\n"
"\n"
"On the following closed auction:"
"\n"
"- title: %s\n"
"- id: %d\n"
"- end date: %s\n"
"- seller: %s\n"
"- seller e-mail: %s\n"
"\n"
"You have entered this winning bid:\n"
"\n"
"- bid price: %.2f\n"
"- bid quantity: %d\n"
"- final price: %.2f\n"
"- alloted quantity: %d\n"
"\n"
"Please contact the seller as soon as possible to close the transaction\n"
"\n"
"-- \n"
"Apartia auction daemon\n"                ), bidder_login[j], title,                auction_id, stopdate, seller_login,
seller_mail,               bid_price[j], bid_lot[j], final_price,                (bid_lot[j] < l ? l : bid_lot[j]));
           sendmail(bidder_mail[j], "Auction win notification", mess);                free(mess);
 
                /* decrease available quantity marker until all is sold,                 * dutch auctions only
      */                l -= bid_lot[j];            }
 
            /* now notify the seller with a list of winning bids             */            asprintf(&mess, _(
"\tDear %s,\n"
"\n"
"On your closed auction:\n"
"\n"
"- title: %s\n"
"- id: %d\n"
"- end date: %s\n"
"\n"
"The following winning bid(s) have been placed:\n"
"%s\n"
"\n"
"Please contact the winner(s) as soon as possible to close the transaction\n"
"-- \n"
"Apartia auction daemon\n"),            seller_login, title, auction_id, stopdate, winner            );
free(winner);           sendmail(seller_mail,                    _("Auction successful close notification"), mess);
      free(mess);
 

        } else if (type == AUCTION_REVERSE || type == AUCTION_FIXED) {        } else if (type == AUCTION_BID) {
}
        /* clean up memory         */
/*            free(bid_price);        free(bid_lot);        free(bidder_mail);        free(bidder_login);
free(bidder_locale);       free(bidder_id);*/    }
 
    /* DELETE all old bids     */    asprintf(&query, "DELETE FROM bid WHERE auction_id = %d",            auction_id);
 SPI_exec(query, 0);    free(query);
 
    asprintf(&query, "DELETE FROM autobid WHERE auction_id = %d",            auction_id);    SPI_exec(query, 0);
free(query);
    /* renew expired auctions with unsold lots     */    if (auction_status < 0 && renew_count > 0) {
asprintf(&query,"UPDATE auction SET startdate = now(), \            stopdate = now() + (stopdate - startdate), \
   renew_count = renew_count - 1, \            lot = -auction_status(id), notified = FALSE, \            WHERE id =
%d",auction_id);        SPI_exec(query, 0);        free(query);
 
        /* localize message, numbers, dates         */        setlocale(LC_ALL, seller_locale);        setenv("LC_ALL",
seller_locale,1);
 
        /* notify seller of renewal         */        asprintf(&query, _(
"\tDear %s\n"
"\n"
"Your expired auction:\n"
"- title: %s\n"
"- id: %d\n"
"- end date: %s\n"
"\n"
"has been auto-renewed today with the same duration.\n"
"\n"
"Greetings\n"
"-- \n"
"The auction daemon\n"        ), seller_login, title, auction_id, stopdate);        sendmail(seller_mail, _("Auction
renewalnotice"), query);        free(query);    } else {
 
        /* auction was closed and fully sold OR not auto_renewed,         */        asprintf(&query, "DELETE FROM
auctionWHERE id = %d", auction_id);        SPI_exec(query, 0);        free(query);
 
        /* only notify if nothing was sold; when something has been sold         * normal winner/seller notification
hasalready taken place         * higher in this code         */        if (auction_status == -lot) {
setlocale(LC_ALL,seller_locale);            setenv("LC_ALL", seller_locale, 1);            /* notify seller of auction
end            */            asprintf(&query, _(
 
"\tDear %s\n"
"\n"
"Your expired auction:\n"
"- title: %s\n"
"- id: %d\n"
"- end date: %s\n"
"\n"
"Has been removed from the system.\n"
"\n"
"-- \n"
"The auction daemon\n"            ), seller_login, title, auction_id, stopdate);            sendmail(seller_mail,
_("Auctionexpiration notice"), query);            free(query);        }    }
 
/*#endif*/    elog(NOTICE, "End of loop %d", i);}
/* restore default locale */setlocale(LC_ALL, default_locale);setenv("LC_ALL", default_locale, 1);

/*    asprintf(&query, "COMMIT");SPI_exec(query, 0);free(query);*/
SPI_finish();return current;
}

-- 
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

Radioactive cats have 18 half-lives.