Обсуждение: Feature / Enhancement request.
It would really be nice if pg_class were altered to add two new columns: relcreatedat & relmoddat.
Both would be of type timestamp. relcreatedat would contain the timestamp the object was created.
Likewise, relmoddat would contain the last timestamp the object was altered / modified.
This would be very useful for user tables that are meant to exist for only a specific length of time. eg: 1 month.
Then a simple:
SELECT relname
FROM pg_class
WHERE current_timestamp::date - relcreatedat::date > 30
AND relname LIKE ‘tmp_%’;
Would show possible expired tables.
Likewise, relmoddat would be useful for determining when someone made changes to a table, index, view, etc.
Melvin Davidson – DBA
Confidentiality Notice: This e-mail message (including any attached or embedded documents) is intended for the exclusive and confidential use of the individual or entity to which this message is addressed, and unless otherwise expressly indicated, is confidential and privileged information of Rackspace. Any dissemination, distribution or copying of the enclosed material is prohibited. If you receive this transmission in error, please notify us immediately by e-mail at abuse@rackspace.com, and delete the original message. Your cooperation is appreciated.
On Tue, Aug 25, 2009 at 11:18 AM, Melvin Davidson<melvin.davidson@rackspace.com> wrote: > > This would be very useful for user tables that are meant to exist for only a > specific length of time. eg: 1 month. > that's a weird concept > > Likewise, relmoddat would be useful for determining when someone made > changes to a table, index, view, etc. > i agree it could be useful for auditing porpouses... in fact, in environments like government it's likely the law who wants such a functionality... but instead of new fields we can invent a new catalog for that and we can use a guc to enable/disable the track of such changes -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On 8/25/09 9:18 AM, Melvin Davidson wrote: > > > It would really be nice if pg_class were altered to add two new columns: > relcreatedat & relmoddat. I don't think that altering pg_class would really cover all the various cases of what people want for auditing functionality. It would be more useful to be able to put a trigger on various system tables, so you could record what you wanted. However, the system tables are not normal tables to that would not work predictably. Search on "DDL Triggers" on the mailing lists for previous discussions of the technical difficulties around this issue. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com