Having not seen anyone asking about the progress on the 7.3beta RPMset, I 
thought I would give a statement as to where things stand.

I am waiting the result of the pg_dump from 7.2.x to 7.3 restore discussion.  
The structure of the entire packaging depends upon knowing how the upgrade 
will be performed, since the rest of the packaging is just getting a good 
build, and excising the gborged clients, which will then have to have their 
own RPMs built.  But I'll get the core built first, then I'll work on the 
clients.

I have a basic build running, but it's not releasable.  I haven't had time to 
go through it with the properly fine-toothed comb that I want to as yet.  I 
would expect to be able to release an RPMset for beta 2 if that is a week or 
two off.

I'll try to keep everyone who cares updated periodically.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Tue, 17 Sep 2002 11:51:39 -0400
Lamar Owen <lamar.owen@wgcr.org>
RPMS for 7.3 beta.
Lamar Owen <lamar.owen@wgcr.org> writes:
> I have a basic build running, but it's not releasable.  I haven't had time to
> go through it with the properly fine-toothed comb that I want to as yet.  I 
> would expect to be able to release an RPMset for beta 2 if that is a week or 
> two off.

Sounds good.  I think the earliest we could be ready for beta2 is the
end of this week; sometime next week may be more realistic.

Given that we'll be forcing an initdb for beta2 anyway, those who use
RPMs may be just as happy to have missed beta1.

> I am waiting the result of the pg_dump from 7.2.x to 7.3 restore discussion.

Right.  We clearly have to support loading of 7.2 dumps; the only issue
in my mind is exactly how we kluge that up ;-).  I just talked to Bruce
about this a little bit, and we came to the conclusion that there are
two plausible-looking paths:

1. Relax CREATE LANGUAGE to accept either LANGUAGE_HANDLER or OPAQUE as
the datatype of the function (ie, make it work more like CREATE TRIGGER
does).

2. Hack CREATE LANGUAGE so that if it's pointed at an OPAQUE-returning
function, it actually updates the recorded return type of the function
in pg_proc to say LANGUAGE_HANDLER.

If we go with #1 we're more or less admitting that we have to support
OPAQUE forever, I think.  If we go with #2, then dumps out of 7.3 or
later would be OPAQUE-free, and we could eventually remove OPAQUE a few
release cycles down the road.  So even though #2 looks mighty ugly,
I am leaning in that direction.

Whichever way we jump, I think the same behavior should be adopted for
all three contexts where OPAQUE is relevant: language handlers,
triggers, and user-defined-datatype I/O functions.  Either we accept
OPAQUE forever, or we proactively fix the function declarations when
an old dump is loaded.

Another interesting thought is that if we do the OPAQUE-to-HANDLER
update thing, we could at the same time coerce the stored path for
the PL's shared library into the preferred '$libdir/foo' format,
rather than the absolute-path form it's likely to have if we're dealing
with a pre-7.2 dump.  This would not help anything immediately (if you
got past the CREATE FUNCTION then you gave a valid shlib path) but it'd
very possibly save people trouble down the road.

Comments?

			regards, tom lane

Tue, 17 Sep 2002 15:59:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: RPMS for 7.3 beta. 
On Tuesday 17 September 2002 03:59 pm, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > as yet.  I would expect to be able to release an RPMset for beta 2 if
> > that is a week or two off.

> Given that we'll be forcing an initdb for beta2 anyway, those who use
> RPMs may be just as happy to have missed beta1.

Hmmm. Any idea if any more initdb forcings are going to happen? :-)

> > I am waiting the result of the pg_dump from 7.2.x to 7.3 restore
> > discussion.

> Right.  We clearly have to support loading of 7.2 dumps; the only issue
> in my mind is exactly how we kluge that up ;-).  I just talked to Bruce
> about this a little bit, and we came to the conclusion that there are
> two plausible-looking paths:

> Comments?

 From a user/packager viewpoint: the exact mechanics on the internal level, 
while nice to know (so that I know what to look for in bug reports), are 
rather irrelevant when it comes to 'how do I package?'.   What I am looking 
at is whether the user will have to run 7.3's pg_dump in order to migrate 
older data.  If so I, and Oliver, will have to kludge up dependencies and 
linkages in ways that I'm not happy with, but can do if need be.  And 
migration is 'need be' if ever there were 'need be'.

I think that I will be able to just build a 'postgresql-olddump' package or 
similar that contains 7.3's pg_dump in a 7.2.2-friendly form, and let the 
various distributors worry about building that for older system libraries. 
:-)  This is just a possibility -- it may not be nearly as hard as I fear it 
will be -- best case is I do virtually nothing and let people upgrade the 
postgresql-libs and the main package (which includes pg_dump anyway), leaving 
the existing postgresql-server package in place.  They then dump, erase the 
old server package, and install the new server package.  I have disabled rpm 
upgrades for the server subpackage as of 7.2.2, so that portion I know is 
doable.  I'll just have to try it.  I may be overanalyzing the situation. :-)
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Tue, 17 Sep 2002 16:17:45 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: RPMS for 7.3 beta.
Lamar Owen <lamar.owen@wgcr.org> writes:
> ... What I am looking 
> at is whether the user will have to run 7.3's pg_dump in order to migrate 
> older data.

AFAIK this is not *necessary*, though it may be *helpful*.  Aside from
the OPAQUE issue, which we will fix one way or another, I am aware of
these issues for loading a pre-7.3 dump:

