Thread: Truncation on restore

Truncation on restore

From
Andrew Kelly
Date:
Hi everybody,

forgive me if this is covered in an FAQ or something, I just
haven't been able to find it. This question might not even
belong on this list and I apologize if that's the case.


I was recently handed a CD containing a CVS checkout of more
than 26 thousand files and the dump of a production DB, and
told to duplicate the development environment.
I'm always up for a challenge, so of course I didn't hurt
anybody.

I seem already to be failing at trying to use the dump to
create the database, though, and I'm wondering where I should
start looking.

If I do a simple grep of the dump file, I count 265 table
creation statements. However, after restoring
(psql test -f dump.sql) there are only 240 tables in the
test database, and there were truncations and errors due
to them sent to the console during the restore.

It seems that many of the index names are being truncated
31 characters and this cascades to other errors.

I guess that my question in its most basic form is,
"What have I done wrong?".  A quick google didn't turn
up much other than apparently truncation at 31 chars is
common and generally never causes a problem. And yet,
my stuff takes a nose dive.

Any help out there?

The system where the dump was generated and the system
where it's trying to be restored are both RH 7.3 boxes,
driving 7.2.1 versions of PostgreSQL.

Thanks everybody.

Andy

Re: Truncation on restore

From
Tom Lane
Date:
Andrew Kelly <akelly@transparency.org> writes:
> It seems that many of the index names are being truncated
> 31 characters and this cascades to other errors.

31 was the standard name length limit in PG 7.2.  If you see longer
names in a dump from a 7.2 server, then that server is *not* standard
but was built with a custom value of NAMEDATALEN.  You will need to
build yours likewise.  I would recommend holding a gun to someone's
head till they tell you what other nonstandard build options were
used, too ;-)

            regards, tom lane

Re: Truncation on restore

From
Andrew Kelly
Date:

Tom Lane wrote:
>
> Andrew Kelly <akelly@transparency.org> writes:
> > It seems that many of the index names are being truncated
> > 31 characters and this cascades to other errors.
>
> 31 was the standard name length limit in PG 7.2.  If you see longer
> names in a dump from a 7.2 server, then that server is *not* standard
> but was built with a custom value of NAMEDATALEN.  You will need to
> build yours likewise.  I would recommend holding a gun to someone's
> head till they tell you what other nonstandard build options were
> used, too ;-)
>
>                         regards, tom lane


Oh Tom, if you only knew...

The difficulties I'm having getting information out of
these chuckle-heads is at a last stage prior to
open warfare. In fact, I'm flying to London on Monday
for a lousy 30 minute face-to-face.
Maybe seeing that some computer weenies like to train with
free weights while they mentally debug will put a little
"here you go, sir" on the table. I'll have a gun, though,
just in case...

Can I pass the value for NAMEDATALEN to the configure
sript (or use a spec file in an rpm build from src), or
do I have to make the change in postgres_ext.h?

And thanks for the help, I really appreciate it.

Andy

Re: Truncation on restore

From
Tom Lane
Date:
Andrew Kelly <akelly@transparency.org> writes:
> Can I pass the value for NAMEDATALEN to the configure
> sript (or use a spec file in an rpm build from src), or
> do I have to make the change in postgres_ext.h?

You have to hack postgres_ext.h before building.  Probably this can be
handled from an RPM spec file, but not being an RPM guy I dunno how.

            regards, tom lane

Re: Truncation on restore

From
greg@turnstep.com
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Can I pass the value for NAMEDATALEN to the configure
> sript (or use a spec file in an rpm build from src), or
> do I have to make the change in postgres_ext.h?

For what it's worth, a configurable option has been proposed
before, but rejected for client reasons. You probably have
to compile yourself, I doubt there is a rpm option.

http://archives.postgresql.org/pgsql-patches/2001-05/msg00137.php

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200312102048

-----BEGIN PGP SIGNATURE-----

iD8DBQE/180gvJuQZxSWSsgRAuXNAKDsyTNv7Cp1SIjcQdz3LE8d0WZK8gCgq23B
n1vsfG0zYsvA3R025HOxkYk=
=kSIs
-----END PGP SIGNATURE-----



Re: Truncation on restore

From
Andrew Kelly
Date:

greg@turnstep.com wrote:

[in response to my writing]
> > Can I pass the value for NAMEDATALEN to the configure
> > sript (or use a spec file in an rpm build from src), or
> > do I have to make the change in postgres_ext.h?
>
> For what it's worth, a configurable option has been proposed
> before, but rejected for client reasons. You probably have
> to compile yourself, I doubt there is a rpm option.
>
> http://archives.postgresql.org/pgsql-patches/2001-05/msg00137.php

Yeah, Tom Lane told me the deal a few days ago. Thanks for
the link to the patch discussion, it's good to have the
background.

I changed the value of NAMEDATALEN in a source package
for 7.2.1 and compiled a quick test server, and with that little
beastie the dump restored cleanly.
I wonder what else they've hacked at and kept quiet about.

By the way, what's the value of NAMEDATALEN in the
current release?

Andy

Re: Truncation on restore

From
greg@turnstep.com
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> By the way, what's the value of NAMEDATALEN in the
> current release?

The value of NAMEDATALEN switched from 32 to 64 in
release 7.3 of PostgreSQL. IIRC, the SQL standard encourages
128, but that probably won't happen as the default for
quite a while.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200312141444

-----BEGIN PGP SIGNATURE-----

iD8DBQE/3L3fvJuQZxSWSsgRAjtqAKDxcEtMx/I/x8iI0W7i4lomIMdqLgCgxIrG
XiaaC/ggGnUgB5Wml4d/S7M=
=TREp
-----END PGP SIGNATURE-----