Thread: TRIGGERed INSERTS

Howdy;
  I'm writing a "script" to pre-populate a database system that's
already in place.  The database system is in an advanced stage of
development and includes many stored procedures and TRIGGERS.
  The "script" is really a stored procedure designed to be executed by
the system's admin guy as part of "user system start-up."  Within the
script I'm calling stored procedures to create "items" (which in my
system are "data" table records with corresponding records in an
ordering/pointer table).
  Some of the data tables have "AFTER INSERT" TRIGGERs on them that, in
turn, insert some subordinate items into parallel data tables and the
central pointer/ordering table.
  As part of the pre-population "script" I call "add_item" and then
want to UPDATE a record in a different data table created by one of the
TRIGGERS (i.e. a "sub-record" if you will).  When I use a SELECT INTO
within the "script" to discover the pointer record key for this new
sub-record (so I can go UPDATE it), the SELECT INTO comes up with NULL.
After my "script" is done, however, a SELECT from the psql command line
discovers the pointer record's key value with no problem!
  Am I not waiting long enough within my "script" for the engine to
settle out?  Are the results of that transaction not available to me
until I roll out of the transaction initiated by my "script" procedure?I'm somewhat baffled as to why the SELECT INTO
iscoming up NOT FOUND
 
within the scope of my "script" procedure.  I did a search of the
mailing lists on "TRIGGERS" but didn't turn up any clues.
  Thanks for your help!
  Martin

"Martin Crundall" <pgsql@ac6rm.net> writes:
>    Some of the data tables have "AFTER INSERT" TRIGGERs on them that, in
> turn, insert some subordinate items into parallel data tables and the
> central pointer/ordering table.

It looks to me like AFTER triggers are fired upon return to the main
loop in postgres.c, thus only at the end of a querystring sent by the
client.  This is perhaps wrong, but I'm not sure that allowing them to
fire during plpgsql functions would be a good thing either.
        regards, tom lane

Thanks Tom.  The work-around was to create the "script" in SQL -- using
SELECT INTO statements to capture the key values -- instead of creating
the "script" as a stored procedure.

Seems like a "transaction" issue.  Having transactions occur at the SELECT
level is very intuitive and a really nice, "protect-us-from-ourselves"
feature that I'm not sure I'd want to mess with.  Having the ability to
execute a stored procedure outside the scope of a SELECT would ultimately
resolve this.  A topic for another day ... unless I missed something in an
update somewhere.

Anyway, thanks again.

> "Martin Crundall" <pgsql@ac6rm.net> writes:
>>    Some of the data tables have "AFTER INSERT" TRIGGERs on them that,
>> in
>> turn, insert some subordinate items into parallel data tables and the
>> central pointer/ordering table.
>
> It looks to me like AFTER triggers are fired upon return to the main
> loop in postgres.c, thus only at the end of a querystring sent by the
> client.  This is perhaps wrong, but I'm not sure that allowing them to
> fire during plpgsql functions would be a good thing either.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html