Re: Table modification

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr]
> Sent: 02 October 2001 13:26
> To: pgadmin-hackers(at)postgresql(dot)org
> Subject: Re: [pgadmin-hackers] Table modification
>
>
>
> >That said, if you look at the new revision control code, that may
> >provide some of the answers (note that it's not all in CVS
> yet as I'm
> >working on it at the moment).
>
> Yes, I noticed you were working on revision control code.
> Revision control is a great tool to perform upgrades. I doubt
> this is a reliable tool for
> rebuilding.

No, I didn't say it was. But it does provide some indication of when the
current object in the rcs doesn't match the real version of that object.

> I do not agree. The one and only way to solve all
> circular/dependency/PosgreSQL questions is to read/write
> information in development tables.

That will not solve a circular dependency.

> I would swear that even Oracle is not modifying code directly in the
> database schema.
> Oracle has got the same dependency problems. i.e. Code is stored in
> separate tables.
>
> >Then you'll need to completely rewrite pgSchema to work that
> way (which
> >I'm not about to do). For months pgSchema has been developed in an
> >interpretive way (which you were well aware of) - I'm not about to
> >throw all those months of work down the drain now. I think it is a
> >little late to be saying that this is not the way it should work.
>
> We have been doing it for a long time in pgAdmin I.
> This is the way developers work when writing queries in text
> documents.
>
> -> Object.CodeRepository = 'production' or 'development'. Object.Move
> -> (fromRepository, TargetRepository) will move code and/or
> compile it (like in pgAdmin I).
>
> Objects shall be Table, View, Function, Trigger, etc...
> pgSchema does not need much modification to handle 'production'.

Fine if you think it can be done. Propose a way of doing it. Bear in mind
that most people (myself included) don't work with such large databases and
prefer to work as pgAdmin currently does, so that should not be prevented
(in fact should probably be the default).

> > > The way that most PL/pgSQL developers handle this problem is that
> > > they write functions, views and triggers in a single text file
> > > with DROP/CREATE
> > > stuff. This works for less than 10 objects. Everyone stores
> > > source code in
> > > a separate place. pgAdmin I solution is interesting
> because it stores
> > > source code in the database itself. This is not a new
> > > concept. If we try to
> > > mix source/compiled code, we are going in a wrong direction.
> >
> >If pgAdmin II stored objects as source code then it would
> still need to
> >interpret object design by parsing SQL queries back into values and
> >options. This is significantly more difficult than interpreting the
> >data from the system catalogs.
>
> Not necessarily. If people want to access development tables
> directly, they
> can do it.
> For example, if phpPgAdmin writes in development tables, code
> will still
> compile.
>
> No need to parse SQL queries when using Object.Move (from->
> to) ilke in
> pgAdmin I.

OK, like I said above - what would you want to change/add to
pgSchema/pgAdmin? As a starting point, I would want pgAdmin to display as it
does now, but if the database is in dev mode, each object has an extra
property (like the Revision Control one that's now there) that indicates the
objects 'live' status. Other than that it should look pretty much the same.

> >The way it works presently is the most straight forward in
> my opinion.
> >The SQL reverse engineering can be tested using the
> Publishing Wizard
> >to ensure it's working correctly. An audit trail can be
> generated from
> >the Revision Control log.
>
> I agree this is the most straight forward, but does it suit
> professional needs?
>
> As for me, I still stick to pgAdmin I CVS version because my
> whole business
> process is based on PL/pgSQL.

pgAdmin I *will not* work with PostgreSQL 7.2. Bear that in mind if you look
to upgrade.

> I only trust dependency queries because they are real,
> whereas a revision
> log is only based on user input.

Where else would the input come from if not the user?

The logs are more reliable than trying to figure out what's dependent on
what. This is true because the logfiles represent the actual chronological
sequence of object development. Therefore, following this *must* create
dependencies in the correct order.

Another idea to consider: The Publishing Wizard. I believe this provides
roughly the same facilities but in a much safer environment:

When intending to use the publishing Wizard, you will build your PL/pgSQL
code in a development database. This is safer than building in development
tables because it uses the actual database to store items so you will
instantly become aware of any problems building objects, and more
importantly, you have no developers with hair-delete keys anywhere near your
production systems.

Once you are happy that your 'staging' database is correct, you create
another test database, into which you publish the staging database. Further
checking should prove that the test database is correct (if not then a
pgAdmin bug report would be appropriate).

Now that you've successfully completed a test publication of your new code,
you can publish to your production database(s).

By using a process like this, you:

A) develop on non-production systems without risking the live or production
systems (and therefore your or your shareholders profit).
B) prove your database before going live.
C) can publish to many production systems.

This system has already saved one of my staff from having to re-key a 200
row table just this week (and it's only Tuesday lunchtime now)!

This is a different working methodology, but it does give the same results
with low risk. Thoughts or comments welcomed of course...

Regards, Dave.