* A reloaded dump will fail to GRANT EXECUTE TO PUBLIC on functions,
  likewise fail to GRANT USAGE TO PUBLIC on procedural languages.
  This may not bother some people, but for those it does bother,
  it's not that hard to issue the GRANTs manually after loading the dump.

* A reloaded dump will not create dependencies between serial columns
  and sequence objects, nor between triggers and foreign key
  constraints, thus 7.3's nifty new support for DROP CONSTRAINT won't
  work, nor will dropping a table make its associated sequences go away.
  However, this can be boiled down to saying that it still works like it
  did before.

There are of course the same old same old issues regarding pg_dump's
ability to choose a good dump order, but these are not worse than before
either, and would bite you just as badly if you tried to reload your
dump into 7.2.*.

Using 7.3's pg_dump would help you with the GRANT issue, but AFAIR it
won't do anything for reconstructing serial or foreign-key dependencies.
And it definitely wouldn't help on the ordering issue.  So it's probably
not worth the trouble if you can't do it trivially, which you can't in
an RPM-upgrade context.  (We do advise it for people who are building
from source, since it's not difficult for them.)

In short, I'm not sure why you and Oliver are so unhappy.  We may not
have made the world better than before for upgrade scenarios, but I
don't think we've made it worse either.

			regards, tom lane

Tue, 17 Sep 2002 16:40:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: RPMS for 7.3 beta. 
> Using 7.3's pg_dump would help you with the GRANT issue, but AFAIR it
> won't do anything for reconstructing serial or foreign-key dependencies.

The below perl script can help with both of those.

http://www.rbt.ca/postgresql/upgrade/upgrade.tar.gz

Explanation URL:
http://www.rbt.ca/postgresql/upgrade.shtml


Doesn't deal with DEFERRED triggers.

-- 
  Rod Taylor


17 Sep 2002 16:56:12 -0400
Rod Taylor <rbt@rbt.ca>
Re: RPMS for 7.3 beta.
Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > ... What I am looking 
> > at is whether the user will have to run 7.3's pg_dump in order to migrate 
> > older data.
> 
> AFAIK this is not *necessary*, though it may be *helpful*.  Aside from
> the OPAQUE issue, which we will fix one way or another, I am aware of
> these issues for loading a pre-7.3 dump:
> 
> * A reloaded dump will fail to GRANT EXECUTE TO PUBLIC on functions,
>   likewise fail to GRANT USAGE TO PUBLIC on procedural languages.
>   This may not bother some people, but for those it does bother,
>   it's not that hard to issue the GRANTs manually after loading the dump.
> 
> * A reloaded dump will not create dependencies between serial columns
>   and sequence objects, nor between triggers and foreign key
>   constraints, thus 7.3's nifty new support for DROP CONSTRAINT won't
>   work, nor will dropping a table make its associated sequences go away.
>   However, this can be boiled down to saying that it still works like it
>   did before.

These seem like poor reasons for using 7.3 pg_dump on 7.2 databases. 
Item #1 can be easily fixed via an SQL command issued after the load, if
desired, and #2 is really not something specific to the RPM issue.  

We may be better writing a script that uses the names of the
triggers/sequences to create dependency information automatically.  Has
anyone looked at that?

-- 
  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

Tue, 17 Sep 2002 17:58:33 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RPMS for 7.3 beta.
> Sounds good.  I think the earliest we could be ready for beta2 is the
> end of this week; sometime next week may be more realistic.
>
> Given that we'll be forcing an initdb for beta2 anyway, those who use
> RPMs may be just as happy to have missed beta1.

If an initdb is planned - did that split->split_part or whatever change make
it in?

Chris


Wed, 18 Sep 2002 09:25:32 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: RPMS for 7.3 beta. 
> * A reloaded dump will not create dependencies between serial columns
>   and sequence objects, nor between triggers and foreign key
>   constraints, thus 7.3's nifty new support for DROP CONSTRAINT won't
>   work, nor will dropping a table make its associated sequences go away.
>   However, this can be boiled down to saying that it still works like it
>   did before.

Remember that Rod Taylor's written a script to fix at least the foreign key
issue above.  I think it'd be neat if that script were perfected and did
serials as well and then we could recommend its use...

Chris


Wed, 18 Sep 2002 09:27:42 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: RPMS for 7.3 beta. 
On Tuesday 17 September 2002 04:40 pm, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > ... What I am looking
> > at is whether the user will have to run 7.3's pg_dump in order to migrate
> > older data.

> AFAIK this is not *necessary*, though it may be *helpful*.  Aside from
> the OPAQUE issue, which we will fix one way or another, I am aware of
> these issues for loading a pre-7.3 dump:

Helpful is good.  If it proves not too hard I'm going to try that route.  And 
the more I think about the less difficult I think it will be.  I've about 
given up on the upgrade ever really being easy.

> In short, I'm not sure why you and Oliver are so unhappy.  We may not
> have made the world better than before for upgrade scenarios, but I
> don't think we've made it worse either.

It's a long-term pain, Tom.  With brief paroxysms worthy of appendicitis.

I've been caught by it -- I lost data due to bad RPM packaging coupled with 
the dump/restore cycle.  That's what motivated me to start doing this in the 
first place, three years ago.

I just want people to not get bit in a bad way and decide they don't want to 
use PostgreSQL after all.  And with the new features of 7.3, lots of users 
who might have begun with 7.2 are going to want to upgrade -- but if it's too 
painful....  Sorry, it's just a sore spot for me, this whole upgrade issue.  
I know Oliver has the same problem, with slightly different presentation.

I'm not meaning to be a pain; just trying to prevent some for someone else.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Tue, 17 Sep 2002 22:12:34 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: RPMS for 7.3 beta.
> I just want people to not get bit in a bad way and decide they
> don't want to
> use PostgreSQL after all.  And with the new features of 7.3, lots
> of users
> who might have begun with 7.2 are going to want to upgrade -- but
> if it's too
> painful....  Sorry, it's just a sore spot for me, this whole
> upgrade issue.
> I know Oliver has the same problem, with slightly different presentation.

IS there any solution to Postgres's upgrade problems?  I mean, ever?  With
the complex catalog design, etc - how is it every possible for us to do a
plug-n-play major version upgrade (assuming datafile format doesn't change
anymore)

How does pg_upgrade work?

Chris


Wed, 18 Sep 2002 10:27:10 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: RPMS for 7.3 beta.
Christopher Kings-Lynne wrote:
> > I just want people to not get bit in a bad way and decide they
> > don't want to
> > use PostgreSQL after all.  And with the new features of 7.3, lots
> > of users
> > who might have begun with 7.2 are going to want to upgrade -- but
> > if it's too
> > painful....  Sorry, it's just a sore spot for me, this whole
> > upgrade issue.
> > I know Oliver has the same problem, with slightly different presentation.
> 
> IS there any solution to Postgres's upgrade problems?  I mean, ever?  With
> the complex catalog design, etc - how is it every possible for us to do a
> plug-n-play major version upgrade (assuming datafile format doesn't change
> anymore)
> 
> How does pg_upgrade work?

pg_upgrade sort of worked for 7.2 but I got to it too late and I didn't
properly expand the pg_clog files.  In 7.3, the file format has changed.
If we don't change the format for 7.4, I can do it, but I have to add
schema stuff to it.  Shouldn't be too hard.

-- 
  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

Tue, 17 Sep 2002 22:53:41 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RPMS for 7.3 beta.
> > How does pg_upgrade work?
> 
> pg_upgrade sort of worked for 7.2 but I got to it too late and I didn't
> properly expand the pg_clog files.  In 7.3, the file format has changed.
> If we don't change the format for 7.4, I can do it, but I have to add
> schema stuff to it.  Shouldn't be too hard.

I mean - how does it actually _work_?

Chris


Wed, 18 Sep 2002 11:04:02 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: RPMS for 7.3 beta.
On Tuesday 17 September 2002 10:27 pm, Christopher Kings-Lynne wrote:
> Lamar Owen wrote:
> > Sorry, it's just a sore spot for me, this whole
> > upgrade issue.

> IS there any solution to Postgres's upgrade problems?  I mean, ever?  With
> the complex catalog design, etc - how is it every possible for us to do a
> plug-n-play major version upgrade (assuming datafile format doesn't change
> anymore)

While I should know better, I'm going to reply.....:-)

The system catalog has poor system-user separation.  Better separation might 
help the issue.  Putting all the stuff that belongs to system into the 
'system' catalog and then putting the user's customizations into a 'user' 
catalog, with a set of SQL scripts to upgrade the user portion if columns or 
other metadata changed in the user portion.  This statement is vastly 
simplified.  Then you can blow out the system portion and reinit it without 
disturbing the user data and metadata.  The problem I believe would be 
enforcing a strict enough demarcation to make that possible.  Then there's 
the nontrivial issue of where the point of demarcation lies.  But I should 
let someone better versed in the system catalog structure answer that.

<heresy>
I'd give up a few extensibility features for solid upgrading.  If I didn't 
have so much invested in PostgreSQL I might take a hard look at MySQL 4, 
since data migration has heretofore been one of their few real strengths.  
But I've got three years of RPM maintenance and five years of infrastructure 
built on PostgreSQL, so migrating to something else isn't a real palatable 
option at this point.
</heresy>

> How does pg_upgrade work?

If I am not mistaken pg_upgrade attempts to do just exactly what I described 
above, moving data tables and associated metadata out of the way, initdb, and 
move the data back, rebuiding the system catalog linkages into the user 
metadata as it goes. And it works in a state where there is mixed metadata.  
At least that's what I remember without looking at the source code to it -- 
the code is in contrib/pg_upgrade and is a shell script.  For laughs I have 
the source code in another window now, and it is rather involved, issuing a 
number of queries to gather the information to relink the user metadata back 
in.

It then vacuums so that losing the transaction log file (!!) isn't fatal to 
the upgrade.

It then stops postmaster and moves things out of the way, then an initdb is 
performed.  The schema is restored; the transaction statuses are restored, 
and data is moved back in, into the proper places.  Moving back into the 
proper places is nontrivial, and the existing code makes no attempt to 
rollback partial upgrades.  That failing could be fixed, however.

Then:
# Now that we have moved the WAL/transaction log files, vacuum again to
# mark install rows with fixed transaction ids to prevent problems on xid
# wraparound.

Like I said, it's involved.  I'm not sure it works for a 7.2.2-> 7.3 upgrade.  

If the on-disk binary format has changed, tough cookie.  It won't help us, 
since it doesn't make any effort to convert data -- it's just moving it 
around and recreating the metadata linkages necessary.

Now if a binary data converter could be paired with what pg_upgrade is 
currently doing, it might fly.  But scattered in the code is the discouraging 
comment:
# Check for version compatibility.
# This code will need to be updated/reviewed for each new PostgreSQL release.

Keeping abreast of the changing formats and the other 'gotchas' is just about 
going to be a full-time job, since changes are made to the system catalogs, 
syntax, semantics, and data format with little regard as to how it will 
impact data migration.  IOW, migration/upgrading shouldn't be an afterthought 
if it's going to work right.

I wish (in a somewhat wistful, yet futile manner) that each change was 
accompanied by data migration strategies for that change, but I'm not holding 
my breath, since the core developers have more important things to do.  (Not 
being sarcastic -- just observing a fact).

Oh well.  Chris, you got me wound up again... :-(  I wish I had the time and 
funding to go after it, but I have a full-time job already as a broadcast 
engineer, and while we use PostgreSQL in a mission critical role here, I 
can't justify diverting other monies for this purpose.  Money is tight enough 
already.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Tue, 17 Sep 2002 23:09:57 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: RPMS for 7.3 beta.

This is a better description that I could make. If you look at the
script it is very well commented so you should be able to see it works. 
Also, read the manual page first. 

In summary, doing any kind of data changes is quite involved (smaller
tuple header for 7.3) and because it has to be redone for every release,
it is quite a pain.  Also, considering commercial databases don't do
much better, I fell pretty OK about it.  However, we do make releases
more frequently than commercial folks, so the pain is more consistent.

MySQL hasn't changed their base table format in perhaps 10 years, so
yea, that is a real win for them.  Of course, they don't shoot out
features as fast as we do so that helps.  You could pretend you are
using MySQL and just not upgrade for 5 years.  ;-)

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

Lamar Owen wrote:
> On Tuesday 17 September 2002 10:27 pm, Christopher Kings-Lynne wrote:
> > Lamar Owen wrote:
> > > Sorry, it's just a sore spot for me, this whole
> > > upgrade issue.
> 
> > IS there any solution to Postgres's upgrade problems?  I mean, ever?  With
> > the complex catalog design, etc - how is it every possible for us to do a
> > plug-n-play major version upgrade (assuming datafile format doesn't change
> > anymore)
> 
> While I should know better, I'm going to reply.....:-)
> 
> The system catalog has poor system-user separation.  Better separation might 
> help the issue.  Putting all the stuff that belongs to system into the 
> 'system' catalog and then putting the user's customizations into a 'user' 
> catalog, with a set of SQL scripts to upgrade the user portion if columns or 
> other metadata changed in the user portion.  This statement is vastly 
> simplified.  Then you can blow out the system portion and reinit it without 
> disturbing the user data and metadata.  The problem I believe would be 
> enforcing a strict enough demarcation to make that possible.  Then there's 
> the nontrivial issue of where the point of demarcation lies.  But I should 
> let someone better versed in the system catalog structure answer that.
> 
> <heresy>
> I'd give up a few extensibility features for solid upgrading.  If I didn't 
> have so much invested in PostgreSQL I might take a hard look at MySQL 4, 
> since data migration has heretofore been one of their few real strengths.  
> But I've got three years of RPM maintenance and five years of infrastructure 
> built on PostgreSQL, so migrating to something else isn't a real palatable 
> option at this point.
> </heresy>
> 
> > How does pg_upgrade work?
> 
> If I am not mistaken pg_upgrade attempts to do just exactly what I described 
> above, moving data tables and associated metadata out of the way, initdb, and 
> move the data back, rebuiding the system catalog linkages into the user 
> metadata as it goes. And it works in a state where there is mixed metadata.  
> At least that's what I remember without looking at the source code to it -- 
> the code is in contrib/pg_upgrade and is a shell script.  For laughs I have 
> the source code in another window now, and it is rather involved, issuing a 
> number of queries to gather the information to relink the user metadata back 
> in.
> 
> It then vacuums so that losing the transaction log file (!!) isn't fatal to 
> the upgrade.
> 
> It then stops postmaster and moves things out of the way, then an initdb is 
> performed.  The schema is restored; the transaction statuses are restored, 
> and data is moved back in, into the proper places.  Moving back into the 
> proper places is nontrivial, and the existing code makes no attempt to 
> rollback partial upgrades.  That failing could be fixed, however.
> 
> Then:
> # Now that we have moved the WAL/transaction log files, vacuum again to
> # mark install rows with fixed transaction ids to prevent problems on xid
> # wraparound.
> 
> Like I said, it's involved.  I'm not sure it works for a 7.2.2-> 7.3 upgrade.  
> 
> If the on-disk binary format has changed, tough cookie.  It won't help us, 
> since it doesn't make any effort to convert data -- it's just moving it 
> around and recreating the metadata linkages necessary.
> 
> Now if a binary data converter could be paired with what pg_upgrade is 
> currently doing, it might fly.  But scattered in the code is the discouraging 
> comment:
> # Check for version compatibility.
> # This code will need to be updated/reviewed for each new PostgreSQL release.
> 
> Keeping abreast of the changing formats and the other 'gotchas' is just about 
> going to be a full-time job, since changes are made to the system catalogs, 
> syntax, semantics, and data format with little regard as to how it will 
> impact data migration.  IOW, migration/upgrading shouldn't be an afterthought 
> if it's going to work right.
> 
> I wish (in a somewhat wistful, yet futile manner) that each change was 
> accompanied by data migration strategies for that change, but I'm not holding 
> my breath, since the core developers have more important things to do.  (Not 
> being sarcastic -- just observing a fact).
> 
> Oh well.  Chris, you got me wound up again... :-(  I wish I had the time and 
> funding to go after it, but I have a full-time job already as a broadcast 
> engineer, and while we use PostgreSQL in a mission critical role here, I 
> can't justify diverting other monies for this purpose.  Money is tight enough 
> already.
> -- 
> Lamar Owen
> WGCR Internet Radio
> 1 Peter 4:11
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  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

Tue, 17 Sep 2002 23:22:17 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RPMS for 7.3 beta.
> <heresy>
> I'd give up a few extensibility features for solid upgrading.  If
> I didn't
> have so much invested in PostgreSQL I might take a hard look at MySQL 4,
> since data migration has heretofore been one of their few real
> strengths.
> But I've got three years of RPM maintenance and five years of
> infrastructure
> built on PostgreSQL, so migrating to something else isn't a real
> palatable
> option at this point.
> </heresy>

I do notice that I think MySQL requires you to run a script for some
upgrades...

Chris


Wed, 18 Sep 2002 11:37:17 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: RPMS for 7.3 beta.
On Tue, 2002-09-17 at 21:40, Tom Lane wrote:
> In short, I'm not sure why you and Oliver are so unhappy.  We may not
> have made the world better than before for upgrade scenarios, but I
> don't think we've made it worse either.

I'm unhappy because I know that I will get bug reports that I will have
to deal with.  They will take time and effort and would not be necessary
if we had a seamless upgrade path.  The more PostgreSQL gets used, the
more it will be used by 'clueless' users; they just install binary
packages and expect them to work.  That may currently be an unrealistic
expectation, but I would like it to become a goal of the project.  It
has always been my goal as Debian maintainer, but I don't think I can
achieve it for this release.

-- 
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
                 ========================================
     "Give, and it shall be given unto you; good measure, 
      pressed down, and shaken together, and running over, 
      shall men pour into your lap. For by your standard of 
      measure it will be measured to in return."
                                           Luke 6:38 


18 Sep 2002 04:44:42 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: RPMS for 7.3 beta.
Lamar Owen <lamar.owen@wgcr.org> writes:
>> How does pg_upgrade work?
> [ pretty good description ]

You missed a key point, which is that pg_upgrade does not even try to
cope with version-to-version system catalog changes.  It assumes it can
use pg_dump to dump and reload the database schema.  So there is no
hope, ever, that it will be more reliable than pg_dump.  All pg_upgrade
tries to do is short-circuit the moving of the bulk data.

The bald fact of the matter is that we are still a good ways away from
the point where we might be willing to freeze the system catalogs.  PG
is evolving and improving by a substantial amount with every release,
and the implication of that is that there *will* be some upgrade pain.
If you don't like that ... well ... you're welcome to keep using PG 6.1
... but I haven't got a better answer.

			regards, tom lane

Tue, 17 Sep 2002 23:51:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: RPMS for 7.3 beta. 
On Wed, 2002-09-18 at 04:22, Bruce Momjian wrote:
> 
> In summary, doing any kind of data changes is quite involved (smaller
> tuple header for 7.3) and because it has to be redone for every release,
> it is quite a pain. 

Is it feasible to make a utility to rewrite each table, shortening the
headers and making any other necessary changes?  (Taking for granted
that the database has been vacuumed and the postmaster shut down.)

This could build up over successive releases, with an input section
appropriate to each older version and an output section for the current
version.  Then an upgrade from any older version to the current one
could be done by pg_upgrade.

Is this even worth considering?  

-- 
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
                 ========================================
     "Give, and it shall be given unto you; good measure, 
      pressed down, and shaken together, and running over, 
      shall men pour into your lap. For by your standard of 
      measure it will be measured to in return."
                                           Luke 6:38 


18 Sep 2002 04:54:06 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: RPMS for 7.3 beta.
Oliver Elphick wrote:
> On Tue, 2002-09-17 at 21:40, Tom Lane wrote:
> > In short, I'm not sure why you and Oliver are so unhappy.  We may not
> > have made the world better than before for upgrade scenarios, but I
> > don't think we've made it worse either.
> 
> I'm unhappy because I know that I will get bug reports that I will have
> to deal with.  They will take time and effort and would not be necessary
> if we had a seamless upgrade path.

This last line gave me a chuckle.  It is like "software wouldn't be
necessary if computers could read people's minds".  :-)

The issue with modifying the data files is that if we have to modify the
large binary data file we may as well just dump/reload the data.  If we
don't change the on-disk format for 7.4 I will try again to make
pg_upgrade work.

-- 
  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

Wed, 18 Sep 2002 00:02:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RPMS for 7.3 beta.
On Tuesday 17 September 2002 11:22 pm, Bruce Momjian wrote:
> This is a better description tha[n] I could make. If you look at the
> script it is very well commented so you should be able to see it works.
> Also, read the manual page first.

I don't know how, but this time looking at the script, I just grokked it.  
Maybe that's because it finally clicked in my mind what was happening; 
regardless, thanks for the compliment;  feel free to use that, edited as 
necessary, in any documentation you might desire.

But you are certainly correct about the comments...some of which are more than 
a little tongue in cheek...
# Strip off the trailing directory name and store our data there
# in the hope we are in the same filesystem so 'mv 'works.

:-)

> However, we do make releases
> more frequently than commercial folks, so the pain is more consistent.

Well, for me and Oliver it comes in waves -- every major release has its 
paroxysm.  Then things cool off a little until next cycle.  These one year 
cycles have, in that way, been a good thing..... :-P

You know, if the featureset of the new releases wasn't so _seductive_ it 
wouldn't be nearly as big of a problem... 

> You could pretend you are
> using MySQL and just not upgrade for 5 years.  ;-)

Don't say that too loudly, or my production 6.5.3 database that backends the 
larger portion of my intranet will hear you....I'm just now moving the whole 
shooting match over to 7.2.2 as part of our delayed website redesign to use 
OpenACS.  That dataset started with 6.1.2 over five years ago, and it was the 
6.2.1->6.3.2 fiasco Red Hat created (by giving no warning that 5.1 had 6.3.2 
(5.0 had 6.2.1)) that got my dander up the first time.  I lost a few thousand 
records in that mess, which are now moot but then was a bad problem.  Since 
there wasn't an official Red Hat RPM for 6.1.2, that installation was from 
source and didn't get obliterated when I moved from Red Hat 4.2 to 5.0.  I 
was able to run both 6.1.2 and 6.2.1 concurrently, and the migration went 
smoothly -- but there were less than ten thousand records at that point.

So I _do_ have a three-year old database sitting there.  Rock solid except for 
one or two times of wierd vacuum/pg_dump interactions, solved by making them 
sequential.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Wed, 18 Sep 2002 00:10:09 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: RPMS for 7.3 beta.
On Tuesday 17 September 2002 11:51 pm, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> >> How does pg_upgrade work?
> > [ pretty good description ]

> You missed a key point, which is that pg_upgrade does not even try to
> cope with version-to-version system catalog changes.  It assumes it can
> use pg_dump to dump and reload the database schema.  So there is no
> hope, ever, that it will be more reliable than pg_dump.  All pg_upgrade
> tries to do is short-circuit the moving of the bulk data.

