Обсуждение: Truncation Problems

We are having problems with information we are inserting into our
database being truncated.  This information is being inserted via the
web through Apache 1.3.6 while using ColdFusion 4.0.1's ODBC interface
into a PostgreSQL 7.0.2 database.  It is being truncated in a couple of
ways.  Sometimes, after so many characters, the information is truncated
in each column.  Other times, the information for the last part of the
insertion string is lost.  These insertions usually contain a couple
paragraphs of information for each column.  We haven't had any problem
with short insertions.  Neither ColdFusion nor PostgreSQL return an
error on a truncation.  We occasionally will receive a "query string too
long" error from PostgreSQL, but no information is actually inserted
into the database.  The query string error is also confusing because
each column should be able to contain 1+GBs of information.

Has anyone had any similar problems?

Jeremy D Carlson <jeremy@gustavus.edu> writes:
> We are having problems with information we are inserting into our
> database being truncated.  This information is being inserted via the
> web through Apache 1.3.6 while using ColdFusion 4.0.1's ODBC interface
> into a PostgreSQL 7.0.2 database.  It is being truncated in a couple of
> ways.  Sometimes, after so many characters, the information is truncated
> in each column.  Other times, the information for the last part of the
> insertion string is lost.  These insertions usually contain a couple
> paragraphs of information for each column.  We haven't had any problem
> with short insertions.  Neither ColdFusion nor PostgreSQL return an
> error on a truncation.  We occasionally will receive a "query string too
> long" error from PostgreSQL, but no information is actually inserted
> into the database.  The query string error is also confusing because
> each column should be able to contain 1+GBs of information.

7.0.2 does not support "1+GBs" per column.  You need to update to 7.1.*
to have wide columns.

The error message "query string too long" is also suspicious, since that
string appears nowhere in either current or 7.0 sources.  I suspect you
are using obsolete client-side software (left over from 6.5 or before)
that still thinks Postgres has a query length limit.

            regards, tom lane

> -----Original Message-----
> From: Tom Lane
>
> Jeremy D Carlson <jeremy@gustavus.edu> writes:
> > We are having problems with information we are inserting into our
> > database being truncated.  This information is being inserted via the
> > web through Apache 1.3.6 while using ColdFusion 4.0.1's ODBC interface
> > into a PostgreSQL 7.0.2 database.  It is being truncated in a couple of
> > ways.  Sometimes, after so many characters, the information is truncated
> > in each column.  Other times, the information for the last part of the
> > insertion string is lost.  These insertions usually contain a couple
> > paragraphs of information for each column.  We haven't had any problem
> > with short insertions.  Neither ColdFusion nor PostgreSQL return an
> > error on a truncation.  We occasionally will receive a "query string too
> > long" error from PostgreSQL, but no information is actually inserted
> > into the database.  The query string error is also confusing because
> > each column should be able to contain 1+GBs of information.
>
> 7.0.2 does not support "1+GBs" per column.  You need to update to 7.1.*
> to have wide columns.
>
> The error message "query string too long" is also suspicious, since that
> string appears nowhere in either current or 7.0 sources.

The message seems to come from ODBC driver. It has the query length
limit 65536. The new driver which would be released in a week or so has
no query length limit.
As for truncation, there could be some causes.
1) PostgreSQL silently truncates the text.
2) Psqlodbc driver increases the text size by converting '\n' -> CR/LF
   and the buffer overflows. Unfortunately SQL(Extended)Fetch returns
   an SQL_SUCCESS_WITH_INFO not an SQL_ERROR in suc cases.

regards,
Hiroshi Inoue