Обсуждение: Problems with pg_dump (on Debian i386)

Dear all,

since some version hops, pg_dump created dump files won't be restored
correctly anymore here. In a table I have an array which holds data in
form of "x.y" values, where x are numbers and y either numbers or
letters. An example for a field including such an array is
{"1.2"}. After pg_dumping the DB, all values are OK, but when trying
to restore it, all characters after the x-value (that is, including
the dot) has vanished.

The same thing happens when I try to export the table via COPY TO and
then import again with COPY FROM: the export file is OK, but the
import fails.

This used to work in earlier versions.

Hoping for help,
joachim

--          Dr. Joachim Trinkwitz              email: jtr@uni-bonn.de        Germanistisches Seminar             phone:
0228-737565
Rheinische Friedrich-Wilhelms-Universitaet   fax:   0228-737479     Am Hof 1d, 53113 Bonn, Germany

Joachim Trinkwitz <jtr@uni-bonn.de> writes:
> since some version hops, pg_dump created dump files won't be restored
> correctly anymore here. In a table I have an array which holds data in
> form of "x.y" values, where x are numbers and y either numbers or
> letters. An example for a field including such an array is
> {"1.2"}. After pg_dumping the DB, all values are OK, but when trying
> to restore it, all characters after the x-value (that is, including
> the dot) has vanished.

Could we see a specific example please --- preferably, enough of the
dump file to reproduce the problem?  Also, exactly *what* version hop
are we talking about?
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Could we see a specific example please --- preferably, enough of the
> dump file to reproduce the problem?  Also, exactly *what* version hop
> are we talking about?

Example file (extract of my dump file) is attached -- the misbehaviour
occurs even when simply \i-ing this file in psql.

Regarding version hops: I've followed Oliver Elphicks Debian
(unstable) releases except the RC test releases and now I'm at
7.1.1. Not sure when this occured, but with 6.xx it worked for sure.

Greetings and thanks for your help,
joachim

Joachim Trinkwitz <jtr@uni-bonn.de> writes:
> CREATE TABLE "lplan" (
>     ...
>     "art" character[] NOT NULL,

I believe that PG 7.1 interprets that field declaration as
"art" character(1)[] NOT NULL,

and then truncates your input to match.  7.2-to-be rejects the input
with complaints like
psql:germdb.sql:20: ERROR:  value too long for type character(1)
which is the SQL-specified behavior for char(n) fields.

Since you appear to be using different string lengths in your data,
I doubt char(n) is the right datatype to begin with.  I suggest
"art" varchar(n)[] NOT NULL,

for some appropriate n.
        regards, tom lane