Yes, this is a key point and one that shouldn't be overlooked.  If the 
metadata belonging to the user's data didn't have to be pg_dumped, but was 
decoupled somewhat from the system metadata about types, operators, classes, 
and the like, the schema (great, another overloaded term) wouldn't need 
dumping but would travel with its data.

> The bald fact of the matter is that we are still a good ways away from
> the point where we might be willing to freeze the system catalogs.  

Not talking about a freeze.  Talking about separation of system/feature 
metadata from user metadata that wouldn't change in the upgrade anyway -- 
table names, fields, user types, views, triggers, etc, that belong to this 
database and not to the installation as a whole.  If columns need changed or 
added to the user data's metadata, have the upgrade script run the 
appropriate ALTER commands and UPDATES necessary.  The hard parts, I know, 
are the details behind the broad 'appropriate'.

> PG
> is evolving and improving by a substantial amount with every release,
> and the implication of that is that there *will* be some upgrade pain.

Why is it a given conclusion?  It should not be axiomatic that 'there *will* 
be upgrade pain if we improve our features.'  That's fatalistic.

We have innovative solutions in PostgreSQL that solve some pretty hairy 
problems.  WAL.  MVCC.  The subselect code (made my day when I heard about 
that one -- but then had to wait seven months before Red Hat saw fit to 
provide an RPM that I wasn't expecting.....the other reason I began RPM 
building, even though it was two cycles later before I got up the nerve to 
tackle it...).  The PL's. Foreign keys. TOAST (now that's a prime example of 
a 'sideways' solution to a head-on problem).

This is just a different challenge: how to keep the loosely dynamic system 
catalog structure while at the same time allowing the possibility of smooth 
data migration so people can more easily take advantage of the improved 
system catalog structure.  And yes I know that such a change is not for 7.3.  
Too late for that, and maybe too late for 7.4 too.

But unlike Bruce I winced at Oliver's last line -- it hit a little too close 
to home and to many multitudes of bug reports and nastygrams directed my way 
for something I have tried to kludge around in the past.  Yes, nastygrams, in 
the grand old alt.flame tradition.  When you maintain RPM's, you find 
yourself the point man for the entire project in some people's eyes.  The bug 
report about my RPM's trashing a fellow's RPM database was an extreme example 
of that.  I get two-three dozen e-mails a week that I redirect to the web 
site and/or the mailing lists.  I'm sure Oliver is nodding his head in 
understanding on this one.

I don't think seamless upgrading is a pipe dream.  And I think that dismissing 
it out of hand as 'impossible' is a self-fulfilling prophecy.

But I do think it won't work well if it's just tacked-on.

But, like Tom, I really don't have more of an answer than that.  I do 
understand pg_upgrade much better now, though.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Wed, 18 Sep 2002 00:36:36 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: RPMS for 7.3 beta.
Lamar Owen <lamar.owen@wgcr.org> writes:
> On Tuesday 17 September 2002 11:51 pm, Tom Lane wrote:
>> The bald fact of the matter is that we are still a good ways away from
>> the point where we might be willing to freeze the system catalogs.  

> Not talking about a freeze.  Talking about separation of system/feature 
> metadata from user metadata that wouldn't change in the upgrade anyway -- 

But the system catalogs *store* that metadata.  Whether the user's
metadata is changing or not in a high-level sense doesn't prove much
about what's happening to its low-level representation.

>> PG
>> is evolving and improving by a substantial amount with every release,
>> and the implication of that is that there *will* be some upgrade pain.

> Why is it a given conclusion?  It should not be axiomatic that 'there *will* 
> be upgrade pain if we improve our features.'  That's fatalistic.

I prefer "realistic" :-).  It is probably true that with an adequate
amount of effort directed towards upgrade issues we could make upgrading
less painful than it's usually been for PG.  (I didn't say "zero pain",
mind you, only "less pain".)  But where is that effort going to come
from?  None of the key developers care to spend their time that way;
all of us have other issues that we find more interesting/compelling/fun.
Unless someone of key-developer caliber comes along who *likes* spending
time on upgrade issues, it's not going to get better.  Sorry to be the
bearer of bad news, but that's reality as I see it.

			regards, tom lane

Wed, 18 Sep 2002 00:55:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: RPMS for 7.3 beta. 
On Wednesday 18 September 2002 12:55 am, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > Not talking about a freeze.  Talking about separation of system/feature
> > metadata from user metadata that wouldn't change in the upgrade anyway --

> But the system catalogs *store* that metadata.

They _currently_ store the user's metadata.  But that's my point -- does the 
user metadata that isn't typically substantially different after going 
through a dump/reload _have_ to coexist with the system data which is 
intrinsic to the basic backend operation?

Yes, I know I'm talking about refactoring/renormalizing the system catalogs.  
And I know that's neither interesting nor 'fun'.  And a major undertaking.

> from?  None of the key developers care to spend their time that way;
> all of us have other issues that we find more interesting/compelling/fun.
> Unless someone of key-developer caliber comes along who *likes* spending
> time on upgrade issues, it's not going to get better.  Sorry to be the
> bearer of bad news, but that's reality as I see it.

Quoting myself from my reply a couple of hours ago to Chris:
-> While I should know better, I'm going to reply.....:-)
[snip]
-> I wish (in a somewhat wistful, yet futile manner) that each change was 
-> accompanied by data migration strategies for that change, but I'm not 
-> holding my breath, since the core developers have more important things
-> to do.  (Not being sarcastic -- just observing a fact).

You're not telling me something I don't already know in your paragraph, Tom.  
Data migration of real users isn't interesting, compelling, or fun.  That's 
been made abundantly clear the last ten times the subject of upgrading has 
come up.  What's a real-world user to do?  Find it interesting, compelling, 
and fun to work around our shortcoming? (here comes one of those paroxysms 
that will keep me awake tonight....)

I for one am not doing _this_ because I find it to be 'fun'.  Quite the 
opposite -- you try to help people who end up cussing you out for something 
you can't control.  (And I see all those messages addressed to Tom coming 
through the lists, so I'm sure Tom is no stranger to this portion of the 
project, either)  

I'm doing _this_ to try to help people not go through what I went through, as 
well as to try to help the project in general, for both selfish and selfless 
reasons.  If I were able to spend enough time on the issue I am quite 
confident I could find a solution, in a year or so.  But I find it 
compelling, if nothing else, to put food on my kids' plates, which precludes 
me working much on this particular issue.  But I do what I can, if nothing 
else.

But it is _necessary_ to migrate data for one reason or another.  Lack of 
distributed backports for security patches, that are official releases, is 
one quite compelling reason to go through an upgrade.

Chris, this is why I was somewhat reticent to reply before.  I've been down 
this dead-end road before.  To distill Tom's comments:
It is technically feasible to make a better (not perfect) upgrade path, but 
nobody that can do it wants to.

What good is an interesting, compelling, fun, featureful, new version if 
nobody ugrades to it due to migration difficulties?  This release could be 
the harbinger of further difficulties, I fear.

So, that's why I'm unhappy, to answer a question asked quite a while back in 
the thread.  

Back on topic: I'll work towards using the 7.3 pg_dump unless the 7.2 dump can 
be easily restored.  Given the desireability for opaque to go away soon, if 
the 7.3 pg_dump Does The Right Thing and creates an opaque-free dump, that in 
itself is enough reason to go that route, as it helps the user create a 
nonambiguous data dump.  If it helps the user it is typically a Good Thing, 
and I am willing to put the effort into that.  And it may prove to not be 
that bad -- I'll know in a few days, hopefully.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Wed, 18 Sep 2002 01:46:54 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: RPMS for 7.3 beta.
On Wed, 2002-09-18 at 05:02, Bruce Momjian wrote:
> Oliver Elphick wrote:
> > I'm unhappy because I know that I will get bug reports that I will have
> > to deal with.  They will take time and effort and would not be necessary
> > if we had a seamless upgrade path.
> 
> This last line gave me a chuckle.  It is like "software wouldn't be
> necessary if computers could read people's minds".  :-)

Not really!  We know what the formats are before and after.  

We want PostgreSQL to be the best database.  Why on earth can we not
have the same ambition for the upgrade process?

-- 
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
                 ========================================
     "Give, and it shall be given unto you; good measure, 
      pressed down, and shaken together, and running over, 
      shall men pour into your lap. For by your standard of 
      measure it will be measured to in return."
                                           Luke 6:38 


