Re: Table modification

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr]
> Sent: 02 October 2001 15:51
> To: pgadmin-hackers(at)postgresql(dot)org
> Subject: Re: [pgadmin-hackers] Table modification
>
>
>
> >1) I don't want to retrieve source from logs. I want to analyse the
> >database and generate the source from what is known to work
> and compile
> >correctly. There is no better test for an object than to build it in
> >PostgreSQL.
>
> When an object is broken, its source code disappears or
> cannot be retrieved
> because PostgreSQL internals are based on OIDs.
> Example: if you create a view based on a function and drop
> the function,
> you will not be able to read the view source code.

Yes. So you use Revision Controls Rollback option (planned, but not yet
implemented) to undelete the object if it wasn't supposed to be dropped,
otherwise it doesn't matter because you obviously don't want to publish it
anyway.

> When several developers are working on the same database at
> the same time,
> you cannot guarantee consistency at PostgreSQL level.
> Developer1 will be working in function1, while developer2
> will be working
> no function2 based on function1. Developer circular dependency. Boom!

That is indeed the case, especially when working on a development table. If
working on a real (but staging not production) database, then the PostgreSQL
backend will prevent the circular dependency occuring in most cases.

> When an object is broken, with your system, you have to
> retrieve source
> code from revision logs.
> I don't know if we can speak of ACID rules, but ... this is not a
> consistent and atomic choice.

If you or I break code in CVS (or M$ Visual Sourcesafe), then we rollback
using a log. The fact that my system stores the SQL required to re-create
the object is neither here nor there, it's just the easiest way or
representing *any* object in a standard way.

I did consider storing copies of each attribute of each object of each type,
however the work involved in keeping that upgradeable through versions of
PostgreSQL would be huge. That's why the PostgreSQL upgrade procedure is
Dump/Reload rather than pg_upgrade or similar (I believe this was tried
once).

There are 2 key points here for my system to work:

1) The SQL generation must be correct. In reality (once past testing), most
errors already appear to occur in an obvious way - the SQL syntax is
normally screwed because of a a double quote _in_ an object name, or there's
a missing space before a little used qualifier...

2) You _must_ have suitable procedures in place for publishing. i.e. all
developers commit and logout and you refresh pgSchema before publishing
(actually, I think I'll make pgSchema do that).

> >2) Yes, compilation must be in dependancy order (which
> Publishing is).
> >I don't know how we could ever get over my circular
> dependency problem
> >though. That *would* require logs to figure out the sequence
> of queries
> >required to create the circular dependency in the first
> place. And yes,
> >I do agree that that is nasty... A good heated debate does
> brighten up
> >the day don't you think?!
>
> Yes my friend. Indeed, we are discussing about the (future)
> schema features
> of PostgreSQL.
> We need more input on PostgreSQL core team schema and
> dependency projects
> ... or start our own project.

Yes, it would be good to know where they are going. If there will be a
pg_dependencies in 7.3 for example, it might be worth holding off for that
on some tasks...

Later, Dave.