PostgreSQL 8.1.0 RHEL / Debian incompatible packages

Hi,

On Wed, 7 Dec 2005, Richard van den Berg wrote:

I just tried to move my database files over from a CentOS 4.2 (RHEL AS 4
clone) machine to Debian 3.1. Unfortunately, the server complains:

2005-12-07 11:55:04 CET FATAL: database files are incompatible with server
2005-12-07 11:55:04 CET DETAIL: The database cluster was initialized
without HAVE_INT64_TIMESTAMP but the server was compiled with
HAVE_INT64_TIMESTAMP.
2005-12-07 11:55:04 CET HINT: It looks like you need to recompile or
initdb.

The message is clear enough, but I'm kinda disappointed because I am
using the official packages for both distribution:

Well, this is because Debian packages are built
with --enable-integer-datetimes. However we don't build the RPMs with
that. This has been proposed before AFAIR, but it has been rejected.

So you should dump/reload I think, or rebuild our SRPM
with --enable-integer-datetimes enabled.

Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Wed, Dec 07, 2005 at 12:14:05PM +0100, Richard van den Berg wrote:

I just tried to move my database files over from a CentOS 4.2 (RHEL AS 4
clone) machine to Debian 3.1. Unfortunately, the server complains:

2005-12-07 11:55:04 CET FATAL: database files are incompatible with server
2005-12-07 11:55:04 CET DETAIL: The database cluster was initialized
without HAVE_INT64_TIMESTAMP but the server was compiled with
HAVE_INT64_TIMESTAMP.
2005-12-07 11:55:04 CET HINT: It looks like you need to recompile or
initdb.

<snip>

Is there a good reason that the official RPM on postgresql.org is not
build with HAVE_INT64_TIMESTAMP ? It would have been so nice if this
would have worked. :-/

PostgreSQL does not guarentee any kind of portability of the datafiles
between major releases, different platforms and as you pointed out,
different compile options (including for example number of index keys,
blocksize). There are defaults but obviously they were compiled
differently. The exact option here is USE_INTEGER_DATETIMES.

Just be glad the server didn't startup and subsequently crash the first
time you read a timestamp value.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout wrote:

Just be glad the server didn't startup and subsequently crash the first
time you read a timestamp value.

I was pleasantly surprised that PostgreSQL gave such a detailed error
message. Even down to the compile option that was different, good job.

I'll just do a pg_dump / pg_restore cycle, as I was planning to do. Is
there any guarantee for compatability the pg_dump format? Say, can a
8.1.0 pg_dump made using a 64 bit Solaris SPARC binary be read using a
32 bit Intel Linux pg_restore 8.1.0 binary? ;-)

--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------

Hi,
On Wed, 2005-12-07 at 12:14 +0100, Richard van den Berg wrote:

I just tried to move my database files over from a CentOS 4.2 (RHEL AS 4
clone) machine to Debian 3.1. Unfortunately, the server complains:

2005-12-07 11:55:04 CET FATAL: database files are incompatible with server
2005-12-07 11:55:04 CET DETAIL: The database cluster was initialized
without HAVE_INT64_TIMESTAMP but the server was compiled with
HAVE_INT64_TIMESTAMP.
2005-12-07 11:55:04 CET HINT: It looks like you need to recompile or
initdb.

The message is clear enough, but I'm kinda disappointed because I am
using the official packages for both distribution:

Well, this is because Debian packages are built
with --enable-integer-datetimes. However we don't build the RPMs with
that. This has been proposed before AFAIR, but it has been rejected.

Also I want to remind that these packages are not "official". Debian
packages and RPMs are built by different people.

So you should dump/reload I think, or rebuild our SRPM
with --enable-integer-datetimes enabled.

Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Wed, Dec 07, 2005 at 02:15:29PM +0100, Richard van den Berg wrote:

I'll just do a pg_dump / pg_restore cycle, as I was planning to do. Is
there any guarantee for compatability the pg_dump format? Say, can a
8.1.0 pg_dump made using a 64 bit Solaris SPARC binary be read using a
32 bit Intel Linux pg_restore 8.1.0 binary? ;-)

That's definitly the intention. If you find otherwise, please report as
a bug.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Richard van den Berg <richard.vandenberg@trust-factory.com> writes:

Is there a good reason that the official RPM on postgresql.org is not
build with HAVE_INT64_TIMESTAMP ? It would have been so nice if this
would have worked. :-/

You've got that 100% backwards: you should be complaining to Debian that
it's not their business to editorialize on the default setting.

regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Richard van den Berg <richard.vandenberg@trust-factory.com> writes:

Is there a good reason that the official RPM on postgresql.org is not
build with HAVE_INT64_TIMESTAMP ? It would have been so nice if this
would have worked. :-/

You've got that 100% backwards: you should be complaining to Debian that
it's not their business to editorialize on the default setting.

The default setting sucks. You're advocating a position which would
mean more work for our users (since they'd have to recompile their own
packages to achieve a sane setting). No thanks.

Stephen

Tom Lane wrote:

You've got that 100% backwards: you should be complaining to Debian that
it's not their business to editorialize on the default setting.

I've forwarded this thread to Debian as a bug report. Their answer is
they will discuss this setting again when 8.2 comes out. The full answer
is here: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=342369

For the Debian bug track system, the full discussion on the postgresql
mailing list starts here:
http://archives.postgresql.org/pgsql-general/2005-12/msg00367.php

Thanks everyone for clearing this up.

Sincerely,

--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------

On Mon, Dec 12, 2005 at 09:41:47AM +0100, Richard van den Berg wrote:

Tom Lane wrote:

You've got that 100% backwards: you should be complaining to Debian that
it's not their business to editorialize on the default setting.

