Upgrading rant.

Lamar Owen <lamar.owen@wgcr.org> writes:

So I figured I'd roll a 7.1.3 RPMset for him to install onto Red Hat 8. It
was very bad. It simply would not build -- I guess it's the gcc 3
stuff.

If you don't know *exactly* why it doesn't build, I don't think you
should be blaming us for it. (FWIW, I just tried to build 7.1 branch
on RHL 8.0 --- the main problem seems to be that 7.1's configure isn't
expecting multiline output from "gcc --version", and it produces a
PG_VERSION_STR definition that's missing a trailing quote. There are
some other issues, none that look very difficult to fix, all indicative
of incompatible changes in either gcc or system include files.)

THIS DOESN'T HAPPEN WITH MySQL.

Oh? Do they have a crystal ball that lets them predict incompatible
future platform changes?

(I'm not very sure I believe your assertion above, anyway. We are
painfully aware of our own compatibility issues, but I wonder how many
people on this list pay close enough attention to MySQL to know what
their version-to-version compatibility track record *really* is.)

I thought the last time through would be the _last_ time through --
but I also thought I'd be able to build older PostgreSQL packages for
newer dists, which would prevent much of this problem.

You could do so if you were willing to track the platform changes.
7.1 isn't hopelessly broken for RHL 8.0, but it's definitely suffering
bit rot. Someone would have to expend effort on updating obsolete
branches, if we wanted them to keep working in the face of incompatible
platform changes.

And I'd love to see someone who has the time to do so (not me) grab
this bull by the horns and make it happen.

Well, this is exactly the issue: someone would have to put substantial
amounts of time into update mechanisms and/or maintenance of obsolete
versions, as opposed to features, performance improvements, or bug
fixes.

Personally, I feel that if we weren't working as hard as we could on
features/performance/bugfixes, the upgrade issue would be moot because
there wouldn't *be* any reason to upgrade. So I'm not planning to
redirect my priorities. But this is an open source project and every
developer gets to set their own priorities. If you can persuade someone
to put their time into that, go for it.

And I _know_ some are just going to fingerpoint and blame Red Hat. Any such
replies I will rather quickly redirect to /dev/null, as it isn't Red Hat's
fault we can't do a sane upgrade.

I think you're wasting your time trying to hold us to a higher standard
of backwards-compatibility than is maintained by the OSes and tools we
must sit on top of.

regards, tom lane

On Thu, Jan 02, 2003 at 07:26:06PM -0500, Tom Lane wrote:

Lamar Owen <lamar.owen@wgcr.org> writes:

replies I will rather quickly redirect to /dev/null, as it isn't Red Hat's
fault we can't do a sane upgrade.

I think you're wasting your time trying to hold us to a higher standard
of backwards-compatibility than is maintained by the OSes and tools we
must sit on top of.

Case in point: even though the Debian upgrade scripts have occasionally
given me a near-heart attack by claiming that they didn't succcessfully
upgrade when they did, I've never had this problem. Is this because
Oliver is smarter than you? Or Debian is 'superior'? No, it's because
_incremental upgradability_ is _the_ design goal for the Debian
distribution. Lots of other stuff may work better on RedHat (auto
hardware detection, etc.) but this is the design case for Debian, so
the facilities are mostly there for Oliver to use to do incremental,
rollbackable, upgrades.

What does that mean for PostgreSQL? Perhaps Tom's right: you can't fix
it in the program if the underlying system doesn't support it.

Ross

Tom Lane wrote:

Well, this is exactly the issue: someone would have to put substantial
amounts of time into update mechanisms and/or maintenance of obsolete
versions, as opposed to features, performance improvements, or bug
fixes.

Personally, I feel that if we weren't working as hard as we could on
features/performance/bugfixes, the upgrade issue would be moot because
there wouldn't *be* any reason to upgrade. So I'm not planning to
redirect my priorities. But this is an open source project and every
developer gets to set their own priorities. If you can persuade someone
to put their time into that, go for it.

Do not under estimate the upgrade issue. I think it is huge and a LOT of
people have problems with it. Personally, I never understood why the
dump/restore needed to happen in the first place.

Can't the data and index file format be more rigidly defined and stuck
too? Can't there just be some BKI process to add new data entries? I had
the same issues with 7.1 and 7.2,

On Fri, 2003-01-03 at 13:45, mlw wrote:

Tom Lane wrote:

Personally, I feel that if we weren't working as hard as we could on
features/performance/bugfixes, the upgrade issue would be moot because
there wouldn't *be* any reason to upgrade.

What about the standard Microsoft reason for upgrades - the bug fixes ;)

So I'm not planning to
redirect my priorities. But this is an open source project and every
developer gets to set their own priorities. If you can persuade someone
to put their time into that, go for it.

Do not under estimate the upgrade issue.

Very true! If upgrading is hard, users will surely expect us to keep
maintaining all non-upgradable old versions for the foreseeable future
;(

I think it is huge and a LOT of
people have problems with it. Personally, I never understood why the
dump/restore needed to happen in the first place.

Can't the data and index file format be more rigidly defined and stuck
too?

I don't think the main issues are with file _formats_ but rather with
system file structures - AFAIK it is a fundamental design decision
(arguably a design flaw ;( ) that we use system tables straight from
page cache via C structure pointers, even though there seems to be a
layer called "storage Manager" which should hide the on-disk format
completely.

Can't there just be some BKI process to add new data entries? I had
the same issues with 7.1 and 7.2,

--
Hannu Krosing <hannu@tm.ee>

Hannu Krosing wrote:

I don't think the main issues are with file _formats_ but rather with
system file structures - AFAIK it is a fundamental design decision
(arguably a design flaw ;( ) that we use system tables straight from
page cache via C structure pointers, even though there seems to be a
layer called "storage Manager" which should hide the on-disk format
completely.

I don't think that is a big issue, no one is saying the file format
should "change" or be used any differently, just that the structures be
more rigid, and anyone wishing to make a change, had better also have an
upgrade strategy.

Perhaps there could be a review/document phase for 7.4 where the
structures are cleaned up, checked, and perhaps have a couple "reserved"
entries added. (As governed by efficiecy) and have one last "speak now,
or forever hold your peace" and cast them in stone.

I think, as professional standards go, this is probably LONG over due.

On Thursday 02 January 2003 19:26, Tom Lane wrote:

Lamar Owen <lamar.owen@wgcr.org> writes:

So I figured I'd roll a 7.1.3 RPMset for him to install onto Red Hat 8.
It was very bad. It simply would not build -- I guess it's the gcc 3
stuff.

If you don't know *exactly* why it doesn't build, I don't think you
should be blaming us for it.

Who did I blame? (no one). I just made a statement -- which you pretty much
agreed with later (gcc --version being multiline IS a gcc 3 problem, no?
:-)). No blame at all. None of the message was a blame game of any kind,
although you seem to take it personally every time I bring up upgrading.
But, then again, I take it personally when someone e-mails me ranting (and
sometimes cussing) about my stupid program's not upgrading (and PostgreSQL
isn't 'my' program!).

THIS DOESN'T HAPPEN WITH MySQL.

Oh? Do they have a crystal ball that lets them predict incompatible
future platform changes?

No, they just allow for the old format, while making a new format. At least
that's the way it looks from reading the docs and a cursory install run. The
Linux Magazine article states it in a quite pointed way -- I'll grab that
magazine when I get back home and post a quote if you'd like.

(I'm not very sure I believe your assertion above, anyway. We are
painfully aware of our own compatibility issues, but I wonder how many
people on this list pay close enough attention to MySQL to know what
their version-to-version compatibility track record *really* is.)

I pay close enough attention that I was asked by a publisher to do a technical
review of a MySQL reference book.

And I'd love to see someone who has the time to do so (not me) grab
this bull by the horns and make it happen.

Well, this is exactly the issue: someone would have to put substantial
amounts of time into update mechanisms and/or maintenance of obsolete
versions, as opposed to features, performance improvements, or bug
fixes.

Fixing the upgrade 'bug' is a bugfix, IMHO. A _big_ bugfix.

Personally, I feel that if we weren't working as hard as we could on
features/performance/bugfixes, the upgrade issue would be moot because
there wouldn't *be* any reason to upgrade.

However, I'm sure there are people who hesitate to upgrade BECAUSE of the
difficulty, thereby causing them to miss features. And potentially bugfixes,
too.

So I'm not planning to
redirect my priorities. But this is an open source project and every
developer gets to set their own priorities. If you can persuade someone
to put their time into that, go for it.

Which is why I shake the branches periodically, to see if I can persuade
someone who can do this (in practice this means they either have a great deal
of free time, or they are paid fulltime to work on PostgreSQL).

I think you're wasting your time trying to hold us to a higher standard
of backwards-compatibility than is maintained by the OSes and tools we
must sit on top of.

I think PostgreSQL already sets a higher standard in many ways. Particularly
in the release cycle (we don't 'release early and release often').
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Hannu Krosing <hannu@tm.ee> writes:

I don't think the main issues are with file _formats_ but rather with
system file structures - AFAIK it is a fundamental design decision
(arguably a design flaw ;( ) that we use system tables straight from
page cache via C structure pointers,

The system tables are not the problem. pg_upgrade has shown how we
can have cross-version upgrades no matter how much the system catalogs
change (a good thing too, because we cannot freeze the system catalog
layout without bringing development to a standstill). A schema-only
dump and restore is cheap enough that there's no real reason to look
for any other solution.

Changes in the on-disk representation of user tables would be harder to
deal with, but they are also much rarer (AFAIR we've only done that
twice: WAL required additions to page and tuple headers, and then there
were Manfred's space-saving changes in 7.3). And as of 7.3 there is a
version field in page headers, which would in theory allow for a
page-at-a-time update process to work.

There isn't any fundamental reason why we cannot have a pg_upgrade
utility; claiming that there is something wrong with how we handle
catalog changes misses the point. The point is that *someone would
have to do the work*. Unless someone wants to step up and volunteer,
there's little value in discussing it.

regards, tom lane

On Friday 03 January 2003 15:16, Lamar Owen wrote:

On Thursday 02 January 2003 19:26, Tom Lane wrote:

Lamar Owen Wrote

THIS DOESN'T HAPPEN WITH MySQL.

Oh? Do they have a crystal ball that lets them predict incompatible
future platform changes?

No, they just allow for the old format, while making a new format. At
least that's the way it looks from reading the docs and a cursory install
run. The Linux Magazine article states it in a quite pointed way -- I'll
grab that magazine when I get back home and post a quote if you'd like.

Linux Magazine, January 2003, page 28. Article by Jeremy Zawodny.

Too long to quote directly. Relevant section is headed 'Compatability'. The
short of it: the on-disk format didn't change between MySQL 3.23 and MySQL
4.0, so you don't need to do a massive export and import process. You will,
however, need to run mysql_fix_privilege_tables to update the grant tables to
use the new privileges features in 4.0. Also, and I'll quote this one:
"Moving to MySQL 4.1 will require you to either dump and re-import your data
or run various ALTER commands to take advantage of its new features.
However, if you don't need those features, your existing tables will work
fine."

It's that last sentence that has me perturbed. It is just so obvious a
feature...
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Tom Lane wrote:

The system tables are not the problem. pg_upgrade has shown how we
can have cross-version upgrades no matter how much the system catalogs
change (a good thing too, because we cannot freeze the system catalog
layout without bringing development to a standstill). A schema-only
dump and restore is cheap enough that there's no real reason to look
for any other solution.

Changes in the on-disk representation of user tables would be harder to
deal with, but they are also much rarer (AFAIR we've only done that
twice: WAL required additions to page and tuple headers, and then there
were Manfred's space-saving changes in 7.3). And as of 7.3 there is a
version field in page headers, which would in theory allow for a
page-at-a-time update process to work.

There isn't any fundamental reason why we cannot have a pg_upgrade
utility; claiming that there is something wrong with how we handle
catalog changes misses the point. The point is that *someone would
have to do the work*. Unless someone wants to step up and volunteer,
there's little value in discussing it.

pg_upgrade does work, assuming there are no changes to the index or heap
file formats. (However, I now need to update it for schemas.) However,
the last time I worked on it for 7.2, no one was really interested in
testing it, so it never got done. In fact, there was a bug in the
handling of clog or wal files, but I didn't find out about it until long
after 7.2 because no one was using it.

Is pg_upgrade too hard to run? Is no one really interested in it?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
Sent: January 03, 2003 6:31 PM

Tom Lane wrote:

The system tables are not the problem. pg_upgrade has shown how we
can have cross-version upgrades no matter how much the system catalogs
change (a good thing too, because we cannot freeze the system catalog
layout without bringing development to a standstill). A schema-only
dump and restore is cheap enough that there's no real reason to look
for any other solution.

Changes in the on-disk representation of user tables would be harder to
deal with, but they are also much rarer (AFAIR we've only done that
twice: WAL required additions to page and tuple headers, and then there
were Manfred's space-saving changes in 7.3). And as of 7.3 there is a
version field in page headers, which would in theory allow for a
page-at-a-time update process to work.

There isn't any fundamental reason why we cannot have a pg_upgrade
utility; claiming that there is something wrong with how we handle
catalog changes misses the point. The point is that *someone would
have to do the work*. Unless someone wants to step up and volunteer,
there's little value in discussing it.

pg_upgrade does work, assuming there are no changes to the index or heap
file formats. (However, I now need to update it for schemas.) However,
the last time I worked on it for 7.2, no one was really interested in
testing it, so it never got done. In fact, there was a bug in the
handling of clog or wal files, but I didn't find out about it until long
after 7.2 because no one was using it.

I guess the main point here was to have co-existing versions of data
in the /data dierectory and the sever be able to the talk to all of them,
with an option to upgrade the data to the most recent format at some point.

If it's done that way, there won't be much of a pain if at all to upgrade
the server only and benifit from the general bug-fixes and general new features.
The specific features that require more recent data models, will have to
be tracked down, and dealt with if attempted for the older data model.

The other main point was to find smb to do the dirty job of implementing,
maintaining, and tracking down format changes of that concurrent data storage
"model".

pg_upgrade would be the tool to envetually upgrade the data to the
recent model in that case.

Is pg_upgrade too hard to run? Is no one really interested in it?

Would still be nice to have some GUI to it in Java/Web/*Admin/TclTk etc.
It pg_upgrade documented anywhere besided man pages and the script itself?

-s

Serguei Mokhov wrote:

pg_upgrade does work, assuming there are no changes to the index or heap
file formats. (However, I now need to update it for schemas.) However,
the last time I worked on it for 7.2, no one was really interested in
testing it, so it never got done. In fact, there was a bug in the
handling of clog or wal files, but I didn't find out about it until long
after 7.2 because no one was using it.

I guess the main point here was to have co-existing versions of data
in the /data dierectory and the sever be able to the talk to all of them,
with an option to upgrade the data to the most recent format at some point.

If it's done that way, there won't be much of a pain if at all to upgrade
the server only and benifit from the general bug-fixes and general new features.
The specific features that require more recent data models, will have to
be tracked down, and dealt with if attempted for the older data model.

We have to update the system tables for each release, and that is going
to require something like pg_upgrade. I don't see how we can avoid
that. We could enable reading of old heap/index file formats if we
tried hard, but that still requires pg_upgrade to update the system
catalogs.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

There isn't any fundamental reason why we cannot have a pg_upgrade
utility; claiming that there is something wrong with how we handle
catalog changes misses the point.

pg_upgrade does work, assuming there are no changes to the index or heap
file formats.

Does it really work? I had thought that there were some issues
associated with adjusting transaction numbers that couldn't be solved
by pg_upgrade in its present shell-script form; I was anticipating that
pg_upgrade would have to be rewritten as a C program so that it could
get at stuff at the necessary low level. I cannot recall the details
right at the moment though.

In fact, there was a bug in the
handling of clog or wal files, but I didn't find out about it until long
after 7.2 because no one was using it.

This may be what I was recalling. Did you find a bulletproof fix?

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

There isn't any fundamental reason why we cannot have a pg_upgrade
utility; claiming that there is something wrong with how we handle
catalog changes misses the point.

pg_upgrade does work, assuming there are no changes to the index or heap
file formats.

Does it really work? I had thought that there were some issues
associated with adjusting transaction numbers that couldn't be solved
by pg_upgrade in its present shell-script form; I was anticipating that
pg_upgrade would have to be rewritten as a C program so that it could
get at stuff at the necessary low level. I cannot recall the details
right at the moment though.

It just needs to be able to create an empty clog file, I think. You
actually gave me the steps, but weren't sure it would work, and it was
post-7.2 anyway, so I never did it. The issue is that once we increment
the transaction log counter, we need to create the proper clog file. I
didn't see in my testing because my testing db wasn't big enough.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Friday 03 January 2003 18:31, Bruce Momjian wrote:

Tom Lane wrote:

There isn't any fundamental reason why we cannot have a pg_upgrade
utility; claiming that there is something wrong with how we handle
catalog changes misses the point. The point is that *someone would
have to do the work*. Unless someone wants to step up and volunteer,
there's little value in discussing it.

pg_upgrade does work, assuming there are no changes to the index or heap
file formats. (However, I now need to update it for schemas.) However,
the last time I worked on it for 7.2, no one was really interested in
testing it, so it never got done. In fact, there was a bug in the
handling of clog or wal files, but I didn't find out about it until long
after 7.2 because no one was using it.

Is pg_upgrade too hard to run? Is no one really interested in it?

It has been considered 'experimental' in the past, Bruce. It needs more
credibility from the development group, as in 'We recommend you try to use
pg_upgrade (after making a backup), then attempt to do a dump/restore if
pg_upgrade doesn't work" (and pg_upgrade needs to be more robust in its
failure modes).

I am very interested in pg_upgrade, as I believe I mentioned the last go
through this topic. But it's the 'red-headed stepchild' utility here. (I'm
red-headed, my mother's red-headed, so no slight meant to those of fiery
folicles.) But it's also all or nothing -- you go the whole way through.

It's again our tremendous dependence upon the contents of the system catalogs
that does us in. That is one of our greatest strengths, until you have to
upgrade for some reason. Then it becomes our biggest liability.

And unlike Tom I think it is worthwhile to discuss it periodically, to remind
the group as a whole (which composition and membership changes frequently)
that there's a problem waiting to be solved.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

OK, taking up the pg_upgrade banner, I think there are two things
missing from the current code:

1) schema awareness -- easily fixed with some code
2) need to creat clog files to match incremented xid

I can do 1, and I think Tom can help me with 2. Then folks can test it
and see how it works. It is that last part that made me stop around
7.2, and 7.3 had a heap format change that guarantteed it wouldn't work.
Also, I think we make index format changes more frequently that Tom
recollects. Tom?

---------------------------------------------------------------------------

Lamar Owen wrote:

On Friday 03 January 2003 18:31, Bruce Momjian wrote:

Tom Lane wrote:

There isn't any fundamental reason why we cannot have a pg_upgrade
utility; claiming that there is something wrong with how we handle
catalog changes misses the point. The point is that *someone would
have to do the work*. Unless someone wants to step up and volunteer,
there's little value in discussing it.

pg_upgrade does work, assuming there are no changes to the index or heap
file formats. (However, I now need to update it for schemas.) However,
the last time I worked on it for 7.2, no one was really interested in
testing it, so it never got done. In fact, there was a bug in the
handling of clog or wal files, but I didn't find out about it until long
after 7.2 because no one was using it.

Is pg_upgrade too hard to run? Is no one really interested in it?

It has been considered 'experimental' in the past, Bruce. It needs more
credibility from the development group, as in 'We recommend you try to use
pg_upgrade (after making a backup), then attempt to do a dump/restore if
pg_upgrade doesn't work" (and pg_upgrade needs to be more robust in its
failure modes).

I am very interested in pg_upgrade, as I believe I mentioned the last go
through this topic. But it's the 'red-headed stepchild' utility here. (I'm
red-headed, my mother's red-headed, so no slight meant to those of fiery
folicles.) But it's also all or nothing -- you go the whole way through.

It's again our tremendous dependence upon the contents of the system catalogs
that does us in. That is one of our greatest strengths, until you have to
upgrade for some reason. Then it becomes our biggest liability.

And unlike Tom I think it is worthwhile to discuss it periodically, to remind
the group as a whole (which composition and membership changes frequently)
that there's a problem waiting to be solved.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, taking up the pg_upgrade banner, I think there are two things
missing from the current code:

1) schema awareness -- easily fixed with some code
2) need to creat clog files to match incremented xid

I can do 1, and I think Tom can help me with 2.

I was just now wondering whether we really need to do that at all.
We're already vacuuming the user tables before we bring 'em over.
What if we VACUUM FREEZE them instead? Then there are *no* xids of
interest in the tables being brought over, and no need to screw around
with the xid counter in the new installation. That in turn would mean
no need to mess with its pg_clog files. I think we'd still need to
advance the xlog position past the old installation's xlog end, but we
have the tool for that (pg_resetxlog) already.

Also, I think we make index format changes more frequently that Tom
recollects. Tom?

Oh? Name one... not that they'd be a critical problem anyway, as we
could easily reconstruct indexes via REINDEX rather than moving them
over, any time we made such a change.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is pg_upgrade too hard to run?

It is hard and dangerous to run (IMHO). One thing I would like to see
that would make it much safer to run is to recast it to operate through
standalone backends. That is, the idea is

bring down old postmaster

install new version

run new version's pg_upgrade
-- all work done without a postmaster

start new postmaster

It's much harder to get this wrong than it is to mess up with the
current situation (where pg_upgrade talks to live postmasters).
There isn't any simple way to lock *everyone* out of the DB and still
allow pg_upgrade to connect via the postmaster, and even if there were,
the DBA could too easily forget to do it.

This would require a nontrivial amount of work (notably, we'd have to
be able to get pg_dump to run against a standalone backend) but I don't
think I'd trust pg_upgrade as a production-grade tool until its
invocation method looks like the above.

regards, tom lane

On Sat, 2003-01-04 at 02:17, Tom Lane wrote:

There isn't any simple way to lock *everyone* out of the DB and still
allow pg_upgrade to connect via the postmaster, and even if there were,
the DBA could too easily forget to do it.

I tackled this issue in the Debian upgrade scripts.

I close the running postmaster and open a new postmaster using a
different port, so that normal connection attempts will fail because
there is no postmaster running on the normal port.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"But because of his great love for us, God, who is rich
in mercy, made us alive with Christ even when we were
dead in transgressions-it is by grace you have been
saved." Ephesians 2:4,5

On Fri, 3 Jan 2003, Bruce Momjian wrote:

Is pg_upgrade too hard to run? Is no one really interested in it?

All of my boxes are still on 7.2.3. Does that represent a viable test
base?