================================================== ==========================
POSTGRESQL BUG REPORT TEMPLATE
================================================== ==========================


Your name : Thomas Erskine
Your email address : thomaserskine (AT) yahoo (DOT) 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 (AT) sympatico (DOT) ca> +1.613.591.8490



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Thomas Erskine <thomas.erskine (AT) sympatico (DOT) ca> writes:
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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Neil Conway 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 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





















---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Mon, Nov 10, 2003 at 21:48:30 +0100,
Gaetano Mendola <mendola (AT) bigfoot (DOT) com> wrote:
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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Gaetano Mendola <mendola (AT) bigfoot (DOT) com> writes:
It isn't -- trigger firing order is irrelevant to the original
question.

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Neil Conway wrote:

Well, it is. If the data integrity was done with a system trigger
created at table creation time the firing order is relevant.


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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Gaetano Mendola <mendola (AT) bigfoot (DOT) com> writes:
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.

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)