Re: pg_upgrade and extra_float_digits

FYI, I test pg_upgrade by loading the old cluster's regression database
from a pg_dump output file, then after the upgrade, I dump the
regression database of the new cluster and diff the changes.

The problem I just encountered is that pg_dump uses
extra_float_digits=-3 for 9.0, while previous releases used '2'. I had
to do hack each server version to get a dump output that would match
without rounding errors --- it did eventually work and validated.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
> FYI, I test pg_upgrade by loading the old cluster's regression database
> from a pg_dump output file, then after the upgrade, I dump the
> regression database of the new cluster and diff the changes.
>
> The problem I just encountered is that pg_dump uses
> extra_float_digits=-3 for 9.0, while previous releases used '2'. I had
> to do hack each server version to get a dump output that would match
> without rounding errors --- it did eventually work and validated.
>
>

That sounds like a disaster waiting to happen. The server version is
going to affect much more than just this behaviour, surely. Wouldn't it
be better to provide a pg_dump option to provide the extra_float_digits
setting?

cheers

andrew

Andrew Dunstan wrote:
>
>
> Bruce Momjian wrote:
> > FYI, I test pg_upgrade by loading the old cluster's regression database
> > from a pg_dump output file, then after the upgrade, I dump the
> > regression database of the new cluster and diff the changes.
> >
> > The problem I just encountered is that pg_dump uses
> > extra_float_digits=-3 for 9.0, while previous releases used '2'. I had
> > to do hack each server version to get a dump output that would match
> > without rounding errors --- it did eventually work and validated.
> >
> >
>
> That sounds like a disaster waiting to happen. The server version is
> going to affect much more than just this behaviour, surely. Wouldn't it
> be better to provide a pg_dump option to provide the extra_float_digits
> setting?

FYI, you can't override it with PGOPTIONS because it is set inside the
pg_dump binary. I am not sure what you mean by your second sentence.

I was just reporting it in case anyone else was trying this for testing.
I doubt anyone else is going to try such a thing.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Bruce Momjian wrote:
>> FYI, I test pg_upgrade by loading the old cluster's regression database
>> from a pg_dump output file, then after the upgrade, I dump the
>> regression database of the new cluster and diff the changes.
>>
>> The problem I just encountered is that pg_dump uses
>> extra_float_digits=-3 for 9.0, while previous releases used '2'. I had
>> to do hack each server version to get a dump output that would match
>> without rounding errors --- it did eventually work and validated.

> That sounds like a disaster waiting to happen. The server version is
> going to affect much more than just this behaviour, surely. Wouldn't it
> be better to provide a pg_dump option to provide the extra_float_digits
> setting?

What disaster? That's only for test purposes, it has nothing to do with
actual data transfer.

regards, tom lane

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>> Bruce Momjian wrote:
>>
>>> FYI, I test pg_upgrade by loading the old cluster's regression database
>>> from a pg_dump output file, then after the upgrade, I dump the
>>> regression database of the new cluster and diff the changes.
>>>
>>> The problem I just encountered is that pg_dump uses
>>> extra_float_digits=-3 for 9.0, while previous releases used '2'. I had
>>> to do hack each server version to get a dump output that would match
>>> without rounding errors --- it did eventually work and validated.
>>>
>
>
>> That sounds like a disaster waiting to happen. The server version is
>> going to affect much more than just this behaviour, surely. Wouldn't it
>> be better to provide a pg_dump option to provide the extra_float_digits
>> setting?
>>
>
> What disaster? That's only for test purposes, it has nothing to do with
> actual data transfer.
>
>
>

Maybe I have misunderstood. How exactly is the server version being
hacked here? I know it's only for testing, but it still seems to me that
lying to a program as heavily version dependant as pg_dump is in general
a bad idea.

cheers

andrew

Andrew Dunstan wrote:
> >>> The problem I just encountered is that pg_dump uses
> >>> extra_float_digits=-3 for 9.0, while previous releases used '2'. I had
> >>> to do hack each server version to get a dump output that would match
> >>> without rounding errors --- it did eventually work and validated.
> >>>
> >
> >
> >> That sounds like a disaster waiting to happen. The server version is
> >> going to affect much more than just this behaviour, surely. Wouldn't it
> >> be better to provide a pg_dump option to provide the extra_float_digits
> >> setting?
> >>
> >
> > What disaster? That's only for test purposes, it has nothing to do with
> > actual data transfer.
> >
> >
> >
>
> Maybe I have misunderstood. How exactly is the server version being
> hacked here? I know it's only for testing, but it still seems to me that
> lying to a program as heavily version dependant as pg_dump is in general
> a bad idea.

The code in pg_dump 9.0 is:

/*
* If supported, set extra_float_digits so that we can dump float data
* exactly (given correctly implemented float I/O code, anyway)
*/
if (g_fout->remoteVersion >= 90000)
do_sql_command(g_conn, "SET extra_float_digits TO 3");
else if (g_fout->remoteVersion >= 70400)
--> do_sql_command(g_conn, "SET extra_float_digits TO 2");

The indicated line had to be changed to '3'. I did not change anything
else, and it was only done in my private CVS tree.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
>>
>> Maybe I have misunderstood. How exactly is the server version being
>> hacked here? I know it's only for testing, but it still seems to me that
>> lying to a program as heavily version dependant as pg_dump is in general
>> a bad idea.
>>
>
> The code in pg_dump 9.0 is:
>
> /*
> * If supported, set extra_float_digits so that we can dump float data
> * exactly (given correctly implemented float I/O code, anyway)
> */
> if (g_fout->remoteVersion >= 90000)
> do_sql_command(g_conn, "SET extra_float_digits TO 3");
> else if (g_fout->remoteVersion >= 70400)
> --> do_sql_command(g_conn, "SET extra_float_digits TO 2");
>
> The indicated line had to be changed to '3'. I did not change anything
> else, and it was only done in my private CVS tree.
>
>

Oh, I see. It is pg_dump that you hacked. That wasn't clear to me from
what you first said.

But do earlier server versions accept a value of 3? The 8.4 docs say
"The value can be set as high as 2".

cheers

andrew

Andrew Dunstan wrote:
>
>
> Bruce Momjian wrote:
> >>
> >> Maybe I have misunderstood. How exactly is the server version being
> >> hacked here? I know it's only for testing, but it still seems to me that
> >> lying to a program as heavily version dependant as pg_dump is in general
> >> a bad idea.
> >>
> >
> > The code in pg_dump 9.0 is:
> >
> > /*
> > * If supported, set extra_float_digits so that we can dump float data
> > * exactly (given correctly implemented float I/O code, anyway)
> > */
> > if (g_fout->remoteVersion >= 90000)
> > do_sql_command(g_conn, "SET extra_float_digits TO 3");
> > else if (g_fout->remoteVersion >= 70400)
> > --> do_sql_command(g_conn, "SET extra_float_digits TO 2");
> >
> > The indicated line had to be changed to '3'. I did not change anything
> > else, and it was only done in my private CVS tree.
> >
> >
>
> Oh, I see. It is pg_dump that you hacked. That wasn't clear to me from
> what you first said.
>
> But do earlier server versions accept a value of 3? The 8.4 docs say
> "The value can be set as high as 2".

That is the other thing I had to hack --- the 8.4 backend version had to
be changed to accept '3'. The good thing is this has to be done only
once --- once I have the dump file, I can use it in testing repeatedly
because 8.4 does not change.

Eventually the idea would be to have the build farm run such tests (with
a properly created dump file) so we can learn quickly if the backend
data format is changed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Andrew Dunstan wrote:
>> But do earlier server versions accept a value of 3? The 8.4 docs say
>> "The value can be set as high as 2".

> That is the other thing I had to hack --- the 8.4 backend version had to
> be changed to accept '3'. The good thing is this has to be done only
> once --- once I have the dump file, I can use it in testing repeatedly
> because 8.4 does not change.

> Eventually the idea would be to have the build farm run such tests (with
> a properly created dump file) so we can learn quickly if the backend
> data format is changed.

If we're thinking of doing that, it would be better to back-patch the
change that allowed '3'.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>
>> Andrew Dunstan wrote:
>>
>>> But do earlier server versions accept a value of 3? The 8.4 docs say
>>> "The value can be set as high as 2".
>>>
>
>
>> That is the other thing I had to hack --- the 8.4 backend version had to
>> be changed to accept '3'. The good thing is this has to be done only
>> once --- once I have the dump file, I can use it in testing repeatedly
>> because 8.4 does not change.
>>
>
>
>> Eventually the idea would be to have the build farm run such tests (with
>> a properly created dump file) so we can learn quickly if the backend
>> data format is changed.
>>
>
> If we're thinking of doing that, it would be better to back-patch the
> change that allowed '3'.
>
>
>

Yeah.

It's going to require some fancy dancing to get the buildfarm to do it.
Each buildfarm run is for a specific branch, and all the built artefacts
are normally thrown away. I'd have to work out a way of stashing the
binaries from a build on one branch for use in the pg_upgrade tests in
the run on another branch. It's doable but could get messy.

cheers

andrew

Andrew Dunstan wrote:
> >> Eventually the idea would be to have the build farm run such tests (with
> >> a properly created dump file) so we can learn quickly if the backend
> >> data format is changed.
> >>
> >
> > If we're thinking of doing that, it would be better to back-patch the
> > change that allowed '3'.
> >
> >
> >
>
> Yeah.
>
> It's going to require some fancy dancing to get the buildfarm to do it.
> Each buildfarm run is for a specific branch, and all the built artefacts
> are normally thrown away. I'd have to work out a way of stashing the
> binaries from a build on one branch for use in the pg_upgrade tests in
> the run on another branch. It's doable but could get messy.

Uh, that is not actually a problem. You just need to set
extra_float_digits=-3 to create the dump file, which is only done once
for each major version. You can _load_ that dump file into an
unmodified old cluster and test just fine. I will write up some
instructions in the next few days.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
> Andrew Dunstan wrote:
>
>>>> Eventually the idea would be to have the build farm run such tests (with
>>>> a properly created dump file) so we can learn quickly if the backend
>>>> data format is changed.
>>>>
>>>>
>>> If we're thinking of doing that, it would be better to back-patch the
>>> change that allowed '3'.
>>>
>>>
>>>
>>>
>> Yeah.
>>
>> It's going to require some fancy dancing to get the buildfarm to do it.
>> Each buildfarm run is for a specific branch, and all the built artefacts
>> are normally thrown away. I'd have to work out a way of stashing the
>> binaries from a build on one branch for use in the pg_upgrade tests in
>> the run on another branch. It's doable but could get messy.
>>
>
> Uh, that is not actually a problem. You just need to set
> extra_float_digits=-3 to create the dump file, which is only done once
> for each major version. You can _load_ that dump file into an
> unmodified old cluster and test just fine. I will write up some
> instructions in the next few days.
>
>

You are missing the point I was making. A buildfarm run does not
normally have available to it any binaries for a version other that the
one it is building. There is no notion of a multi-branch buildfarm run.
Each run is for a particular branch and is a separate miracle. So I'm
not concerned about the structure of the dump file but about what will
be used to load it into an old cluster during a buildfarm run.

cheers

andrew

Andrew Dunstan wrote:
> > Uh, that is not actually a problem. You just need to set
> > extra_float_digits=-3 to create the dump file, which is only done once
> > for each major version. You can _load_ that dump file into an
> > unmodified old cluster and test just fine. I will write up some
> > instructions in the next few days.
> >
> >
>
> You are missing the point I was making. A buildfarm run does not
> normally have available to it any binaries for a version other that the
> one it is building. There is no notion of a multi-branch buildfarm run.
> Each run is for a particular branch and is a separate miracle. So I'm
> not concerned about the structure of the dump file but about what will
> be used to load it into an old cluster during a buildfarm run.

Thank you. I understand now.

Imagine finding out on the build farm right away when we break binary
compatibility --- that would be cool. However, that might be overkill.
My testing seems to be working just fine. In fact the only diff I see
is:

< CREATE PROCEDURAL LANGUAGE plpgsql;
---
> CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;

and that is a known change. I might end up adding my regression dump
files to our ftp site (400k for each major version), and just having
people use them for testing.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
> Thank you. I understand now.
>
> Imagine finding out on the build farm right away when we break binary
> compatibility --- that would be cool.
>

I'm not saying we can't do that, just that it will not be a trivial
change. And yes it would be cool, although I hope we would know before
we committed such a change that that would be the outcome.

cheers

andrew

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Andrew Dunstan wrote:
>> It's going to require some fancy dancing to get the buildfarm to do it.
>> Each buildfarm run is for a specific branch, and all the built artefacts
>> are normally thrown away.

> Uh, that is not actually a problem. You just need to set
> extra_float_digits=-3 to create the dump file, which is only done once
> for each major version.

Wrong. In the first place, we're not going to start carrying something
as large as a pg_dump of the regression database as part of the source
code for the buildfarm. Even if we wanted to, it wouldn't work because
the results aren't platform-independent --- there are float differences
and probably row ordering differences to worry about. In the second
place, it won't "only be done once", unless you imagine that we never
change the regression tests for back branches; a casual perusal of the
CVS logs will disprove that idea.

The only thing that's really going to work here is to generate the dump
on the fly.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>
>> Andrew Dunstan wrote:
>>
>>> It's going to require some fancy dancing to get the buildfarm to do it.
>>> Each buildfarm run is for a specific branch, and all the built artefacts
>>> are normally thrown away.
>>>
>
>
>> Uh, that is not actually a problem. You just need to set
>> extra_float_digits=-3 to create the dump file, which is only done once
>> for each major version.
>>
>
> Wrong. In the first place, we're not going to start carrying something
> as large as a pg_dump of the regression database as part of the source
> code for the buildfarm. Even if we wanted to, it wouldn't work because
> the results aren't platform-independent --- there are float differences
> and probably row ordering differences to worry about. In the second
> place, it won't "only be done once", unless you imagine that we never
> change the regression tests for back branches; a casual perusal of the
> CVS logs will disprove that idea.
>
> The only thing that's really going to work here is to generate the dump
> on the fly.
>
>
>

This whole discussion leads me to the conclusion that we need to look
more imaginatively at our testing regime. When the buildfarm was created
it (via pg_regress) covered a lot of what we needed to test, but that is
becoming less and less true. Not only does pg_upgrade need testing but
we need to devise some sort of automated testing regime for SR and HS,
among other things. pg_regress is showing it's age a bit, I think.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> This whole discussion leads me to the conclusion that we need to look
> more imaginatively at our testing regime. When the buildfarm was created
> it (via pg_regress) covered a lot of what we needed to test, but that is
> becoming less and less true. Not only does pg_upgrade need testing but
> we need to devise some sort of automated testing regime for SR and HS,
> among other things. pg_regress is showing it's age a bit, I think.

The regression tests have never pretended to test more than a fraction
of what might be interesting to test. Crash recovery, in particular,
has always been interesting and has never been tested in any mechanized
way. They don't really exercise concurrent behavior in any meaningful
way either. I don't think they're "showing their age" so much as we're
starting to get more ambitious about what we would like to have routine
testing for.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Andrew Dunstan wrote:
> >> It's going to require some fancy dancing to get the buildfarm to do it.
> >> Each buildfarm run is for a specific branch, and all the built artefacts
> >> are normally thrown away.
>
> > Uh, that is not actually a problem. You just need to set
> > extra_float_digits=-3 to create the dump file, which is only done once
> > for each major version.
>
> Wrong. In the first place, we're not going to start carrying something
> as large as a pg_dump of the regression database as part of the source
> code for the buildfarm. Even if we wanted to, it wouldn't work because
> the results aren't platform-independent --- there are float differences
> and probably row ordering differences to worry about. In the second

Oh, yea.

> place, it won't "only be done once", unless you imagine that we never
> change the regression tests for back branches; a casual perusal of the
> CVS logs will disprove that idea.

Well, it doesn't have to match the regression test output exactly --- it
just has to be a valid sample. I never run the regression tests as part
of my testing --- I only load my fixed pg_dump output into the old
database and dump them from the new, and diff.

> The only thing that's really going to work here is to generate the dump
> on the fly.

Well, to do it on the fly, you need to:

use $libdir for regression .so files, not absolute paths
change CREATE OR REPLACE LANGUAGE to simple CREAtE for 8.4
run it twice to fix inheritance COPY column ordering
deal with extra_float_digits

That sounds tricky.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
> Well, to do it on the fly, you need to:
>
> use $libdir for regression .so files, not absolute paths
> change CREATE OR REPLACE LANGUAGE to simple CREAtE for 8.4
> run it twice to fix inheritance COPY column ordering
> deal with extra_float_digits
>
> That sounds tricky.

I have added the attached file to CVS to explain the proper pg_upgrade
testing method.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com