Обсуждение: [GENERAL] 'value too long' and before insert/update trigger
Hi all I'm currently migrating a legacy Informix 4gl application to run on PostgreSQL (v9.5.8) There are errors occurring because sometimes the application tries to insert/update values longer than the definition ofthe database column. The error message is eg. "value too long for type character(20)". The behaviour under Informix is to silently truncate the saved value, but with PostgreSQL it crashes our application program.Obviously the short answer is to modify the application so that it does not attempt to save values that are too long,but with 1000+ programs and approx. 8000 insert/update statements to review this will take a lot of time. I was hoping to emulate the Informix behaviour by creating triggers to run before each insert/update that would check thelength of the values being saved and truncate if too long. However I'm still getting the error messages. Presumably thelength validation is being done before the trigger is run. Is there some way this could be changed so the trigger happensfirst? I've seen examples that involve changing the char type columns to text, but this changes the semantics of string comparisonswith regard to trailing spaces, and I'm concerned that this might cause subtle and hard to find problems withinthe application. Thanks Kevin
Hi > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Kevin Golding > Sent: Donnerstag, 24. August 2017 07:08 > To: pgsql-general@postgresql.org > Subject: [GENERAL] 'value too long' and before insert/update trigger > > Hi all > I'm currently migrating a legacy Informix 4gl application to run on PostgreSQL (v9.5.8) > > There are errors occurring because sometimes the application tries to insert/update values longer than the > definition of the database column. > The error message is eg. "value too long for type character(20)". > > The behaviour under Informix is to silently truncate the saved value, but with PostgreSQL it crashes our application > program. Obviously the short answer is to modify the application so that it does not attempt to save values that are > too long, but with 1000+ programs and approx. 8000 insert/update statements to review this will take a lot of time. If there is no good reason to force the length of the field to be a maximum of 20 characters, you may change the data typeto text. > I was hoping to emulate the Informix behaviour by creating triggers to run before each insert/update that would > check the length of the values being saved and truncate if too long. However I'm still getting the error messages. > Presumably the length validation is being done before the trigger is run. Is there some way this could be changed so > the trigger happens first? When you create the trigger you can define if the function must be called before or after the action. Something like: CREATE TRIGGER name BEFORE INSERT OR UPDATE ON table_name FOR EACH ROW EXECUTE PROCEDURE ...; In the function you must then replace the value of the string before you return the NEW record to the calling process: [...] NEW.variable_name := substr(NEW.variable_name,1,20); [...] RETURN NEW; Hope this helps. Bye Charles > I've seen examples that involve changing the char type columns to text, but this changes the semantics of string > comparisons with regard to trailing spaces, and I'm concerned that this might cause subtle and hard to find problems > within the application. > > Thanks > Kevin > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Hi
2017-08-24 7:08 GMT+02:00 Kevin Golding <KGolding@axessgroup.com.au>:
Hi all
I'm currently migrating a legacy Informix 4gl application to run on PostgreSQL (v9.5.8)
There are errors occurring because sometimes the application tries to insert/update values longer than the definition of the database column.
The error message is eg. "value too long for type character(20)".
The behaviour under Informix is to silently truncate the saved value, but with PostgreSQL it crashes our application program. Obviously the short answer is to modify the application so that it does not attempt to save values that are too long, but with 1000+ programs and approx. 8000 insert/update statements to review this will take a lot of time.
I was hoping to emulate the Informix behaviour by creating triggers to run before each insert/update that would check the length of the values being saved and truncate if too long. However I'm still getting the error messages. Presumably the length validation is being done before the trigger is run. Is there some way this could be changed so the trigger happens first?
I've seen examples that involve changing the char type columns to text, but this changes the semantics of string comparisons with regard to trailing spaces, and I'm concerned that this might cause subtle and hard to find problems within the application.
Thanks
Kevin
The most simple way is using custom type with similar behave like Informix - I did it with port from MySQL
you can use google translator - the article is in Czech language - but can be translated
Regards
Pavel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wednesday, August 23, 2017, Kevin Golding <KGolding@axessgroup.com.au> wrote:
Presumably the length validation is being done before the trigger is run. Is there some way this could be changed so the trigger happens first?
The input tuple passed into the trigger is a valid record of the same type as the table to which it is attached. This means that while table constraints are not enforced all column values must already be valid for the defined column type. I suspect changing this property of the system is unlikely though I agree that I too have sometimes wished that a trigger could be used to make invalid data, for a given type, valid.
Some hackish use of views, and triggers thereon, may provide an avenue to gradual migration.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Wednesday, August 23, 2017, Kevin Golding <KGolding@axessgroup.com.au> > wrote: >> Presumably the length validation is being done before the trigger is run. >> Is there some way this could be changed so the trigger happens first? > The input tuple passed into the trigger is a valid record of the same type > as the table to which it is attached. This means that while table > constraints are not enforced all column values must already be valid for > the defined column type. Right, that's why this isn't likely to change. You could replace the datatype-related limit with a CHECK constraint, and then it'd be possible for a BEFORE trigger to modify the value to make it compliant before the constraint is checked. regards, tom lane