Order of Update - Second Try - Mailing list pgsql-general

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