18 Sep 2002 08:04:00 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: RPMS for 7.3 beta.
> Remember that Rod Taylor's written a script to fix at least the foreign key
> issue above.  I think it'd be neat if that script were perfected and did
> serials as well and then we could recommend its use...

It does do serials (adds pg_depend entry -- which is just enough), as
well as changes unique indexes into unique constraints.

As I had a few items I didn't want to upgrade, it asks the user if they
want to do each one (-Y to fix 'em all).

-- 
  Rod Taylor


18 Sep 2002 08:24:07 -0400
Rod Taylor <rbt@rbt.ca>
Re: RPMS for 7.3 beta.
Lamar Owen <lamar.owen@wgcr.org> writes:
> On Wednesday 18 September 2002 12:55 am, Tom Lane wrote:
>> But the system catalogs *store* that metadata.

> They _currently_ store the user's metadata.  But that's my point -- does the 
> user metadata that isn't typically substantially different after going 
> through a dump/reload _have_ to coexist with the system data which is 
> intrinsic to the basic backend operation?

I think we're talking at cross-purposes.  When I said we can't freeze
the system catalogs yet, I meant that we cannot freeze the format/schema
in which metadata is stored.  That affects both system and user entries.
You seem to be envisioning moving user metadata into a separate set of
tables from the predefined entries --- but that will help not one whit
as far as easing upgrades goes.

> Given the desireability for opaque to go away soon, if 
> the 7.3 pg_dump Does The Right Thing and creates an opaque-free dump,

The present proposal for that has the 7.3 backend patching things up
during reload; it won't matter whether you use 7.2 or 7.3 pg_dump to
dump from a 7.2 database.

> And it may prove to not be 
> that bad -- I'll know in a few days, hopefully.

If you find that it's not too painful then I do agree with doing it.
There will doubtless be future cycles where it's more valuable to be
able to use the up-to-date pg_dump than it is in this one.

			regards, tom lane

Wed, 18 Sep 2002 09:51:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: RPMS for 7.3 beta. 

I am working on a README and will add this to /contrib.  Thanks.

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

Rod Taylor wrote:
> > Using 7.3's pg_dump would help you with the GRANT issue, but AFAIR it
> > won't do anything for reconstructing serial or foreign-key dependencies.
> 
> The below perl script can help with both of those.
> 
> http://www.rbt.ca/postgresql/upgrade/upgrade.tar.gz
> 
> Explanation URL:
> http://www.rbt.ca/postgresql/upgrade.shtml
> 
> 
> Doesn't deal with DEFERRED triggers.
> 
> -- 
>   Rod Taylor
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  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

Wed, 18 Sep 2002 13:29:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RPMS for 7.3 beta.
Christopher Kings-Lynne wrote:
> > Sounds good.  I think the earliest we could be ready for beta2 is the
> > end of this week; sometime next week may be more realistic.
> >
> > Given that we'll be forcing an initdb for beta2 anyway, those who use
> > RPMs may be just as happy to have missed beta1.
> 
> If an initdb is planned - did that split->split_part or whatever change make
> it in?

Yes, it did, and in fact if you didn't initdb after the patch was
applied, you would see regression failures.

-- 
  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

Wed, 18 Sep 2002 15:17:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RPMS for 7.3 beta.
Oliver Elphick writes:

> We want PostgreSQL to be the best database.  Why on earth can we not
> have the same ambition for the upgrade process?

We do have that ambition.  We just don't have enough clues and time to
follow up on it.

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 18 Sep 2002 22:09:51 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: RPMS for 7.3 beta.

OK, I have added this script to /contrib/adddepend with an appropriate
README.  It seems to work quite well.  It does require Pg:DBD.  I will
add a mention of the script in the release notes.

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

Rod Taylor wrote:
> > Using 7.3's pg_dump would help you with the GRANT issue, but AFAIR it
> > won't do anything for reconstructing serial or foreign-key dependencies.
> 
> The below perl script can help with both of those.
> 
> http://www.rbt.ca/postgresql/upgrade/upgrade.tar.gz
> 
> Explanation URL:
> http://www.rbt.ca/postgresql/upgrade.shtml
> 
> 
> Doesn't deal with DEFERRED triggers.
> 
> -- 
>   Rod Taylor
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  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

Wed, 18 Sep 2002 16:39:33 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RPMS for 7.3 beta.
On Tue, 17 Sep 2002, Tom Lane wrote:

> > I am waiting the result of the pg_dump from 7.2.x to 7.3 restore discussion.
>
> Right.  We clearly have to support loading of 7.2 dumps; the only issue
> in my mind is exactly how we kluge that up ;-).  I just talked to Bruce
> about this a little bit, and we came to the conclusion that there are
> two plausible-looking paths:
>
> 1. Relax CREATE LANGUAGE to accept either LANGUAGE_HANDLER or OPAQUE as
> the datatype of the function (ie, make it work more like CREATE TRIGGER
> does).
>
> 2. Hack CREATE LANGUAGE so that if it's pointed at an OPAQUE-returning
> function, it actually updates the recorded return type of the function
> in pg_proc to say LANGUAGE_HANDLER.

Stupid question, but why not just create an upgrade script that does any
required translations external to the database?



Wed, 18 Sep 2002 22:21:13 -0300 (ADT)
"Marc G. Fournier" <scrappy@hub.org>
Restore from pre-v7.3 -> v7.3 (Was: Re: RPMS for 7.3 beta.)