pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger question
15+ messages / 9 participants
[nested] [flat]
* Trigger question
@ 2004-01-15 13:13 pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-16 10:19 ` Re: Trigger question Chris Travers <chris@travelamericas.com>
0 siblings, 2 replies; 15+ messages in thread
From: pginfo @ 2004-01-15 13:13 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am using pg 7.4.1 and have created a trigger over table with 3 M rows.
If I start masive update on this table, pg executes this trigger on
every row and dramaticaly slows the system.
Exists in pg any way to define the trigger execution only if I have
changes on some fields?
For example I am able to declare this in oracle.
My trigger is writen in pgSQL.
regards,
ivan.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
@ 2004-01-15 16:10 ` Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
1 sibling, 1 reply; 15+ messages in thread
From: Richard Huxton @ 2004-01-15 16:10 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; pgsql-performance
On Thursday 15 January 2004 13:13, pginfo wrote:
> Hi,
>
> I am using pg 7.4.1 and have created a trigger over table with 3 M rows.
> If I start masive update on this table, pg executes this trigger on
> every row and dramaticaly slows the system.
> Exists in pg any way to define the trigger execution only if I have
> changes on some fields?
Not at the moment (and I don't know of any plans for it).
> For example I am able to declare this in oracle.
> My trigger is writen in pgSQL.
Hmm - I can only think of two things you can try:
1. check for the change first thing you do and exit if not there
2. do the same, but write the trigger function in 'C'
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
@ 2004-01-15 17:08 ` Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
0 siblings, 1 reply; 15+ messages in thread
From: Evil Azrael @ 2004-01-15 17:08 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
Guten Tag Richard Huxton,
Am Donnerstag, 15. Januar 2004 um 17:10 schrieben Sie:
RH> On Thursday 15 January 2004 13:13, pginfo wrote:
>> Hi,
>>
>> I am using pg 7.4.1 and have created a trigger over table with 3 M rows.
>> If I start masive update on this table, pg executes this trigger on
>> every row and dramaticaly slows the system.
>> Exists in pg any way to define the trigger execution only if I have
>> changes on some fields?
That not, but perhaps a STATEMENT Level Trigger could help. It´s a new
feature in 7.4.x and rather undocumented. http://www.postgresql.org/docs/current/interactive/sql-createtrigger.html
"FOR EACH STATEMENT" probably this can help you a bit.
Christoph Nelles
--
Mit freundlichen Grüssen
Evil Azrael mailto:evilazrael@evilazrael.de
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
@ 2004-01-16 13:05 ` Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
0 siblings, 1 reply; 15+ messages in thread
From: Harald Fuchs @ 2004-01-16 13:05 UTC (permalink / raw)
To: pgsql-performance
In article <1022542776.20040115180811@evilazrael.de>,
Evil Azrael <evilazrael@evilazrael.de> writes:
> That not, but perhaps a STATEMENT Level Trigger could help. It´s a new
> feature in 7.4.x and rather undocumented. http://www.postgresql.org/docs/current/interactive/sql-createtrigger.html
> "FOR EACH STATEMENT" probably this can help you a bit.
Does anyone know how to access the affected values for statement-level
triggers? I mean what the "old" and "new" pseudo-records are for
row-level triggers.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
@ 2004-01-20 00:01 ` Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Re: Trigger question Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 15+ messages in thread
From: Neil Conway @ 2004-01-20 00:01 UTC (permalink / raw)
To: Harald Fuchs <hf99@protecting.net>; +Cc: pgsql-performance
Harald Fuchs <hf99@protecting.net> writes:
> Does anyone know how to access the affected values for
> statement-level triggers? I mean what the "old" and "new"
> pseudo-records are for row-level triggers.
Yeah, I didn't get around to implementing that. If anyone wants this
feature, I'd encourage them to step up to the plate -- I'm not sure
when I'll get the opportunity/motivation to implement this myself.
-Neil
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
@ 2004-01-20 08:13 ` Richard Huxton <dev@archonet.com>
2004-01-20 15:17 ` Re: Trigger question Neil Conway <neilc@samurai.com>
2004-01-20 16:02 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 15+ messages in thread
From: Richard Huxton @ 2004-01-20 08:13 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; Harald Fuchs <hf99@protecting.net>; +Cc: pgsql-performance
On Tuesday 20 January 2004 00:01, Neil Conway wrote:
> Harald Fuchs <hf99@protecting.net> writes:
> > Does anyone know how to access the affected values for
> > statement-level triggers? I mean what the "old" and "new"
> > pseudo-records are for row-level triggers.
>
> Yeah, I didn't get around to implementing that. If anyone wants this
> feature, I'd encourage them to step up to the plate -- I'm not sure
> when I'll get the opportunity/motivation to implement this myself.
I didn't think they'd be meaningful for a statement-level trigger. Surely
OLD/NEW are by definition row-level details.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Re: Trigger question Richard Huxton <dev@archonet.com>
@ 2004-01-20 15:17 ` Neil Conway <neilc@samurai.com>
1 sibling, 0 replies; 15+ messages in thread
From: Neil Conway @ 2004-01-20 15:17 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Harald Fuchs <hf99@protecting.net>; pgsql-performance
Richard Huxton <dev@archonet.com> writes:
> I didn't think they'd be meaningful for a statement-level
> trigger. Surely OLD/NEW are by definition row-level details.
Granted; the feature in question is *some* means of accessing the
result set of a statement-level trigger -- it probably would not use
the same UI (i.e. OLD/NEW) that row-level triggers use.
-Neil
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Re: Trigger question Richard Huxton <dev@archonet.com>
@ 2004-01-20 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 16:15 ` Re: Trigger question Harald Fuchs <hf118@protecting.net>
1 sibling, 1 reply; 15+ messages in thread
From: Tom Lane @ 2004-01-20 16:02 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Neil Conway <neilc@samurai.com>; Harald Fuchs <hf99@protecting.net>; pgsql-performance
Richard Huxton <dev@archonet.com> writes:
> On Tuesday 20 January 2004 00:01, Neil Conway wrote:
>> Yeah, I didn't get around to implementing that. If anyone wants this
>> feature, I'd encourage them to step up to the plate -- I'm not sure
>> when I'll get the opportunity/motivation to implement this myself.
> I didn't think they'd be meaningful for a statement-level trigger. Surely
> OLD/NEW are by definition row-level details.
According to the complainants, OLD/NEW are commonly available as
recordsets (tables) inside a statement trigger. I'm not very clear on
how that works myself --- in particular, one would think it important to
be able to work with corresponding pairs of OLD and NEW rows, which
would be painful with a table-like abstraction. Can anyone explain
exactly how it's done in, say, Oracle?
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-20 16:02 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-20 16:15 ` Harald Fuchs <hf118@protecting.net>
2004-01-20 16:30 ` Re: Trigger question Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-20 16:42 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 15+ messages in thread
From: Harald Fuchs @ 2004-01-20 16:15 UTC (permalink / raw)
To: pgsql-performance
In article <24300.1074614549@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Richard Huxton <dev@archonet.com> writes:
>> On Tuesday 20 January 2004 00:01, Neil Conway wrote:
>>> Yeah, I didn't get around to implementing that. If anyone wants this
>>> feature, I'd encourage them to step up to the plate -- I'm not sure
>>> when I'll get the opportunity/motivation to implement this myself.
>> I didn't think they'd be meaningful for a statement-level trigger. Surely
>> OLD/NEW are by definition row-level details.
> According to the complainants, OLD/NEW are commonly available as
> recordsets (tables) inside a statement trigger.
Yes.
> I'm not very clear on
> how that works myself --- in particular, one would think it important to
> be able to work with corresponding pairs of OLD and NEW rows, which
> would be painful with a table-like abstraction.
Why? If the underlying table has a primary key, finding corresponding
pairs is trivial; if there isn't, it's impossible.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-20 16:02 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 16:15 ` Re: Trigger question Harald Fuchs <hf118@protecting.net>
@ 2004-01-20 16:30 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2004-01-20 16:30 UTC (permalink / raw)
To: Harald Fuchs <hf118@protecting.net>; +Cc: pgsql-performance
On Tue, 20 Jan 2004, Harald Fuchs wrote:
> In article <24300.1074614549@sss.pgh.pa.us>,
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> > Richard Huxton <dev@archonet.com> writes:
> >> On Tuesday 20 January 2004 00:01, Neil Conway wrote:
> >>> Yeah, I didn't get around to implementing that. If anyone wants this
> >>> feature, I'd encourage them to step up to the plate -- I'm not sure
> >>> when I'll get the opportunity/motivation to implement this myself.
>
> >> I didn't think they'd be meaningful for a statement-level trigger. Surely
> >> OLD/NEW are by definition row-level details.
>
> > According to the complainants, OLD/NEW are commonly available as
> > recordsets (tables) inside a statement trigger.
>
> Yes.
>
> > I'm not very clear on
> > how that works myself --- in particular, one would think it important to
> > be able to work with corresponding pairs of OLD and NEW rows, which
> > would be painful with a table-like abstraction.
>
> Why? If the underlying table has a primary key, finding corresponding
> pairs is trivial; if there isn't, it's impossible.
I don't think that's sufficient unless you can guarantee that the primary
key values never change for any reason that causes the trigger to try to
correspond them.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-20 16:02 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 16:15 ` Re: Trigger question Harald Fuchs <hf118@protecting.net>
@ 2004-01-20 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 19:05 ` Re: Trigger question Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 15+ messages in thread
From: Tom Lane @ 2004-01-20 16:42 UTC (permalink / raw)
To: Harald Fuchs <hf118@protecting.net>; +Cc: pgsql-performance
Harald Fuchs <hf118@protecting.net> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> I'm not very clear on
>> how that works myself --- in particular, one would think it important to
>> be able to work with corresponding pairs of OLD and NEW rows, which
>> would be painful with a table-like abstraction.
> Why? If the underlying table has a primary key, finding corresponding
> pairs is trivial; if there isn't, it's impossible.
Exactly. Nonetheless, the correspondence exists --- the UPDATE
definitely updated some particular row of the OLD set into some
particular one of the NEW set. If the trigger API makes it impossible
to reconstruct the matchup, the API is broken.
Even if there is a primary key, the API should not force you to rely
on that; what of an UPDATE that changes the primary key?
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-20 16:02 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 16:15 ` Re: Trigger question Harald Fuchs <hf118@protecting.net>
2004-01-20 16:42 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-20 19:05 ` Richard Huxton <dev@archonet.com>
2004-01-21 13:50 ` Re: Trigger question Harald Fuchs <hf118@protecting.net>
0 siblings, 1 reply; 15+ messages in thread
From: Richard Huxton @ 2004-01-20 19:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Harald Fuchs <hf118@protecting.net>; +Cc: pgsql-performance
On Tuesday 20 January 2004 16:42, Tom Lane wrote:
> Harald Fuchs <hf118@protecting.net> writes:
> > Why? If the underlying table has a primary key, finding corresponding
> > pairs is trivial; if there isn't, it's impossible.
>
> Exactly. Nonetheless, the correspondence exists --- the UPDATE
> definitely updated some particular row of the OLD set into some
> particular one of the NEW set. If the trigger API makes it impossible
> to reconstruct the matchup, the API is broken.
Perhaps they should be cursors? The only sensible way I can think of working
with them would be:
1. count how many rows affected
2. step through one row at a time, doing something.
I suppose there might be cases where you'd want to GROUP BY... which would
mean you'd need some oid/row-id added to a "real" recordset.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-20 16:02 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 16:15 ` Re: Trigger question Harald Fuchs <hf118@protecting.net>
2004-01-20 16:42 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 19:05 ` Re: Trigger question Richard Huxton <dev@archonet.com>
@ 2004-01-21 13:50 ` Harald Fuchs <hf118@protecting.net>
2004-01-21 16:45 ` Re: Trigger question Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 15+ messages in thread
From: Harald Fuchs @ 2004-01-21 13:50 UTC (permalink / raw)
To: pgsql-performance
In article <200401201905.46699.dev@archonet.com>,
Richard Huxton <dev@archonet.com> writes:
> On Tuesday 20 January 2004 16:42, Tom Lane wrote:
>> Harald Fuchs <hf118@protecting.net> writes:
>> > Why? If the underlying table has a primary key, finding corresponding
>> > pairs is trivial; if there isn't, it's impossible.
>>
>> Exactly. Nonetheless, the correspondence exists --- the UPDATE
>> definitely updated some particular row of the OLD set into some
>> particular one of the NEW set. If the trigger API makes it impossible
>> to reconstruct the matchup, the API is broken.
I would not say so. You could use tables without primary keys, and
you could define statement-level triggers on them, but you could not
identify a particular row in this very special and probably rare case.
> Perhaps they should be cursors? The only sensible way I can think of working
> with them would be:
> 1. count how many rows affected
> 2. step through one row at a time, doing something.
When I read about the "insert" and "delete" pseudotables in a book
about Transact-SQL, i was enthusiastic about the elegance of this
idea: you're operating on multiple (perhaps lots of) rows, and the SQL
way of doing that is by set-operations, i.e. single operations
affecting a set of rows. Pseudotables extend this idea nicely into
the area of statement-level triggers. Your cursor idea doesn't look
very SQL-like to me.
We really should find an Oracle/DB2/Informix guy who can tell us how
to get that right.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Re: Trigger question Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Re: Trigger question Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Re: Trigger question Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-20 16:02 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 16:15 ` Re: Trigger question Harald Fuchs <hf118@protecting.net>
2004-01-20 16:42 ` Re: Trigger question Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 19:05 ` Re: Trigger question Richard Huxton <dev@archonet.com>
2004-01-21 13:50 ` Re: Trigger question Harald Fuchs <hf118@protecting.net>
@ 2004-01-21 16:45 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2004-01-21 16:45 UTC (permalink / raw)
To: Harald Fuchs <hf118@protecting.net>; +Cc: pgsql-performance
On Wed, 21 Jan 2004, Harald Fuchs wrote:
> In article <200401201905.46699.dev@archonet.com>,
> Richard Huxton <dev@archonet.com> writes:
>
> > On Tuesday 20 January 2004 16:42, Tom Lane wrote:
> >> Harald Fuchs <hf118@protecting.net> writes:
> >> > Why? If the underlying table has a primary key, finding corresponding
> >> > pairs is trivial; if there isn't, it's impossible.
> >>
> >> Exactly. Nonetheless, the correspondence exists --- the UPDATE
> >> definitely updated some particular row of the OLD set into some
> >> particular one of the NEW set. If the trigger API makes it impossible
> >> to reconstruct the matchup, the API is broken.
>
> I would not say so. You could use tables without primary keys, and
> you could define statement-level triggers on them, but you could not
> identify a particular row in this very special and probably rare case.
A technique that requires matching of primary key values also undercuts
its usefulness for at least some types of triggers. For example, ON UPDATE
referential actions need three pieces of information, the starting state,
the end state and the mapping between those states. AFAICS you cannot
fake the last by trying to map primary key values and still implement the
constraint correctly. Even if 99.9% of the time the primary key value
doesn't change, you can't safly implement the triggers this way. Other
triggers of the same sort may run into the same problems.
> > Perhaps they should be cursors? The only sensible way I can think of working
> > with them would be:
> > 1. count how many rows affected
> > 2. step through one row at a time, doing something.
>
> When I read about the "insert" and "delete" pseudotables in a book
> about Transact-SQL, i was enthusiastic about the elegance of this
> idea: you're operating on multiple (perhaps lots of) rows, and the SQL
> way of doing that is by set-operations, i.e. single operations
> affecting a set of rows. Pseudotables extend this idea nicely into
> the area of statement-level triggers. Your cursor idea doesn't look
> very SQL-like to me.
>
> We really should find an Oracle/DB2/Informix guy who can tell us how
> to get that right.
It wouldn't surprise me if there was an internal key (or row number)
that could be used to match the rows between the old and new.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Trigger question
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
@ 2004-01-16 10:19 ` Chris Travers <chris@travelamericas.com>
1 sibling, 0 replies; 15+ messages in thread
From: Chris Travers @ 2004-01-16 10:19 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; pgsql-performance
> Exists in pg any way to define the trigger execution only if I have
> changes on some fields?
No, but you chould check for those fields and return if no changes have been
made. Depending on how intensive the trigger is, this might help. You may
also want to look at statement-level triggers or conditional rules.
Best Wishes,
Chris Travers
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2004-01-21 16:45 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-15 13:13 Trigger question pginfo <pginfo@t1.unisoftbg.com>
2004-01-15 16:10 ` Richard Huxton <dev@archonet.com>
2004-01-15 17:08 ` Evil Azrael <evilazrael@evilazrael.de>
2004-01-16 13:05 ` Harald Fuchs <hf99@protecting.net>
2004-01-20 00:01 ` Neil Conway <neilc@samurai.com>
2004-01-20 08:13 ` Richard Huxton <dev@archonet.com>
2004-01-20 15:17 ` Neil Conway <neilc@samurai.com>
2004-01-20 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 16:15 ` Harald Fuchs <hf118@protecting.net>
2004-01-20 16:30 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-20 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-20 19:05 ` Richard Huxton <dev@archonet.com>
2004-01-21 13:50 ` Harald Fuchs <hf118@protecting.net>
2004-01-21 16:45 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-16 10:19 ` Chris Travers <chris@travelamericas.com>
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