From: Terry Lee Tucker <terry@esc1.com>
To: pgsql-general@postgresql.org
Subject: Order of Update
Date: Sat, 18 Mar 2006 09:16:28 -0500
Message-ID: <200603180916.28095.terry@esc1.com> (raw)
Hello List:
I'm having an "interesting" problem. I know what is happening, but I do not
know why; therefore, I am hoping that some of those very familiar with the
internals can/will explain it to me.
I have a function A written in plpgsql which is designed to undo changes to
several records. This function is called from an X Windows interface when the
user presses the "undo" button. The functions updates one or records in a
table within loop and then updates a "parent" record from a different table.
The updates in the loop depend on certain values being present in the
"parent" record. At the end of the loop, an update to the "parent" record
sets to NULL the values that were used in the loop updates. The problem is
that the update to the parent occurs first even though the code performing
the update is physically located below the loop. Here is the function:
CREATE OR REPLACE FUNCTION removeCash (INTEGER) RETURNS VOID AS '
DECLARE
cash_recid ALIAS FOR $1; -- recid of receipt record
cashRec RECORD; -- cash record buffer
itemRec RECORD; -- item record buffer
itemTypes TEXT DEFAULT ''(w|j|d|o|c|b)''; -- possible item types
funcName TEXT DEFAULT ''removeCash''; -- function name
dbg BOOLEAN DEFAULT True; -- debug print flag
BEGIN
IF dbg THEN
RAISE NOTICE ''% ()'', funcName;
END IF;
SELECT INTO cashRec order_num, ref FROM cash WHERE recid = cash_recid;
IF NOT FOUND THEN
RAISE EXCEPTION
''%: Cannot find a cash record referenced by %.'', funcName,
cash_recid;
END IF;
/* Loop through the item records linked to this cash record. */
FOR itemRec IN SELECT recid, item_type FROM item
WHERE order_num = cashRec.order_num
AND ref = cashRec.ref
AND item_type ~* itemTypes ORDER BY ref
LOOP
/* For type C, simply set the apply amount to zero and link to the
* value of olink. */
IF itemRec.item_type ~* ''c'' THEN
UPDATE item SET apply_amt = 0 WHERE recid = itemRec.recid;
IF NOT FOUND THEN
RAISE EXCEPTION
''%: Cannot update apply_amt for item %.'', funcName,
itemRec.recid;
END IF;
ELSE
/* Delete all other item types. */
DELETE item WHERE recid = itemRec.recid;
IF NOT FOUND THEN
RAISE EXCEPTION
''%: Cannot delete item record %.'', funcName,
itemRec.recid;
END IF;
END IF;
END LOOP;
/* Now, update the cash record setting batch and chkno to null. */
UPDATE cash SET chkno = NULL, batch = NULL
WHERE recid = cash_recid;
IF NOT FOUND THEN
RAISE EXCEPTION
''%: Unable to update cash record %.'', funcName, cash_recid;
END IF;
RETURN;
END;
' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
There are trigger updates involved in this process. Setting apply_amt to zero
on the line item record causes an update to be made to the cash record linked
to the item record subtracting the value of apply_amt from a total applied
column in the cash record. Is this why the cash record is updated first?
Also, cash.batch and cash.chkno are the two components of a UNIQUE index.
Does this have something to do with it?
Any insight anyone can give would be greatly appreciated.
--
Quote: 81
"Government does not solve problems; it subsidizes them."
--Ronald Reagan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: terry@esc1.com
Subject: Re: Order of Update
In-Reply-To: <200603180916.28095.terry@esc1.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox