function crashes backend

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, person seller WHERE auction_status(a.id) <= 0 \
AND a.person_id = seller.id \
AND (notified IS FALSE OR notified IS NULL) 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) AS bid_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 \
GROUP BY 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
* auctions only
*/
/* 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 renewal notice"), query);
free(query);
} else {

/* auction was closed and fully sold OR not auto_renewed,
*/
asprintf(&query, "DELETE FROM auction WHERE 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 has already 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, _("Auction expiration 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(at)apartia(dot)org - http://www.apartia.org

Radioactive cats have 18 half-lives.