[OGo-Users] Migration of ogo

[Peter Brueckner]

On Thursday 03 April 2008, Helge Hess wrote:
At first thanks and please excuse me for grizzle.
My idea for upgrading a database application was
wrong. I thougth it wouldt be better to use the
new schema and the old data. Thats why i was
installing the new applicaten with empty schema
and insert the old data by name (pg_dump -aD).
> .. this always worked just fine for me, w/o any of the tweaks you are
> doing (takes ~1h). You need to load the dump, maybe perform UTF-8
> conversion, and add the schema additions (if you want, seems to be
> 100% compat).
The UTF-Conversion was not needed because of dump
(LATIN1) and starting ogo with PGCLIENTENCODING in
the provided start-script.
>
> If you have a database dump, why do you run 'database_setup_psql.sh'?
> Thats sounds VERY VERY wrong and probably explains 95% of the mess you
> experienced.
The run of database_setup... was 10 seconds.
Drop (nearly) empty DB+USER->create USER+DB+SCHEMA.
We wanted to have the 'correct' and empty schema.
>
> > There are some questions:
> > 1. Nobody uses the constraints? With the actual postgres
> > release the inherits-feature is not usable with constraints!
> > see: http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html
> >  paragraph: 5.8.1 caveats
The next mistake was to think about using the constraints file.
There were about 10 iterations in trying to get our data
constraint-conform.
>
> Nothing changed here from 7.4 AFAIK. The link just says that
> constraints are not automatically inherited to child tables (hence,
> you need to define them on the child tables as well).
That is not the problem. The problem is the foreign key in
the refering table. If you say=20
  "ADD constraint telephone2company foreign key(company_id) references
  company(company_id)"
you will not get any telephone into the db because there are no companies!
There are only persons, enterprises.... So i cant see a way for this
kind of foreign key until this is repaired in postgres.=20
So we removed all such keys (also to documents - same problem) from=20
the constraints-file. Funnily one comment in company_info shows
the problem but all other constrains are not commented. I attach
the constraints.sql file that works for us.

> Anyways, the pg-build-schema.psql.constraints indeed includes
> constraints which do not work. Anymore? Most likely never did!
> Possibly 7.4 allowed to define the CONSTRAINT but didn't actually
> check it (this was common behaviour in very early PGs). Eg:
> ---snip---
> ALTER TABLE company_value ADD CONSTRAINT
>     company_value2company FOREIGN KEY (company_id)
>      REFERENCES company(company_id);
> ---snap---
So a little comment in the file would be nice. f.e "Please dont use this fi=
le"
>
> > 2. Is there a regular way for upgrading?
>
> Not between unstable releases. Hence, no, because we only have one
> official stable release :-) And you didn't even use that (1.0.0), but
> a beta (1.0b..) :-)
We used 1.0b because in there was no release for a long time
and all our urgent bugs were fixed in this version about four years ago.
A good idea would be a repository for ogo on OpenSuse. It is
always a nightmare to find a working release or trunc for a specific
version. Why trunc? Often urgent fixes are only in trunc versions
as to hear here.
>
> But usually its just a dump/load/done. So far the DB schema has been
> superstable:
Thats why a complete dump and restore works. But if are
sometimes schema changes?
> > 4. We are working with OGO/Skyrix for years and are very
> > comfortable but every upgrade is like hell
>
> Ah, common. So far I'm not convinced that anything is inherently
> wrong. Why would you initialize a fresh database and then load a dump
> over it? This just doesn't make any sense, really.
At least a little check for correct object_model and table_versions
would be good.

=2D-=20
mit freundlichen Gr=FC=DFen / Kind regards
Peter Br=FCckner
=46ON +49=3D361-21240.12

Br=FCckner & Jarosch Ingenieurgesellschaft mbH
Germany, 99084 Erfurt, Nonnengasse 5a=20
=46ON +49=3D361-21240.0 FAX +49=3D361-21240.19
WEB <http://www.bj-ig.de> MAIL <mailto:info@bj-ig.de>
Sitz Erfurt Register HRB 3134 Amtsgericht Erfurt
Steuer 151/106/02585 UID DE150092958=20
Gesch=E4ftsf=FChrer Dipl.-Ing. Wolfgang Jarosch, Dipl.-Ing. Peter Br=FCckner