Skip site navigation (1) Skip section navigation (2)

Re: DDL triggers?

From: David Fetter <david(at)fetter(dot)org>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: DDL triggers?
Date: 2003-05-20 16:59:14
Message-ID: 20030520165914.GC19086@fetter.org (view raw or flat)
Thread:
Lists: sfpug
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

In response to

Responses

sfpug by date

Next:From: Josh BerkusDate: 2003-05-20 17:09:40
Subject: Re: DDL triggers?
Previous:From: Josh BerkusDate: 2003-05-20 16:46:02
Subject: Re: DDL triggers?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group