Re: [GENERAL] Own messages for constraints?
On Fri, 2007-03-16 at 20:19 +0100, Kacper Chrapa wrote: Hi ! Is it possible in postgres to define own message for some constraint? Example: If i create check constraint on zip_code column , i want to get a message: Zip code is invalid.Please,input code in format: nn-nnn and I want to send this message to the end user. It will be much better(in my case) than violates constraint zip_code_check :-) . I can make this validation in trigger (and send msg to application by notice or raise),but in this case i will duplicate validation rules (in trigger and in constraint).Effect: Lower performance(i think) and rules in two places... There's no custom message for a CHECK constraint violation. You can use an AFTER trigger instead of a CHECK constraint (but that may have a performance impact - test for your application). You can also give descriptive names to your CHECK constraint which may help. It's not a good idea to pass database errors directly back to the user anyway. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Own messages for constraints?
On 3/19/07, Jeff Davis [EMAIL PROTECTED] wrote: You can use an AFTER trigger instead of a CHECK constraint (but that may have a performance impact - test for your application). are you sure you meant AFTER? why? generally data-checks should be in before triggers. i guess. depesz -- http://www.depesz.com/ - nowy, lepszy depesz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Own messages for constraints?
On Mon, Mar 19, 2007 at 07:08:41PM +0100, hubert depesz lubaczewski wrote: On 3/19/07, Jeff Davis [EMAIL PROTECTED] wrote: You can use an AFTER trigger instead of a CHECK constraint (but that may have a performance impact - test for your application). are you sure you meant AFTER? why? generally data-checks should be in before triggers. i guess. In an AFTER trigger you can be sure you're seeing what actually got inserted. In a BEFORE trigger other triggers after you could still modify the data... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Own messages for constraints?
On 3/19/07, Martijn van Oosterhout kleptog@svana.org wrote: In an AFTER trigger you can be sure you're seeing what actually got inserted. In a BEFORE trigger other triggers after you could still modify the data... yes but in after trigger the only thing you can do is to raise exception. you cannot fix the data, issue warning, or simply stop the insert/update without breaking the transaction. depesz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Own messages for constraints?
On Mon, 2007-03-19 at 19:08 +0100, hubert depesz lubaczewski wrote: On 3/19/07, Jeff Davis [EMAIL PROTECTED] wrote: You can use an AFTER trigger instead of a CHECK constraint (but that may have a performance impact - test for your application). are you sure you meant AFTER? why? generally data-checks should be in before triggers. i guess. If you do the check BEFORE, you have to make sure that no other BEFORE triggers that execute afterward modify the data again. Assuming your AFTER trigger is on INSERT and UPDATE, there is no way for a subsequent AFTER trigger to modify the data to be invalid. So an AFTER trigger is more of an assurance that your data is valid. Note that AFTER triggers need to queue up, so if you do a huge update and have an AFTER trigger, it might use a lot of memory. BEFORE triggers don't have that problem. If you're very concerned about this you could use a BEFORE trigger and just make sure that no other trigger will cause a problem. Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Own messages for constraints?
On Mon, 2007-03-19 at 19:26 +0100, hubert depesz lubaczewski wrote: On 3/19/07, Martijn van Oosterhout kleptog@svana.org wrote: In an AFTER trigger you can be sure you're seeing what actually got inserted. In a BEFORE trigger other triggers after you could still modify the data... yes but in after trigger the only thing you can do is to raise exception. you cannot fix the data, issue warning, or simply stop the insert/update without breaking the transaction. If you only issue a warning, it's not a constraint because data violating the constraint still goes in. And you can issue a warning in an AFTER trigger. Fixing the data is probably something that should be done in a different place (like the application correcting the data). It also begs the question: If the data can be fixed, why is the original form not acceptable anyway (i.e. fixed in the datatype's input function)? I assume by stop the insert/update without breaking the transaction you mean a return NULL from the BEFORE trigger, thereby not inserting the row. COMMIT should mean yes, I successfully completed what you asked, and that usually means that the data was actually inserted. You're correct that you have more flexibility with a BEFORE trigger in many ways. However, be careful using those strategies to constrain data. Generally you do want it to break the transaction if the data you're trying to insert is invalid. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Own messages for constraints?
Hi ! Is it possible in postgres to define own message for some constraint? Example: If i create check constraint on zip_code column , i want to get a message: Zip code is invalid.Please,input code in format: nn-nnn and I want to send this message to the end user. It will be much better(in my case) than violates constraint zip_code_check :-) . I can make this validation in trigger (and send msg to application by notice or raise),but in this case i will duplicate validation rules (in trigger and in constraint).Effect: Lower performance(i think) and rules in two places... Best regards and thanks for help, Kacper Chrapa Fantastyczna promocja w RUCHu! Rób zakupy, wysyłaj SMSy i codziennie wygrywaj bony pieniężne! W finale do wygrania Nissan NOTE. Sprawdź: http://klik.wp.pl/?adr=www.loteriaruch.plsid=1062 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
