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.
