Обсуждение: 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: terry@esc1.com

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

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.

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: terry@esc1.com

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: terry@esc1.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

> 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).

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 majordomo@postgresql.org 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: terry@esc1.com