query visibility - trigger order - bug? - Mailing list pgsql-general

Dear Gurus,

I know that order of triggers on the same event are undetermined in psql7.2,
but this case is not about that (I think). Also, the mail may seem to be
long, but it is probably enough to read the abstract. Please read on.

I don't know if it's a bug in 7.2.1 or it's a concept I should know about.

ABSTRACT:
~~~~~~~~

I use PostgreSQL 7.2.1
I have several tables with several triggers and it seems that certain
queries 3 deep in triggering don't run in the order that I would assume. At
the end of this mail is an (edited, very short) excerpt of postgresql's log,
to which I request explanation. The entire thing is related to 3 BEFORE
triggers (on different tables), and is solved by split one of them into
BEFORE and AFTER triggers, though I don't understand why.

Please have a look at the log excerpt and explain. In the first round, all
you have to know is that pack_item references to pack, and insert/update on
pack triggers the same event on pack_item (at least, in our case). It can be
seen clearly that INSERT on pack_item is run later than UPDATE on pack, even
though it sounds illogical to me.

If you have patience, read the details, in hope that it helps understand my
problem.

DETAILS:
~~~~~~~

DATA MODEL
Imagine tables referencing to others as follows.
* There is a document called PARCEL.
* Parcel has Parcel_items (referencing to parcels) which I don't wish to
discuss now in details.
* Each parcel may have one or more PACKs (referencing to parcels)
* Each pack has PACK_ITEMs (referencing to packs), created from parcel_items
or other pack_items (in which case pack also references another pack).

TRIGGERS
In the following, I will describe some PL/PGSQL triggers. I describe them in
the order I assume they trigger one another, and, as the document says, in
the order I assume queries to be run:

In postgresdoc-7.2/html/trigger-datachanges.html:
"Changes made by query Q are visible by queries that are started after query
Q, no matter whether they are started inside Q (during the execution of Q)
or after Q is done."

-- I have to cancel a parcel. From client or backend I say:
UPDATE parcel SET state='cancelled' WHERE id=1000;
  -- In parcel_biud (BEFORE INSERT OR UPDATE OR DELETE ON parcel), UPDATE:
  -- Creating negative packs, one for each positive pack
  INSERT INTO pack (cancelled_pack)
  SELECT id FROM pack WHERE parcel=new.id;
    -- In pack_biud, INSERT:
    -- generating new negative pack from cancelled pack
    INSERT INTO pack_item (...)
    SELECT (...) FROM pack_item WHERE pack=new.cancelled_pack;
      -- In pack_item_biud, INSERT:
      RAISE DEBUG '...';
  -- back to parcel_biud:
  -- closing a pack effectively manipulates stored goods etc.
  UPDATE pack SET closed='t'
  WHERE parcel=new.id AND cancelled_pack NOTNULL;
    -- In pack_biud, UPDATE:
    -- Just a denormalization
    UPDATE pack_item SET closed='t'
    WHERE pack=new.id;
      -- In pack_item_biud, UPDATE:
      RAISE DEBUG '...';


EXPERIENCES
1. If I send the "INSERT INTO pack..." and "UPDATE pack..." queries (as
described in trigger function parcel_biud) in separate transactions,
everything goes OK.

2. If I send a simple update to parcel, triggering the two queries in the
same transaction (effectively, in the same run of the trigger):
in pack_biud, UPDATE: the query "UPDATE pack_item..." does nothing.
Debugging reveals that the WHERE clause returns 0 tuples.

3. The log seems to tell me that all the INSERTs and UPDATEs on table pack
are run (and trigger pack_biud) before any INSERTs on table pack_item (that
triggers pack_item_biud later).

4. Further examinations revealed that it is different from the way
parcel_biud sends INSERT and UPDATE to table pack, since the latter's WHERE
clause does find the tuples created by the former.

5. Putting UPDATE handling on table pack from BEFORE to AFTER trigger solved
the problem. In pack_au (UPDATE), the where clause of query "UPDATE
pack_item..." already sees the tuples inserted by an earlier run of
pack_biud(INSERT). That is OK, except that I don't understand why didn't it
work the original way.

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
DEBUG:  parcel_biud, UPDATE: cancelling parcel xxxx
DEBUG:  pack_biud, UPDATE: closing pack yyyyy, 0 item(s)
DEBUG:  pack_item_biud, INSERT: pack: yyyyy, ...
---------------------------- cut here ------------------------------