pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger definition . . . puzzled
7+ messages / 3 participants
[nested] [flat]
* Trigger definition . . . puzzled
@ 2007-12-12 21:38 Rolf A. de By <deby@itc.nl>
0 siblings, 1 reply; 7+ messages in thread
From: Rolf A. de By @ 2007-12-12 21:38 UTC (permalink / raw)
To: pgsql-sql
Greetings list,
Running pg 8.2.3. on a windows machine, I have become blind in a
trigger definition puzzle, so hope that somebody may help me understand
where I goof.
I have a base table i_s that has three tables that inherit from it, one
of them being i_s_nowhere. The base table should be left empty, but I
want it to be the prime port of call for data changes. Hence the
following set-up. Let me try to be brief, in the hope of not leaving
out relevant detail.
Base table def is as follows:
CREATE TABLE i_s
(
sidx integer NOT NULL, -- The s identifier
gid integer NOT NULL, -- The i identifier
status character(1),
confirmation character(1),
CONSTRAINT pk_is PRIMARY KEY (sidx, gid)
)
WITH (OIDS=FALSE);
And the trigger definition is here:
CREATE TRIGGER aw_archival_is2
BEFORE UPDATE OR INSERT OR DELETE
ON i_s
FOR EACH ROW
EXECUTE PROCEDURE aw_archive_test();
And the trigger function:
CREATE OR REPLACE FUNCTION aw_archive_test()
RETURNS "trigger" AS
$BODY$
BEGIN
RAISE WARNING 'Starting isa trigger for %', TG_OP;
IF (TG_OP = 'UPDATE') THEN
RAISE WARNING 'Calling insert_isa with update';
ELSIF (TG_OP = 'INSERT') THEN
RAISE WARNING 'Calling insert_isa with insert';
ELSIF (TG_OP = 'DELETE') THEN
RAISE WARNING 'Calling insert_isa with delete';
END IF; RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
The trigger is enabled.
Yes, this does intentionally nothing. The real code will obviously take
care of data change in proper subtables. Well, the trigger should do
nothing now . . . What I cannot get round to understanding is that an
insert attempt will nicely give me two warnings, and will not insert, as
expected with this code:
WARNING: Starting isa trigger for INSERT
WARNING: Calling insert_isa with insert
Query returned successfully: 0 rows affected, 31 ms execution time.
But an attempt to update actually magically goes to the proper subtable
and performs the update:
Query returned successfully: 1 rows affected, 16 ms execution time.
Where did I deserve this?? ;-)
In attempts to solve this I did mess around with trigger and trigger
function definitions a bit. Could there be funny traces of this? What
is the best way to analyse this behavior? I am testing from a pgAdmin
1.8 setup.
All suggestions welcome!
--
Rolf A. de By
The Netherlands
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trigger definition . . . puzzled
@ 2007-12-12 22:30 Erik Jones <erik@myemma.com>
parent: Rolf A. de By <deby@itc.nl>
0 siblings, 0 replies; 7+ messages in thread
From: Erik Jones @ 2007-12-12 22:30 UTC (permalink / raw)
To: Rolf A. de By <deby@itc.nl>; +Cc: pgsql-sql
On Dec 12, 2007, at 3:38 PM, Rolf A. de By wrote:
> Greetings list,
>
> Running pg 8.2.3. on a windows machine, I have become blind in a
> trigger definition puzzle, so hope that somebody may help me
> understand where I goof.
>
> I have a base table i_s that has three tables that inherit from it,
> one of them being i_s_nowhere. The base table should be left
> empty, but I want it to be the prime port of call for data
> changes. Hence the following set-up. Let me try to be brief, in
> the hope of not leaving out relevant detail.
>
> Base table def is as follows:
>
> CREATE TABLE i_s
> (
> sidx integer NOT NULL, -- The s identifier
> gid integer NOT NULL, -- The i identifier
> status character(1),
> confirmation character(1),
> CONSTRAINT pk_is PRIMARY KEY (sidx, gid)
> )
> WITH (OIDS=FALSE);
>
> And the trigger definition is here:
>
> CREATE TRIGGER aw_archival_is2
> BEFORE UPDATE OR INSERT OR DELETE
> ON i_s
> FOR EACH ROW
> EXECUTE PROCEDURE aw_archive_test();
>
> And the trigger function:
>
> CREATE OR REPLACE FUNCTION aw_archive_test()
> RETURNS "trigger" AS
> $BODY$
> BEGIN
> RAISE WARNING 'Starting isa trigger for %', TG_OP;
> IF (TG_OP = 'UPDATE') THEN
> RAISE WARNING 'Calling insert_isa with update';
> ELSIF (TG_OP = 'INSERT') THEN
> RAISE WARNING 'Calling insert_isa with insert';
> ELSIF (TG_OP = 'DELETE') THEN
> RAISE WARNING 'Calling insert_isa with delete';
> END IF; RETURN NULL;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
> The trigger is enabled.
>
> Yes, this does intentionally nothing. The real code will obviously
> take care of data change in proper subtables. Well, the trigger
> should do nothing now . . . What I cannot get round to
> understanding is that an insert attempt will nicely give me two
> warnings, and will not insert, as expected with this code:
>
> WARNING: Starting isa trigger for INSERT
> WARNING: Calling insert_isa with insert
>
> Query returned successfully: 0 rows affected, 31 ms execution time.
>
> But an attempt to update actually magically goes to the proper
> subtable and performs the update:
>
> Query returned successfully: 1 rows affected, 16 ms execution time.
>
> Where did I deserve this?? ;-)
>
> In attempts to solve this I did mess around with trigger and
> trigger function definitions a bit. Could there be funny traces of
> this? What is the best way to analyse this behavior? I am testing
> from a pgAdmin 1.8 setup.
The function you've shown won't do anything because BEFORE row
triggers that return NULL don't do anything (for that row). If you
want the operation to continue without any modification then just
return NEW.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trigger definition . . . puzzled
@ 2007-12-13 07:19 Rolf A. de By <deby@itc.nl>
0 siblings, 1 reply; 7+ messages in thread
From: Rolf A. de By @ 2007-12-13 07:19 UTC (permalink / raw)
To: pgsql-sql
Erik,
Thanks for that. There is some misunderstanding here. For this example,
I had taken the sting out of my trigger function and turned it into a
much more concise no-op, with warnings. The actual code of my original
trigger function is irrelevant. The no-op trigger function displays the
same strange behaviour: it works as expected for INSERTs, but not for
UPDATEs. The update goes through! And it shouldn't.
My question to the list is how I can analyze what is happening here, I
am lost at where to start on that.
Rolf
> Greetings list,
>
> Running pg 8.2.3. on a windows machine, I have become blind in a
> trigger definition puzzle, so hope that somebody may help me
> understand where I goof.
>
> I have a base table i_s that has three tables that inherit from it,
> one of them being i_s_nowhere. The base table should be left empty,
> but I want it to be the prime port of call for data changes. Hence
> the following set-up. Let me try to be brief, in the hope of not
> leaving out relevant detail.
>
> Base table def is as follows:
>
> CREATE TABLE i_s
> (
> sidx integer NOT NULL, -- The s identifier
> gid integer NOT NULL, -- The i identifier
> status character(1),
> confirmation character(1),
> CONSTRAINT pk_is PRIMARY KEY (sidx, gid)
> )
> WITH (OIDS=FALSE);
>
> And the trigger definition is here:
>
> CREATE TRIGGER aw_archival_is2
> BEFORE UPDATE OR INSERT OR DELETE
> ON i_s
> FOR EACH ROW
> EXECUTE PROCEDURE aw_archive_test();
>
> And the trigger function:
>
> CREATE OR REPLACE FUNCTION aw_archive_test()
> RETURNS "trigger" AS
> $BODY$
> BEGIN
> RAISE WARNING 'Starting isa trigger for %', TG_OP;
> IF (TG_OP = 'UPDATE') THEN
> RAISE WARNING 'Calling insert_isa with update';
> ELSIF (TG_OP = 'INSERT') THEN
> RAISE WARNING 'Calling insert_isa with insert';
> ELSIF (TG_OP = 'DELETE') THEN
> RAISE WARNING 'Calling insert_isa with delete';
> END IF; RETURN NULL;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
> The trigger is enabled.
>
> Yes, this does intentionally nothing. The real code will obviously
> take care of data change in proper subtables. Well, the trigger
> should do nothing now . . . What I cannot get round to understanding
> is that an insert attempt will nicely give me two warnings, and will
> not insert, as expected with this code:
>
> WARNING: Starting isa trigger for INSERT
> WARNING: Calling insert_isa with insert
>
> Query returned successfully: 0 rows affected, 31 ms execution time.
>
> But an attempt to update actually magically goes to the proper
> subtable and performs the update:
>
> Query returned successfully: 1 rows affected, 16 ms execution time.
>
> Where did I deserve this?? ;-)
>
> In attempts to solve this I did mess around with trigger and trigger
> function definitions a bit. Could there be funny traces of this?
> What is the best way to analyse this behavior? I am testing from a
> pgAdmin 1.8 setup.
The function you've shown won't do anything because BEFORE row triggers
that return NULL don't do anything (for that row). If you want the
operation to continue without any modification then just return NEW.
Erik Jones
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trigger definition . . . puzzled
@ 2007-12-13 15:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rolf A. de By <deby@itc.nl>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-12-13 15:49 UTC (permalink / raw)
To: Rolf A. de By <deby@itc.nl>; +Cc: pgsql-sql
"Rolf A. de By" <deby@itc.nl> writes:
> Thanks for that. There is some misunderstanding here. For this example,
> I had taken the sting out of my trigger function and turned it into a
> much more concise no-op, with warnings. The actual code of my original
> trigger function is irrelevant. The no-op trigger function displays the
> same strange behaviour: it works as expected for INSERTs, but not for
> UPDATEs. The update goes through! And it shouldn't.
Reading between the lines, I gather you have an inheritance setup and
are expecting a trigger on the parent table to fire for events occurring
in the child tables. Doesn't work like that; you need to put triggers
on the child tables.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trigger definition . . . puzzled
@ 2007-12-13 15:59 Rolf A. de By <deby@itc.nl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Rolf A. de By @ 2007-12-13 15:59 UTC (permalink / raw)
To: pgsql-sql
Thanks Tom,
Yes, this is an inheritance set-up. But actually no: I am executing all
my data changes against the parent table, and want the trigger on that
parent table to fire for an insert on the parent table as it does. But
I also want the trigger to fire when an update on the parent table is
executed, and this does not happen.
Rolf
Tom Lane wrote:
> "Rolf A. de By" <deby@itc.nl> writes:
>
>> Thanks for that. There is some misunderstanding here. For this example,
>> I had taken the sting out of my trigger function and turned it into a
>> much more concise no-op, with warnings. The actual code of my original
>> trigger function is irrelevant. The no-op trigger function displays the
>> same strange behaviour: it works as expected for INSERTs, but not for
>> UPDATEs. The update goes through! And it shouldn't.
>>
>
> Reading between the lines, I gather you have an inheritance setup and
> are expecting a trigger on the parent table to fire for events occurring
> in the child tables. Doesn't work like that; you need to put triggers
> on the child tables.
>
> regards, tom lane
>
--
dr ir Rolf A. de By
assoc prof Geodata Management and Engineering
dept Geoinformation Processing
ITC -- International Institute for Geo-information Science & Earth Observation
Hengelosestraat 99
7500 AA Enschede, The Netherlands
ph +31 53 4874553 fx +31 53 4874335
email deby@itc.nl
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trigger definition . . . puzzled
@ 2007-12-13 16:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rolf A. de By <deby@itc.nl>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-12-13 16:33 UTC (permalink / raw)
To: Rolf A. de By <deby@itc.nl>; +Cc: pgsql-sql
"Rolf A. de By" <deby@itc.nl> writes:
> Yes, this is an inheritance set-up. But actually no: I am executing all
> my data changes against the parent table, and want the trigger on that
> parent table to fire for an insert on the parent table as it does. But
> I also want the trigger to fire when an update on the parent table is
> executed, and this does not happen.
There is no update on any row in the parent table. The update is on a
row in a child table.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trigger definition . . . puzzled
@ 2007-12-13 20:22 Rolf A. de By <deby@itc.nl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Rolf A. de By @ 2007-12-13 20:22 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Ouch, that hurts! It suddenly dawns on me . . . Big difference on table
that you specify and table where it
actually takes effect. Confusing at first. Thanks a million, Tom.
> "Rolf A. de By" <deby@itc.nl> writes:
>
>> Yes, this is an inheritance set-up. But actually no: I am executing all
>> my data changes against the parent table, and want the trigger on that
>> parent table to fire for an insert on the parent table as it does. But
>> I also want the trigger to fire when an update on the parent table is
>> executed, and this does not happen.
>>
>
> There is no update on any row in the parent table. The update is on a
> row in a child table.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-12-13 20:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-12 21:38 Trigger definition . . . puzzled Rolf A. de By <deby@itc.nl>
2007-12-12 22:30 ` Erik Jones <erik@myemma.com>
2007-12-13 07:19 Re: Trigger definition . . . puzzled Rolf A. de By <deby@itc.nl>
2007-12-13 15:49 ` Re: Trigger definition . . . puzzled Tom Lane <tgl@sss.pgh.pa.us>
2007-12-13 15:59 ` Re: Trigger definition . . . puzzled Rolf A. de By <deby@itc.nl>
2007-12-13 16:33 ` Re: Trigger definition . . . puzzled Tom Lane <tgl@sss.pgh.pa.us>
2007-12-13 20:22 ` Re: Trigger definition . . . puzzled Rolf A. de By <deby@itc.nl>
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