Message-ID: <63503.24.52.245.104.1034960350.squirrel@webmail.ac6rm.net>
Date: Fri, 18 Oct 2002 09:59:10 -0700 (PDT)
Subject: Re: TRIGGERed INSERTS
From: "Martin Crundall" <pgsql@ac6rm.net>
To: <tgl@sss.pgh.pa.us>
In-Reply-To: <17482.1034950973@sss.pgh.pa.us>
References: <63387.24.52.245.104.1034949598.squirrel@webmail.ac6rm.net>
	<17482.1034950973@sss.pgh.pa.us>
Importance: Normal
Cc: <pgsql-sql@postgresql.org>
Reply-To: pgsql@ac6rm.net
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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