Thread: Rollback when value too long
Hi, we are using "PostgreSQL 7.3.1 on i386-pc-linux-gnu" with Perl DBI. Some tables where directly updated from web forms, so when the input is longer than the fileld, Postgres forces a rollback. This might be right, but is very angry for me. Is there a way to teach Postgres that it must truncate these values? Thanks in advance, Bart
Check the length of data before saving it and truncate if needed. > Some tables where directly updated from web forms, so when the input > is longer than the fileld, Postgres forces a rollback. This might be > right, but is very angry for me. Is there a way to teach Postgres > that it must truncate these values?
On Fri, 16 May 2003, Bartlomiej Frackiewicz wrote: > Hi, > > we are using "PostgreSQL 7.3.1 on i386-pc-linux-gnu" with Perl DBI. > > Some tables where directly updated from web forms, so when the input > is longer than the fileld, Postgres forces a rollback. This might be > right, but is very angry for me. Is there a way to teach Postgres > that it must truncate these values? No. Can't the perl code truncate them? -- Nigel Andrews
Hi, thanks for answering. Sure Perl can (handmade, DBI do not support this, or i am blindly), but i was in hope that i can solvethis with the DB. Anyway, thanks. Bart -----Ursprüngliche Nachricht----- Von: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk] Gesendet: Freitag, 16. Mai 2003 14:04 An: Bartlomiej Frackiewicz Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Rollback when value too long On Fri, 16 May 2003, Bartlomiej Frackiewicz wrote: > Hi, > > we are using "PostgreSQL 7.3.1 on i386-pc-linux-gnu" with Perl DBI. > > Some tables where directly updated from web forms, so when the input > is longer than the fileld, Postgres forces a rollback. This might be > right, but is very angry for me. Is there a way to teach Postgres > that it must truncate these values? No. Can't the perl code truncate them? -- Nigel Andrews
--- Bartlomiej Frackiewicz <frackiewicz@inity.de> wrote: > Hi, > > thanks for answering. Sure Perl can (handmade, DBI > do not support this, or i am blindly), but i was in > hope that i can solve this with the DB. You can do this with a "before" trigger. IIRC, the data type is checked before the trigger is executed, so you would need to change the datatype to something allowing unlimited length: plain varchar (no length specification), or text. I agree with the other posters, though. This would be trivial to do in the application, and that is what I would choose. __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com
Not sure if this has been mentioned: any chance of doing it via a view? Caveat: not sure when any form of length checking will take place. Create a view, with an insert rule to perform the real insert into the underlying table, and truncate in the rule function. Regards! Ed On Fri, 16 May 2003, Bartlomiej Frackiewicz wrote: > Hi, > > thanks for answering. Sure Perl can (handmade, DBI do not support this, or i am blindly), but i was in hope that i cansolve this with the DB. > > Anyway, thanks. > > Bart > > -----Urspr�ngliche Nachricht----- > Von: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk] > Gesendet: Freitag, 16. Mai 2003 14:04 > An: Bartlomiej Frackiewicz > Cc: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] Rollback when value too long > > > On Fri, 16 May 2003, Bartlomiej Frackiewicz wrote: > > > Hi, > > > > we are using "PostgreSQL 7.3.1 on i386-pc-linux-gnu" with Perl DBI. > > > > Some tables where directly updated from web forms, so when the input > > is longer than the fileld, Postgres forces a rollback. This might be > > right, but is very angry for me. Is there a way to teach Postgres > > that it must truncate these values? > > No. Can't the perl code truncate them? > > > -- > Nigel Andrews > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
I'd do this with a stored procedure. Just hand it the whole row and let it trim the parts down to size you want trimmed down to size. NOT autotrimming content is one of the reasons Postgresql can lay claim to the C in ACID compliance. On Fri, 16 May 2003, Bartlomiej Frackiewicz wrote: > Hi, > > thanks for answering. Sure Perl can (handmade, DBI do not support this, or i am blindly), but i was in hope that i cansolve this with the DB. > > Anyway, thanks. > > Bart > > -----Ursprüngliche Nachricht----- > Von: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk] > Gesendet: Freitag, 16. Mai 2003 14:04 > An: Bartlomiej Frackiewicz > Cc: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] Rollback when value too long > > > On Fri, 16 May 2003, Bartlomiej Frackiewicz wrote: > > > Hi, > > > > we are using "PostgreSQL 7.3.1 on i386-pc-linux-gnu" with Perl DBI. > > > > Some tables where directly updated from web forms, so when the input > > is longer than the fileld, Postgres forces a rollback. This might be > > right, but is very angry for me. Is there a way to teach Postgres > > that it must truncate these values? > > No. Can't the perl code truncate them? > > > -- > Nigel Andrews > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >