pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTRIGGERed INSERTS
4+ messages / 3 participants
[nested] [flat]
* TRIGGERed INSERTS
@ 2002-10-18 13:57 Martin Crundall <martin@ac6rm.net>
0 siblings, 0 replies; 4+ messages in thread
From: Martin Crundall @ 2002-10-18 13:57 UTC (permalink / raw)
To: pgsql-sql
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 is coming 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* TRIGGERed INSERTS
@ 2002-10-18 13:59 Martin Crundall <pgsql@ac6rm.net>
2002-10-18 14:22 ` Re: TRIGGERed INSERTS Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Martin Crundall @ 2002-10-18 13:59 UTC (permalink / raw)
To: pgsql-sql
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 is coming 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TRIGGERed INSERTS
2002-10-18 13:59 TRIGGERed INSERTS Martin Crundall <pgsql@ac6rm.net>
@ 2002-10-18 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-18 16:59 ` Re: TRIGGERed INSERTS Martin Crundall <pgsql@ac6rm.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2002-10-18 14:22 UTC (permalink / raw)
To: pgsql@ac6rm.net; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TRIGGERed INSERTS
2002-10-18 13:59 TRIGGERed INSERTS Martin Crundall <pgsql@ac6rm.net>
2002-10-18 14:22 ` Re: TRIGGERed INSERTS Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-10-18 16:59 ` Martin Crundall <pgsql@ac6rm.net>
0 siblings, 0 replies; 4+ messages in thread
From: Martin Crundall @ 2002-10-18 16:59 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-18 16:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-18 13:57 TRIGGERed INSERTS Martin Crundall <martin@ac6rm.net>
2002-10-18 13:59 TRIGGERed INSERTS Martin Crundall <pgsql@ac6rm.net>
2002-10-18 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-18 16:59 ` Martin Crundall <pgsql@ac6rm.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox