Re: pg_upgrade project status - Mailing list pgsql-hackers

Tom Lane píše v st 28. 01. 2009 v 18:05 -0500:
> Robert Haas <robertmhaas@gmail.com> writes:
> > I really like this idea, assuming I understand it.  Basically, I think
> > you're proposing that we move the old system catalogs out of the way,
> > bootstrap a new catalog, and then using SQL (running inside a
> > standalone backend?)  to migrate data from the old catalog to the new
> > one.  That sounds really good to me.  Even a relatively complicated
> > catalog reorganization should be able to be handled this way without
> > too much work or a lot of special-purpose code.
> 
> Well, what it really means is that all the special-purpose conversion
> code is in SQL instead of C.  Which is sort of good as long as whatever
> transformation you have in mind can be done easily in SQL.  (Good luck
> if you need to control the OIDs of the inserted rows, for instance.
> And I *really* want to see Zdenek handle conversion of stored-rule query
> trees in SQL...)  But far more importantly, it doesn't fix the problem
> that you have to write conversion code in the first place.

OK, it was too optimistic to take only SQL. But all problem are should
be fixable with C procedure and storing plain ascii for rewrite rules
and reparse it again is not so big problem I guess. 

> The appeal of the pg_dump approach is that it will automatically handle
> everything that there exists a plain-SQL representation for, which is to
> say darn near everything.  We will need special purpose code to deal
> with the dropped-column and TOAST-oid issues, but that can probably be
> written in SQL if it makes anyone feel better to do so ;-).  The more
> important point is that once we're done with those two issues, we're
> done, and will stay done for the foreseeable future (at least with
> respect to catalog upgrades).

One one side I was convinced to select on read conversion, because it is
simple and self contained. And now when I try to do catalog upgrade to
same way, the another solution is preferred which needs many
unsystematic hacks into SQL syntax, pg_dump and so on. 

> I am not sure why everyone is so hot to create a conversion path that
> guarantees extra maintenance pain for every future catalog
> reorganization, when it would be no more complex to create one without
> such a burden.

It is not only about catalog. Currently you have FSM which works fine if
it is empty, but when somebody invent any new fork which will needs have
complete data, then you need to handle it separately anyway.

By main opinion to extend parser and add another complexity into already
complex part instead of writing store procedure for each affected
catalog which convert its data.

Both version will work and I think that maintenance pain will not be so
big problem. I think the amount of hot modified catalogs is not big.

Advantage of direct conversion are:
1) no pg_dump
2) no old database server
3) no shuffling with files/tablespaces
4) it should be relatively robust and easy to use

Thats what I can say now It needs more analysis.    Zdenek