Actually it *is* the business of Debian maintainers to determine what
works best for our users over and above what upstream decide.

Otherwise you'd get stuff like /usr/libexec, etc.

If Tom could present an actual reason why it shouldn't be enabled, I'm sure
Martin (Pitt) would be interested. But Stephen Frost and Peter
Eisentraut as well as others seem to be suggesting that Debian default is
sane.

I've forwarded this thread to Debian as a bug report. Their answer is
they will discuss this setting again when 8.2 comes out. The full answer
is here: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=342369

The bug has been closed on Peter's advice.

Cheers,
Anand

--
`When any government, or any church for that matter, undertakes to say to
its subjects, "This you may not read, this you must not see, this you are
forbidden to know," the end result is tyranny and oppression no matter how
holy the motives' -- Robert A Heinlein, "If this goes on --"

On Tue, 13 Dec 2005, Anand Kumria wrote:

On Mon, Dec 12, 2005 at 09:41:47AM +0100, Richard van den Berg wrote:

Tom Lane wrote:

You've got that 100% backwards: you should be complaining to Debian that
it's not their business to editorialize on the default setting.

Actually it *is* the business of Debian maintainers to determine what
works best for our users over and above what upstream decide.

Otherwise you'd get stuff like /usr/libexec, etc.

If Tom could present an actual reason why it shouldn't be enabled, I'm sure
Martin (Pitt) would be interested. But Stephen Frost and Peter
Eisentraut as well as others seem to be suggesting that Debian default is
sane.

In and of itself it's a good option. However, choosing that option means
that Debian is saying that compatibility of data files with default
compiled PostgreSQL is not one of its primary concerns, which is a
reasonable statement, but it's still not the community's problem when
people can't move data to it.

* Stephan Szabo (sszabo@megazone.bigpanda.com) wrote:

On Tue, 13 Dec 2005, Anand Kumria wrote:

On Mon, Dec 12, 2005 at 09:41:47AM +0100, Richard van den Berg wrote:

Tom Lane wrote:

You've got that 100% backwards: you should be complaining to Debian that
it's not their business to editorialize on the default setting.

[...]

If Tom could present an actual reason why it shouldn't be enabled, I'm sure
Martin (Pitt) would be interested. But Stephen Frost and Peter
Eisentraut as well as others seem to be suggesting that Debian default is
sane.

I think what Peter said is probably correct- it defaults to double
because some compilers don't support 64bit integers, or it'd be lots
slower because the architecture doesn't support it and so there's alot
of overhead.

In and of itself it's a good option. However, choosing that option means
that Debian is saying that compatibility of data files with default
compiled PostgreSQL is not one of its primary concerns, which is a
reasonable statement, but it's still not the community's problem when
people can't move data to it.

Honestly, in the end I think the default should be changed. It could
fall-back to double with a warning (if it doesn't already) if the
compiler doesn't support 64bit integers. It sure seems like the general
feeling is that, given the choice, 64bit integer timestamps are
preferred. As the situations where you wouldn't want to use 64bit
integer timestamps are alot, lot smaller than the cases where you would
it's more sensible to have the default be to use them if possible.

Of course, another thought would be to have the rpms default to having
it enabled since the rpms provided on postgresql.org are for
architectures which should deal quite happily with 64bit integers (i686
and x86_64, though I don't actually see any rpms for x86_64, just the
directories, kind of odd). I'm reasonably confident that most rpm-based
architectures supported today support 64bit integers quite well, in
fact...

This really *should* be backwards, funny enough; Debian with support for
things like m68k (which doesn't have hardware 64bit integer support,
afaik) could be argued to use the default while rpm-based distros could
probably move to 64bit integer timestamps without any concerns over
using inefficient datatypes for the architectures those rpms would
likely be used on.

I don't think the Debian default should be changed though. If, say, an
m68k user actually complained about the default not being the right
option for them then I'd say we should consider having configure options
be different for those architectures and not that we should move
everyone to using doubles.

Thanks,

Stephen

Hi Stephen!

Stephen Frost [2005-12-13 11:06 -0500]:

Honestly, in the end I think the default should be changed. It could
fall-back to double with a warning (if it doesn't already) if the
compiler doesn't support 64bit integers.
[...]
I don't think the Debian default should be changed though. If, say, an
m68k user actually complained about the default not being the right
option for them then I'd say we should consider having configure options
be different for those architectures and not that we should move
everyone to using doubles.

I fully agree. (BTW, I doubt that double operations on m68k would be
any faster than integer ones...)

Thanks,

Martin

--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org

In a world without walls and fences, who needs Windows and Gates?

On Tue, 13 Dec 2005, Stephen Frost wrote:

* Stephan Szabo (sszabo@megazone.bigpanda.com) wrote:

In and of itself it's a good option. However, choosing that option means
that Debian is saying that compatibility of data files with default
compiled PostgreSQL is not one of its primary concerns, which is a
reasonable statement, but it's still not the community's problem when
people can't move data to it.

Honestly, in the end I think the default should be changed.

I agree, but that's pretty unrelated to the overall discussion as I see
it. The discussion is not really about whether integer datetime is a good
idea, but instead about packaging because this isn't necessarily the only
option ever going forward for which this could occur. Changing the
defaults might make this case go away, but it doesn't solve the underlying
issue that people have expectations that aren't necessarily true if the
packages (and source tree) don't make equivalent choices.

Martin Pitt <martin@piware.de> writes:

I fully agree. (BTW, I doubt that double operations on m68k would be
any faster than integer ones...)

Debatable at best --- most later 68k machines had hardware FPUs, but
none of them had any 64-bit-int instructions...

regards, tom lane