Thread: before trigger doesn't, on insert of too long data
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Thomas Erskine
Your email address : thomaserskine@yahoo.com
System Configuration
---------------------
Architecture (example: Intel Pentium) :Intel Pentium III
Operating System (example: Linux 2.0.26 ELF) :Linux 2.4.20 ELF
PostgreSQL version (example: PostgreSQL-7.2.3):PostgreSQL-7.3.4-1PGDG
Compiler used (example: gcc 2.95.2) : the RPM did it :-)
Please enter a FULL description of your problem:
------------------------------------------------
A before trigger doesn't always fire. If a column being inserted into is
too small for the incoming data, psql complains:
ERROR: value too long for type ...
without giving the trigger procedure a chance to deal with it.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
------ cut here ------
CREATE TABLE test(
id INTEGER,
code CHARACTER(4)
);
CREATE FUNCTION test_func()
RETURNS TRIGGER
AS 'BEGIN
IF LENGTH(new.code) > 4
THEN new.code = ''xxxx'';
END IF;
new.code = upper(new.code);
RETURN new;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER test_trig
BEFORE INSERT
ON test
FOR EACH ROW
EXECUTE PROCEDURE test_func();
INSERT INTO test VALUES( 1, 'aaaa');
INSERT INTO test VALUES( 2, 'bbbbb');
------ cut here ------
The first INSERT works fine and the function is triggered. The second
INSERT never triggers the function; it just complains.
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Thomas Erskine <thomas.erskine@sympatico.ca> +1.613.591.8490
Thomas Erskine <thomas.erskine@sympatico.ca> writes: > A before trigger doesn't always fire. If a column being inserted into is > too small for the incoming data, psql complains: > ERROR: value too long for type ... > without giving the trigger procedure a chance to deal with it. I believe this is a feature, not a bug: a CHAR(4) field should never, ever contain > 4 characters. Whether there is a trigger that is yet to be processed is not relevant. I'd suggest changing the type of the column to be TEXT, or similar (which should be just as efficient CHAR(4)). -Neil
Neil Conway wrote: > Thomas Erskine <thomas.erskine@sympatico.ca> writes: > >>A before trigger doesn't always fire. If a column being inserted into is >>too small for the incoming data, psql complains: >> ERROR: value too long for type ... >>without giving the trigger procedure a chance to deal with it. > > > I believe this is a feature, not a bug: a CHAR(4) field should never, > ever contain > 4 characters. Whether there is a trigger that is yet to > be processed is not relevant. Seems to me too, from the standard: "The order of execution of a set of triggers is ascending by value of their timestamp of creation in their descriptors, such that the oldest trigger executes first. If one or more triggers have the same timestamp value, then their relative order of execution is implementation-defined." I don't know how the check for the data integrity is implemented but if is a trigger that trigger is for sure older then the user defined trigger. BTW I did this experiment: 1) Create table 2) create a before insert trigger: trigger_a 3) create a before insert trigger: trigger_b test=# insert into test values ( 10 ); NOTICE: TRIGGER A NOTICE: TRIGGER B INSERT 3416835 1 4) drop trigger: trigger_a 5) create trigger: trigger_a ( now trigger b is the oldest one) test=# insert into test values ( 10 ); NOTICE: TRIGGER A NOTICE: TRIGGER B INSERT 3416836 1 why this ? Regards Gaetano Mendola
On Mon, Nov 10, 2003 at 21:48:30 +0100, Gaetano Mendola <mendola@bigfoot.com> wrote: > Seems to me too, from the standard: > > "The order of execution of a set of triggers is ascending by value of > their timestamp of creation in their > descriptors, such that the oldest trigger executes first. If one or more > triggers have the same timestamp value, > then their relative order of execution is implementation-defined." I don't think Postgres uses that ordering. My memory is that it is based on the collation order of the trigger name because that allowed better control of trigger firing order.
Gaetano Mendola <mendola@bigfoot.com> writes: > I don't know how the check for the data integrity is implemented but if > is a trigger It isn't -- trigger firing order is irrelevant to the original question. > 1) Create table > 2) create a before insert trigger: trigger_a > 3) create a before insert trigger: trigger_b > > test=# insert into test values ( 10 ); > NOTICE: TRIGGER A > NOTICE: TRIGGER B > INSERT 3416835 1 The firing order of triggers in PostgreSQL is documented: it is done alphabetically. When this was discussed, the consensus was that the "fire by creation order" part of the spec is not very useful, so we decided to deviate from it deliberately. -Neil
Neil Conway wrote: > Gaetano Mendola <mendola@bigfoot.com> writes: > >>I don't know how the check for the data integrity is implemented but if >>is a trigger > > > It isn't -- trigger firing order is irrelevant to the original > question. Well, it is. If the data integrity was done with a system trigger created at table creation time the firing order is relevant. >>1) Create table >>2) create a before insert trigger: trigger_a >>3) create a before insert trigger: trigger_b >> >>test=# insert into test values ( 10 ); >>NOTICE: TRIGGER A >>NOTICE: TRIGGER B >>INSERT 3416835 1 > > > The firing order of triggers in PostgreSQL is documented: it is done > alphabetically. When this was discussed, the consensus was that the > "fire by creation order" part of the spec is not very useful, so we > decided to deviate from it deliberately. Good to hear. So, why the standard is there ? I'm sorry to say that this is a typical MySQL guy response. Anyway this is my argument against the alphabetic order: before to create a trigger is reasonable to know that the data catched by the trigger are the data that I see inserted in the table when I do an insert. If my aaaaaa trigger is fired before the other I'm not anymore sure about the data catched; shall I call my triggers zzzzzzz in order to be sure to not break previous trigger set behavior? Regards Gaeatano Mendola
Gaetano Mendola <mendola@bigfoot.com> writes: > Well, it is. If the data integrity was done with a system trigger > created at table creation time the firing order is relevant. Right, but the data integrity check is _not_ done via a system trigger. Hence, "trigger firing order is irrelevant to the original question", as I said earlier. > Good to hear. So, why the standard is there ? According to the docs, "PostgreSQL development tends to aim for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense." The previous consensus seemed to be that being non-conformant with the standard in this area was worth it. I'm not particularly attached to the current behavior though, so feel free to restate your case for changing the trigger firing order on -bugs. -Neil