Thread: varchar truncation from 7.1 to 7.2
I know that 7.2 started raising an error when a string is too long for a
varchar, whereas 7.1 silently truncated it.
My question is: why?
I read some previous posts about it, and the solution seemed to be a per-table
trigger to truncate the new value first (Thanks Jan).
Now, I don't think it's a problem if the behavior was always that way. If
every other database threw an error, that would also make sense (I am pretty
sure that db2 silently truncates).
However, it does seem to be a problem (albeit very minor) because it's (a) a
change from previous releases and (b) not always helpful.
If you send a query, and there is an obvious, sane, safe, predictable
way to make it work, I think that's the correct course of action. Moreover,
there really isn't a way for you to know that you've made an application
programming error until it's in production anyway (with the current behavior
or prior behavior), so I don't see how it helps you debug anything.
Am I missing a strong gain here? Again, this is a really minor issue. Overall
I'm really happy with 7.2.1 (which I just put on my production systems, in
case you're curious what prompted this question).
Thanks,
Jeff
The SQL standard required the change. --------------------------------------------------------------------------- Jeff Davis wrote: > I know that 7.2 started raising an error when a string is too long for a > varchar, whereas 7.1 silently truncated it. > > My question is: why? > > I read some previous posts about it, and the solution seemed to be a per-table > trigger to truncate the new value first (Thanks Jan). > > Now, I don't think it's a problem if the behavior was always that way. If > every other database threw an error, that would also make sense (I am pretty > sure that db2 silently truncates). > > However, it does seem to be a problem (albeit very minor) because it's (a) a > change from previous releases and (b) not always helpful. > > If you send a query, and there is an obvious, sane, safe, predictable > way to make it work, I think that's the correct course of action. Moreover, > there really isn't a way for you to know that you've made an application > programming error until it's in production anyway (with the current behavior > or prior behavior), so I don't see how it helps you debug anything. > > Am I missing a strong gain here? Again, this is a really minor issue. Overall > I'm really happy with 7.2.1 (which I just put on my production systems, in > case you're curious what prompted this question). > > Thanks, > Jeff > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Oh, I see. It's good to be standard.
As a related question, I tried making the trigger per Jan's suggestion that
would truncate the value before the insert. However, it appears that the
check is done prior to calling the trigger. I put a notice in the trigger and
it's not even displayed if the string is larger than the allowed value
(therefore, the trigger isn't even called before the insert is rejected).
Regards,
Jeff
On Thursday 01 August 2002 07:22 pm, Bruce Momjian wrote:
> The SQL standard required the change.
>
> ---------------------------------------------------------------------------
>
> Jeff Davis wrote:
> > I know that 7.2 started raising an error when a string is too long for a
> > varchar, whereas 7.1 silently truncated it.
> >
> > My question is: why?
> >
> > I read some previous posts about it, and the solution seemed to be a
> > per-table trigger to truncate the new value first (Thanks Jan).
> >
> > Now, I don't think it's a problem if the behavior was always that way. If
> > every other database threw an error, that would also make sense (I am
> > pretty sure that db2 silently truncates).
> >
> > However, it does seem to be a problem (albeit very minor) because it's
> > (a) a change from previous releases and (b) not always helpful.
> >
> > If you send a query, and there is an obvious, sane, safe, predictable
> > way to make it work, I think that's the correct course of action.
> > Moreover, there really isn't a way for you to know that you've made an
> > application programming error until it's in production anyway (with the
> > current behavior or prior behavior), so I don't see how it helps you
> > debug anything.
> >
> > Am I missing a strong gain here? Again, this is a really minor issue.
> > Overall I'm really happy with 7.2.1 (which I just put on my production
> > systems, in case you're curious what prompted this question).
> >
> > Thanks,
> > Jeff
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
pgman@candle.pha.pa.us wrote: > > The SQL standard required the change. Can you be more specific? Are we talking about SQL-99 or something beyond SQL-92? Of course PostgreSQL doesn't fully comply with that standard (whichever standard it is), so what criteria are used to determine what parts to comply with? Of course I would prefer selective compliance to pick features rather than restrictions. Sorry for the attitude here, but this change did cost me some time to fix applications which broke at 7.2 because of this, and I didn't enjoy that. I don't have access to a lot of different databases, but two I tried do silent truncation into CHAR and VARCHAR. I wonder what Oracle does.
I agree, I had the same last minute scramble to make apps work with the no longer silent truncation. Is it possible to have a flag in the config file to make the silent truncation enabled? Or how about someone writing a sample trigger that would chop fields before the insert/update so as to truncate the data before the actual insert aborts it? Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of ljb > Sent: Friday, August 02, 2002 8:39 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] varchar truncation from 7.1 to 7.2 > > > pgman@candle.pha.pa.us wrote: > > > > The SQL standard required the change. > > Can you be more specific? Are we talking about SQL-99 or > something beyond > SQL-92? Of course PostgreSQL doesn't fully comply with that standard > (whichever standard it is), so what criteria are used to > determine what > parts to comply with? Of course I would prefer selective > compliance to pick > features rather than restrictions. > > Sorry for the attitude here, but this change did cost me some > time to fix > applications which broke at 7.2 because of this, and I didn't > enjoy that. > I don't have access to a lot of different databases, but two > I tried do > silent truncation into CHAR and VARCHAR. I wonder what Oracle does. > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
ljb wrote:
>
> pgman@candle.pha.pa.us wrote:
> >
> > The SQL standard required the change.
>
> Can you be more specific? Are we talking about SQL-99 or something beyond
> SQL-92? Of course PostgreSQL doesn't fully comply with that standard
> (whichever standard it is), so what criteria are used to determine what
> parts to comply with? Of course I would prefer selective compliance to pick
> features rather than restrictions.
SQL92 and SQL99. The criteria we typically use to choose compliance are:
1) If it is a new feature, we try to comply. Unless it is just too
stupid to do so, or if it keeps us from doing other important things.
2) If it is an existing feature, we try to comply. Unless it is beyond
the scope of the standard, if it breaks other features, or if the
standard is just too stupid to be believed.
> Sorry for the attitude here, but this change did cost me some time to fix
> applications which broke at 7.2 because of this, and I didn't enjoy that.
> I don't have access to a lot of different databases, but two I tried do
> silent truncation into CHAR and VARCHAR. I wonder what Oracle does.
We had discussions about this, and the change was motivated by a
complaint that we did things differently from someone else (I think
Oracle). Standards compliance was the tie-breaker. Check the -hackers
archives for the discussion...
It is not out of the question to make this a configurable parameter in
the GUC style. Patches would probably have a good chance of being
accepted, but I'm just guessing on that.
- Thomas
ljb <lbayuk@mindspring.com> writes:
>> The SQL standard required the change.
> Can you be more specific?
SQL92 section 9.2 "Store assignment" says (concerning assigning value V
to object column T):
e) If the data type of T is variable-length character string and
the length in characters M of V is greater than the maximum
length in characters L of T, then,
Case:
i) If the rightmost M-L characters of V are all <space>s, then
the value of T is set to the first L characters of V and
the length in characters of T is set to L.
ii) If one or more of the rightmost M-L characters of V are
not <space>s, then an exception condition is raised: data
exception-string data, right truncation.
Similar statements are made in other contexts such as casting a value to
varchar.
Elsewhere "exception condition" is defined:
3.3.4 Use of terms
3.3.4.1 Exceptions
The phrase "an exception condition is raised:", followed by the
name of a condition, is used in General Rules and elsewhere to
indicate that the execution of a statement is unsuccessful, ap-
plication of General Rules, other than those of Subclause 12.3,
"<procedure>", and Subclause 20.1, "<direct SQL statement>", may
be terminated, diagnostic information is to be made available,
and execution of the statement is to have no effect on SQL-data or
schemas.
Looks pretty black-and-white to me. I don't much care for it either :-(
but the spec is not ambiguous.
regards, tom lane
On Fri, Aug 02, 2002 at 10:04:56PM -0400, terry@greatgulfhomes.com wrote: > I agree, I had the same last minute scramble to make apps work with the no > longer silent truncation. I think one area that could be improved is a clear listing of client-visible incompatibilities in the release notes (AFAIK we might have a 1-line 'Raise error on long varchar' line in HISTORY, but not a lot more than that). > Is it possible to have a flag in the config file to make the silent > truncation enabled? I'm not sure I see the point of this. Most of the people who would have been effected by this change have upgraded months ago, and presumambly fixed their applications. Also, the "silent truncation" behavior is *very bad* -- if PostgreSQL allows a transaction to commit without raising an error, it is guaranteeing that the content of the insertion has been stored successfully. Truncating the input without so much as a warning violates that. Finally, if your application doesn't have a legitimate constraint on the size of the field, you should probably use TEXT anyway. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Neil Conway wrote: > On Fri, Aug 02, 2002 at 10:04:56PM -0400, terry@greatgulfhomes.com wrote: > > I agree, I had the same last minute scramble to make apps work with the no > > longer silent truncation. > > I think one area that could be improved is a clear listing of > client-visible incompatibilities in the release notes (AFAIK we might > have a 1-line 'Raise error on long varchar' line in HISTORY, but not > a lot more than that). I see in 7.2 history: CHAR(), VARCHAR() now reject strings that are too long (Peter E) It should have been in the incompatibilities section at the top, though. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026