Re: Table modification

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr]
> Sent: 02 October 2001 18:38
> To: pgadmin-hackers(at)postgresql(dot)org
> Subject: Re: [pgadmin-hackers] Table modification
>
>
>
> >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.
>
> No, source code should never be 'rolled back'.
> We have to distinguish source (source code) from target
> (compiled code).

If you screw up CVS, then I will roll back to the last good version of the
code. Your broken code may well remain as a previous version in CVS, but the
current version will once again be the working code. This may then be
re-compiled.

Switching this around...

If I break a PL/pgSQL function then you will roll back to the last good
version. My broken code may remain in Revision Control, but the current
version will be the working code. This may then be recompiled (in fact would
be because the current version is built when created).

> In case of problem, we roll back target, not source.

This is terminology. The net effect is that the old source becomes the
current version again as does the old target. eg.

V1 Is good
V2 Is bad
V3 Is a copy of V1

This way, we have the full audit trail of what has been done, and the good
code is the current. This is rebuilt and in theory the target becomes
identical to how it was with V1.

> > > 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.
>
> No, it will not prevent it, because in many cases unresolved
> dependencies
> are necessary and needed.
> Especially when starting a project. You write that functionA
> is based on
> functionB, and functionB does not exist.
> It is normal, this is the development process. Someone else
> might work on
> functionB the day after.

OK, following some tests I find that PostgreSQL doesn't validate a PL/pgSQL
function when it builds it as I thought. I tend to use plain SQL functions
which are validated so the situation you describe above cannot occur. That
is why I figure that the best validation is to actually build each object.

>
> > > 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.
>
> CVS cannot be compared to a programming language but to a
> backup system.

Which does exactly what I designed Revision Control for. Where's the
problem, I don't understand?

> What we call compilation is a process that starts from source
> (even if
> source code is 'ljkgksdjgsdhglsgh')
> and finishes with an executable (workable or not). The most
> important thing
> is too preserver source code,
> even in case of power failure and therefore:
> - store source code in separate tables (because PostgreSQL
> screws up source
> code with OIDs and custom modifications),

Herein lies the problem. Whilst functions can be coded in this way, other
objects (which are equally important) are not coded in one go. To store the
source to a table, you might have to store the original create table query,
and then numerous alter tables, and select intos etc. The table evolves over
time, and whilst publishing the last thing you want to do is repeat all
those edits (which you yourself pointed out recently). Instead, we analyse
the finished result and generate one create table query that builds the
table correctly from the outset.

I understand your reasoning wrt functions (following my discovery that they
aren't validated on build), but what do we do about it? The system that is
in place (and being developed) will work well for my and my staff's
purposes, and would also work well in the software houses I've previously
worked (developing Financials/Payroll/Personnel/Project Management
software), but obviously we need to address your requirements as we.

How do we achieve both?

> - use transactions to preserver ACID rules.

ACID? Do you mean ensuring the either *everything* is built or *nothing* is
built?

> >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...
>
> This is a huge problem.

It isn't a huge problem because before publishing to the live system, you do
a test run to highlight such problems. Also, most of these problems will
hopefully show up in beta releases (which is their purpose).

>
> >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).
>
> This is workable in a small team, not in a mixed pgAdmin /
> phppgAdmin world.

Why? When your qa people agree that all is well, you go into code freeze and
begin testing the publish process. If you're samll enough that you do your
own qa, then the chances are that you can shout to all your developers!

>
> I will be helping you this time. As you know, my business depends on
> PostgreSQL which has better UNICODE support in 7.2.
> Our problem comes from PostgreSQL code style, between
> 'interpreted' and
> 'compiled' type.

Yes, you've used these words previously, but I think I misunderstood your
meaning. I guess now that you are referring to what the difference I
mentioned above between an SQL function and a PL/pgSQL function?

/D