Re: DDL triggers?

On Tue, May 20, 2003 at 09:46:02AM -0700, Josh Berkus wrote:
> David,

> > Roight. In a more perfect world, people would do their DDL
> > changes in some slightly more trackable way than the psql command
> > line. Unfortunately, people sometimes don't. What I'd like to do
> > is make it so that tables, views, functions, &c have comments
> > stuck on (inserted or appended, as appropriate) automagically.
> > These comments would be the DDL statement that did the deed. I'm
> > not so worried about DDL statements like DROP, but CREATE and
> > ALTER would be very, very nice to be able to track.

> This is the developers on the project, I take it?

Yep, and I'm the consultant called in to clean up. What you're
suggesting below re: development process is what I'm doing slowly &
gently. It doesn't help for me to make suggestions that they won't
follow, and besides, I like to set things up so there's *less* stuff
to remember to do rather than more.

> What comes immediately to mind is simply taking away their rights to
> create objects in the public schema. Then they'd have to e-mail
> their DDL statements to you.

I'm working toward that.

> Frankly, I've never done a project before where more than one person
> had admin rights on the database and did not go though an elaborate
> process of authorizing schema changes, i.e. "Proposed change to
> Items table: ALTER ... "

The guy who's been doing all of this is the primary reason I'm
interested in such things. ;)

> For automated tracking, there's the "after the fact" method:
> pg_dump the schema to disk hourly and run a diff, saving the results
> to a log. Or just version the complete schema file in CVS. Not
> quite what you want, but it could be automated.

OK.

> Alternately, we can raise this on -Hackers and see what the
> difficulty would be for hacking some of the libraries to either:

> 1) save all DDL statements to a log, or:

This would actually be a super-groovy thing in general, 'cause the
stuff pg_dump spits out, while useful from a reloading point of view,
is a royal PITA from a reverse-engineering point of view. Ideally,
DDL's would get logged in the precise state (ideally, with "--"'s
included) that they were in before they got executed.

> 2) back-up all tables, views, etc. to file before changing.

Not as elegant, but workable. Thanks for the suggestions :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778