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@apartia.org - http://www.apartia.org

Radioactive cats have 18 half-lives.

Wed, 27 Sep 2000 08:53:58 +0200
Louis-David Mitterrand <cunctator@apartia.ch>
function crashes backend
On Wed, Sep 27, 2000 at 08:53:58AM +0200, Louis-David Mitterrand wrote:
> 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.

Following up to myself, I finally understood my problem: I was trying to
re-use SPI_tuptable->vals[i] after calling SPI_exec() on another,
unrelated query. So the backend crash makes perfect sense now.

What is the best strategy: 
- store the result of a SELECT returning multiple tuples into a local
  SPITupleTable? How do I allocate memory for that?
- iterate over all values contained in the tuples and store _them_ into
  char**, int*, arrays, before re-running SPI_exec() on the second query?

TIA

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

> Any suggestions for setting up WinCVS client + (server) on NT4?
Run away screaming in terror.
					--Toby.

Wed, 27 Sep 2000 10:13:15 +0200
Louis-David Mitterrand <cunctator@apartia.ch>
Re: function crashes backend