Problem with triggers

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?

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

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

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&gt; . 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

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&gt; . 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