Horton

Thread

  1. Problem with triggers

    Sid <sid.the.technician@gmail.com> — 2010-06-15T21:01:29Z

    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

  2. Re: Problem with triggers

    Adrian Klaver <adrian.klaver@gmail.com> — 2010-06-15T22:56:14Z

    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

  3. Re: Problem with triggers

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-06-16T03:14:20Z

    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

  4. Re: Problem with triggers

    Adrian von Bidder <avbidder@fortytwo.ch> — 2010-06-16T04:56:26Z

    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?

  5. Re: Problem with triggers

    Sid <sid.the.technician@gmail.com> — 2010-06-16T05:17:12Z

    2010/6/16 Tom Lane <tgl@sss.pgh.pa.us>

> 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.

I tried to search for this problem, but I failed :(. I spend few hours
trying to find what is
wrong with my code.....

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.
>
I didn't think about that,

>
> 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.
>
Yes, I'll probably do this that way.


> 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.
>
No. This solution is too ugly even for me :)


>
>                        regards, tom lane
>

Thank you for your explanation, Tom


-- 
Best regards
Sid

  6. Re: Problem with triggers

    Sid <sid.the.technician@gmail.com> — 2010-06-16T05:26:27Z

    2010/6/16 Adrian von Bidder <avbidder@fortytwo.ch>

> 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 ...
>
> My plan was to keep as much as possible of the application logic on the
database side.
Triggers were not so complex, because I used one trigger function for many
tables (http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers),so
printing user friendly information about null values was just one line.



> > 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
>
>
-- 
Best regards
Sid

  7. Dynamic triggers

    Rob Richardson <rob.richardson@rad-con.com> — 2010-06-16T12:29:39Z

    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

  8. Re: Problem with triggers

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-06-16T14:53:04Z

    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

  9. Re: Dynamic triggers

    Adrian Klaver <adrian.klaver@gmail.com> — 2010-06-16T22:14:12Z

    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