Re: [GENERAL] Order of Update - Second Try
Terry Lee Tucker wrote: Hello List: I posted a question over the weekend regarding this issue but I failed to communicate effectively what the problem is. I was weary :o[ Let me try again. I have a plpgsql function which makes updates to a set of records across two tables in a One to Many relationship hereinafter referred to as parent and child. The parent record contains two important sets of information that is relative to this process. The first is data that links the children to the parent. The second is data that links the parent to the to a third table that we will call totals. The update to the totals table is accomplished by the execution of a trigger that is fired as a result of updating the child records. The function updates the child records in a loop and then, at the bottom of the function, the parent table is updated, setting to null the values that link it to the totals table. The problem is that the update to the parent table is occurring first; then, the updates to the child records are occurring. I'm curious about this issue. How is this possible? I caught the previous posting, but didn't see any responses. Is there anyway to deal with this issue. I know I've got code that takes a similar approach and I'm now concerned that I'm going to trash my database. Anyone? -- Until later, Geoffrey ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Order of Update - Second Try
On Tue, Mar 21, 2006 at 06:56:23AM -0500, Terry Lee Tucker wrote: Hello List: I posted a question over the weekend regarding this issue but I failed to communicate effectively what the problem is. I was weary :o[ Let me try again. I think one of the reasons why you're not getting any responses is that the problem is complicated but you have not provided a complete example. You don't for example say how the trigger is defined (BEFORE|AFTER) (ROW|STATEMENT) DEFERRED yes/no? If you really want an answer you're going to need to provide a complete example people can run on their own systems. have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[GENERAL] Order of Update - Second Try
Hello List: I posted a question over the weekend regarding this issue but I failed to communicate effectively what the problem is. I was weary :o[ Let me try again. I have a plpgsql function which makes updates to a set of records across two tables in a One to Many relationship hereinafter referred to as parent and child. The parent record contains two important sets of information that is relative to this process. The first is data that links the children to the parent. The second is data that links the parent to the to a third table that we will call totals. The update to the totals table is accomplished by the execution of a trigger that is fired as a result of updating the child records. The function updates the child records in a loop and then, at the bottom of the function, the parent table is updated, setting to null the values that link it to the totals table. The problem is that the update to the parent table is occurring first; then, the updates to the child records are occurring. This is causing the totals table NOT to be updated due to the fact that the information needed by the trigger to find that parent has already been removed. I have proved that the above is happening by placing various RAISE NOTICE messages in triggers on the tables involved. I can work around this by splitting the update into two transactions but I would like to understand why this is happening. I would like to recognize what circumstances might cause this to happen. In all my work with PostgreSQL, so far, I haven't seen this kind of behavior. I have tried to represent the table relationship below. Also, I point out that batch and chkno are the two elements of a UNIQUE index on the parent table. Is this the reason for the behavior? parent -- order_num, batch, chkno batch and chkno are set to null at the bottom of the function. child --- order_num, apply amount apply amount is what is updated on the child. Totals Table - batch, chkno, [...], [...] rnd=# select version(); version -- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) TIA -- Quote: 65 A vote is like a rifle: its usefulness depends upon the character of the user. --Theodore Roosevelt Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Order of Update - Second Try
Thanks for the response. I'm working on a simplified example now. It will take a little time to set it up. I will post all the code for creating the much simplified tables and trigger. Again, thanks for the response... On Tuesday 21 March 2006 08:15 am, Martijn van Oosterhout saith: On Tue, Mar 21, 2006 at 06:56:23AM -0500, Terry Lee Tucker wrote: Hello List: I posted a question over the weekend regarding this issue but I failed to communicate effectively what the problem is. I was weary :o[ Let me try again. I think one of the reasons why you're not getting any responses is that the problem is complicated but you have not provided a complete example. You don't for example say how the trigger is defined (BEFORE|AFTER) (ROW|STATEMENT) DEFERRED yes/no? If you really want an answer you're going to need to provide a complete example people can run on their own systems. have a nice day, -- Quote: 39 Posterity -- you will never know how much it has cost my generation to preserve your freedom. I hope you will make good use of it. --John Quincy Adams Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Order of Update - Second Try
Ok, find attached a script called test.sql that will create three tables called parent, child, and totals. It will create a simple AFTER UPDATE trigger on child and a BEFORE trigger on parent simply to show that the values of batch and chkno are set to NULL right in the beginning. Just load the thing in with the \i command. There is a function created called myfunc(int). Simply do: SELECT myfunc(99); to see what happens. After you execute the function, you will find that parent.total is zero, child.apply_amt for each record is zero, but totals is still set to 1500. It should be 1000. Version info: rnd=# select version(); version -- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) Any insight is appreciated. TIA On Tuesday 21 March 2006 09:11 am, Terry Lee Tucker saith: Thanks for the response. I'm working on a simplified example now. It will take a little time to set it up. I will post all the code for creating the much simplified tables and trigger. Again, thanks for the response... On Tuesday 21 March 2006 08:15 am, Martijn van Oosterhout saith: On Tue, Mar 21, 2006 at 06:56:23AM -0500, Terry Lee Tucker wrote: Hello List: I posted a question over the weekend regarding this issue but I failed to communicate effectively what the problem is. I was weary :o[ Let me try again. I think one of the reasons why you're not getting any responses is that the problem is complicated but you have not provided a complete example. You don't for example say how the trigger is defined (BEFORE|AFTER) (ROW|STATEMENT) DEFERRED yes/no? If you really want an answer you're going to need to provide a complete example people can run on their own systems. have a nice day, -- Quote: 39 Posterity -- you will never know how much it has cost my generation to preserve your freedom. I hope you will make good use of it. --John Quincy Adams Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend CREATE TABLE parent ( order_num INT, batch INT, chkno VARCHAR, total INT ) WITHOUT OIDS; CREATE INDEX batch_chkno ON parent (batch, chkno); CREATE TABLE child ( recid INT, order_num INT, apply_amt INT ) WITHOUT OIDS; CREATE TABLE totals ( batch INT, chkno VARCHAR, amount INT, UNIQUE (batch, chkno) ) WITHOUT OIDS; CREATE OR REPLACE FUNCTION parent_func () RETURNS TRIGGER AS ' DECLARE BEGIN IF TG_OP = ''UPDATE'' THEN RAISE NOTICE ''%: batch: % chkno: %'', TG_NAME, new.batch, new.chkno; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER parent_preupd BEFORE INSERT OR UPDATE ON parent FOR EACH ROW EXECUTE PROCEDURE parent_func (); CREATE OR REPLACE FUNCTION child_func () RETURNS TRIGGER AS ' DECLARE parentRec RECORD; -- parent record buffer BEGIN IF TG_OP = ''UPDATE'' THEN /* find the parent record. */ SELECT INTO parentRec batch, chkno FROM parent WHERE order_num = new.order_num; RAISE NOTICE ''%: parentRec.batch: % parentRec.chkno: %'', TG_NAME, parentRec.batch, parentRec.chkno; /* update the parent */ UPDATE parent SET total = total - old.apply_amt WHERE order_num = new.order_num; /* update totals */ UPDATE totals SET amount = amount - old.apply_amt WHERE batch = parentRec.batch AND chkno = parentRec.chkno; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER child_write AFTER INSERT OR UPDATE ON child FOR EACH ROW EXECUTE PROCEDURE child_func (); CREATE OR REPLACE FUNCTION myfunc (integer) RETURNS void AS ' DECLARE tripALIAS FOR $1; -- order_num childRecRECORD; BEGIN FOR childRec IN SELECT * FROM child WHERE order_num = trip LOOP UPDATE child SET apply_amt = 0 WHERE recid = childRec.recid; END LOOP; UPDATE parent SET batch = NULL, chkno = NULL WHERE order_num = trip; RETURN; END; ' LANGUAGE plpgsql; /* Insert data. */ INSERT INTO totals (batch, chkno, amount) VALUES (100, 'BR-549', 1500); INSERT INTO parent (order_num, batch, chkno, total) VALUES (99, 100, 'BR-549', 500); INSERT INTO child (recid, order_num, apply_amt) VALUES (1, 99, 100); INSERT INTO child (recid, order_num, apply_amt) VALUES (2, 99, 100); INSERT INTO child (recid, order_num, apply_amt) VALUES (3, 99, 100); INSERT INTO child (recid, order_num, apply_amt) VALUES (4, 99, 100); INSERT INTO child (recid, order_num,
Re: [GENERAL] Order of Update - Second Try
Ok, find attached a script called test.sql that will create three tables called parent, child, and totals. It will create a simple AFTER UPDATE trigger on child and a BEFORE trigger on parent simply to show that the values of batch and chkno are set to NULL right in the beginning. Just load the thing in with the \i command. There is a function created called myfunc(int). Simply do: SELECT myfunc(99); to see what happens. After you execute the function, you will find that parent.total is zero, child.apply_amt for each record is zero, but totals is still set to 1500. It should be 1000. Version info: rnd=# select version(); version -- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) Any insight is appreciated. I think in 8.0 or later it'd do what you want, however IIRC in 7.4 the after triggers are delayed until after the full execution of the function myfunc (ie, at the end of the outer statement). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Order of Update - Second Try
Stephan, Thanks for the reply. We will be upgrading to version 8.x in the third quarter of the year. As I said, I have a workaround. I just wanted to know why it was behaving that way and you provided that. Thanks for the help. On Tuesday 21 March 2006 11:36 am, Stephan Szabo saith: Ok, find attached a script called test.sql that will create three tables called parent, child, and totals. It will create a simple AFTER UPDATE trigger on child and a BEFORE trigger on parent simply to show that the values of batch and chkno are set to NULL right in the beginning. Just load the thing in with the \i command. There is a function created called myfunc(int). Simply do: SELECT myfunc(99); to see what happens. After you execute the function, you will find that parent.total is zero, child.apply_amt for each record is zero, but totals is still set to 1500. It should be 1000. Version info: rnd=# select version(); version - - PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) Any insight is appreciated. I think in 8.0 or later it'd do what you want, however IIRC in 7.4 the after triggers are delayed until after the full execution of the function myfunc (ie, at the end of the outer statement). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Quote: 71 When the Lord calls me home, whenever that may be, I will leave with the greatest love for this country of ours and eternal optimism for its future. I now begin the journey that will lead me into the sunset of my life. I know that for America there will always be a bright dawn ahead. --Ronald Reagan Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
