Re: proposal: add columns created and altered to pg_proc and pg_class

On Mon, Apr 13, 2009 at 2:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> this my proposal is very simple. It help to people who have to manage
>> large or complex database system. Important data are date of creating
>> and date of altering tables and stored procedures. These data cannot
>> be modified by user, so implementation doesn't need any new
>> statements.
>
> ISTM anyone who thinks they need this actually need a full DDL log;
> or at least, if we give them this, they will be back next week asking
> for a full log.  So it'd save a lot of work to tell them to just log
> their DDL to start with.

DDL logs are good, but you generally can't keep them around forever,
so it's helpful to have some basic information that occupies O(1)
space. So based on that I'd respond to these objections as follows:

> Some obvious objections to the simple approach:
> - what if I want to know *who* made the change
Fine, let's log the OID of the creator and of the person who made the
last change, too (or else decide that the dependency problems are too
thorny - we'd need to set this to NULL if a role is dropped - and
don't).

> - what if I need to know about the change before last
Tough, you should have a DDL log.

> - what if I need to know about a DROP
Tough, you should have a DDL log.

> - what if I need to know about operators, operator classes, schemas, etc
>  etc
Fine, let's log this info for those too (or else decide they're too
obscure and don't - pg_class and pg_proc are certainly the most
interesting cases).

In my applications, these requirements comes up frequently for user
data and I've handled it by adding creation_time, creator_id,
last_updated_time, last_updater_id columns to nearly every table that
users can modify. It satisfies 90% of the auditing requirements for
10% of the work, and there's nothing to say that a more elaborate
mechanism can't be built and used where necessary (which I have also
done - but only for particularly critical data).

...Robert