Обсуждение: Problem with triggers

Hi,

I am writing trigger function for validating values inserted into table. The goal is to print user friendly messages when inserted value is wrong.
When I check for null values everything works as expected:
Inside trigger I have lines:
if (new.tvalue is null) then
            RAISE EXCEPTION 'error message';
end if;
This one works ok.

But when I try to do something like that:

if length(new.tvalue) > 20 then
      RAISE EXCEPTION 'error message - too long';
end if;

then I get just information from database that given field value is to long (of course only when I try insert too long value).

In fact even if I put RAISE EXCEPTION as first line inside trigger it does not work.
All I get is ERROR: value to long......



Trigger declaration looks as follows:
CREATE TRIGGER bir_validate
  BEFORE INSERT OR UPDATE
  ON portal.documentation
  FOR EACH ROW
  EXECUTE PROCEDURE portal.dynamic_trigger_validate();

Postgres 8.4.4 on Windows 7 32bit

My question is: why do I get information about too long value before trigger fires?
Can I change this behavior?


--
Best regards
Sid

On 06/15/2010 02:01 PM, Sid wrote:
> Hi,
>
> I am writing trigger function for validating values inserted into table. The
> goal is to print user friendly messages when inserted value is wrong.
> When I check for null values everything works as expected:
> Inside trigger I have lines:
> if (new.tvalue is null) then
>              RAISE EXCEPTION 'error message';
> end if;
> This one works ok.
>
> But when I try to do something like that:
>
> if length(new.tvalue)>  20 then
>        RAISE EXCEPTION 'error message - too long';
> end if;
>
> then I get just information from database that given field value is to long
> (of course only when I try insert too long value).
>
> In fact even if I put RAISE EXCEPTION as first line inside trigger it does
> not work.
> All I get is ERROR: value to long......
>
>
>
> Trigger declaration looks as follows:
> CREATE TRIGGER bir_validate
>    BEFORE INSERT OR UPDATE
>    ON portal.documentation
>    FOR EACH ROW
>    EXECUTE PROCEDURE portal.dynamic_trigger_validate();
>
> Postgres 8.4.4 on Windows 7 32bit
>
> My question is: why do I get information about too long value before trigger
> fires?
> Can I change this behavior?
>
>

The database is beating you to the validation. Basically you are trying
to override the built in validation. To make this work you will need to
let the field be longer than you want i.e varchar with no length
argument and then let your trigger handle the validations.

--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 06/15/2010 02:01 PM, Sid wrote:
>> I am writing trigger function for validating values inserted into table. The
>> goal is to print user friendly messages when inserted value is wrong.

>> My question is: why do I get information about too long value before trigger
>> fires?

> The database is beating you to the validation.

People try this every few months :-(, but it's basically a dead-end idea.
A large majority of the things you might want to report an error for are
going to be rejected by the datatype input functions for the column
datatypes --- for example, you're not going to be able to "print a user
friendly message" on a bad timestamp, because that will be noticed long
before any trigger gets to fire.

You can either decide that the built-in error messages aren't so awful
after all, or do your data validation on the client side.

Or I guess you could lobotomize the database completely by making all
your fields be unlimited-length varchar so that there's no interesting
checking to be done.  But you really, really don't want to go there.

            regards, tom lane

Heyho!

On Wednesday 16 June 2010 00.56:14 Adrian Klaver wrote:
> > My question is: why do I get information about too long value before
> > trigger fires?
> > Can I change this behavior?

I firmly feel friendly error messages like this firmly beong into the
application and not into the DB.  Next thing you'll want translated messages
as well, and your triggers become so complex that you don't wnat to maintain
them ...

> The database is beating you to the validation.

With triggers.  A question to the experts: Couldn't this, in theory, be
implememnted within the rules system?  From what I understand they are run
right after the query is parsed; I'd expect data validation to come a bit
later.  Not sure if this is right.

cheers
-- vbi


--
Or is it?

Thank you for your explanation, Tom


--
Best regards
Sid

--
Best regards
Sid

Adrian von Bidder <avbidder@fortytwo.ch> writes:
> On Wednesday 16 June 2010 00.56:14 Adrian Klaver wrote:
>> The database is beating you to the validation.

> With triggers.  A question to the experts: Couldn't this, in theory, be
> implememnted within the rules system?

No, it's pretty much the same problem.  If you've got something like
    INSERT INTO foo VALUES('bar');
the literal is already going to have been fed to the appropriate input
function long before any rule would be considered.

If you're really desperate to do this type of checking on the database
side, you could consider having your app call a stored procedure instead
of trying to touch the table directly.  For example,

    CREATE FUNCTION insert_into_foo(text, text, text) ...

    SELECT insert_into_foo('bar', 'baz', ...);

The function would then try to cast its input strings to the appropriate
types, and could catch errors and replace them with its own messages.

Mind you, I remain far from convinced that you're going to improve on
the built-in error messages this way.

            regards, tom lane

On Wednesday 16 June 2010 5:29:39 am Rob Richardson wrote:
> Sid posted a link to a Wiki example of a dynamic trigger:
> http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
> <http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers> .  The link
> shows a trigger, but it doesn't say anything about what its purpose is
> or what a dynamic trigger is supposed to be good for.  What is it good
> for?
>
> Thank you!
>
> RobR

The dynamic part is the EXECUTE statement. It allows you to build a query on the
fly. More importantly it overrides the default behavior of caching the plan the
first time a function is run in a session. See below for more detail:

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

--
Adrian Klaver
adrian.klaver@gmail.com