Re: proposal: add columns created and altered to pg_proc and pg_class - Mailing list pgsql-hackers

2009/4/13 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.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.

Yes, it is solution, but this method isn't too much practical. You
have to do some grep and regular-expressions gaming for to get some
info. It's similar info about autovacuum.

>
> Some obvious objections to the simple approach:
> - what if I want to know *who* made the change

it should be interesting, but I see two problems - a) user's should be
dropped, b) lot of firms use some special no login user for creating
objects. But it has sense.

> - what if I need to know about the change before last

it is out of relation databases. Should be solved via triggers on DDL
statements. You hypothetical  request going to much far - you should
to store state before, DDL statement, . In this moment I don't would
to create complete system like CVS. And I would not do it in future.
When PostgreSQL will have some DDL statement triggers or some similar,
then people will do own complete systems.  For me, time of last change
is basic property like owner, size, name ...

> - what if I need to know about a DROP

it's similar to creation time and modify time in file systems. When
you need some special, you need some special techniques - watchers,
...

> - what if I need to know about operators, operator classes, schemas, etc
>  etc

+ databases, all is important, and why not?


All your objections are regular, but I propose some what I thing is
most important, and other should be added later (step by step).

>

I am sure, so all these information should be taken from outer
sources, and reason, why I should it, should be removed via some
procedural or organisation rules. One sample from my current practise.
I am working as database architect in large firm. I have full access
to postgres on develop, deploy and preprod environment. But I haven't
any special access on production. I am able to see logs on production.
But when I would to check if some patches was applied, then I have to
search in logs - and logs are parted and rotated - for me is important
information about last change on tables or functions. Of course,
there are other kind of objects, but I never needed this info.

regards
Pavel Stehule



>                        regards, tom lane
>