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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
