Hi, Tom Lane <t...@sss.pgh.pa.us> writes: >>>> This proposal is about add a column "datcreated" on "pg_database" to store >>>> the "timestamp" of the database creation. > > I'm inclined to think that anyone who really needs this should be > pointed at event triggers. That feature (if it gets in) will allow > people to track creation/DDL-change times with exactly the behavior > they want.
Agreed. Stephen Frost <sfr...@snowman.net> writes: > To be honest, I really just don't find this to be *that* difficult and > an intuitive set of rules which are well documented feels like it'd > cover 99% of the cases. pg_dump would preserve the times (though it > could be optional), replicas should as well, etc. We haven't even > started talking about the 'hard' part, which would be a 'modification' > type of field.. Here's a complete test case that works with my current branch, with a tricky test while at it, of course: create table public.tracking ( relation regclass primary key, relname name not null, -- in case it changes later relnamespace name not null, -- same reason created timestamptz default now(), altered timestamptz, dropped timestamptz ); create or replace function public.track_table_activity() returns event_trigger language plpgsql as $$ begin raise notice 'track table activity: % %', tg_tag, tg_objectid::regclass; if tg_operation = 'CREATE' then insert into public.tracking(relation, relname, relnamespace) select tg_objectid, tg_objectname, tg_schemaname; elsif tg_operation = 'ALTER' then update public.tracking set altered = now() where relation = tg_objectid; elsif tg_operation = 'DROP' then update public.tracking set dropped = now() where relation = tg_objectid; else raise notice 'unknown operation'; end if; end; $$; drop event trigger if exists track_table; create event trigger track_table on ddl_command_trace when tag in ('create table', 'alter table', 'drop table') and context in ('toplevel', 'generated', 'subcommand') execute procedure public.track_table_activity(); drop schema if exists test cascade; create schema test create table foo(id serial primary key, f1 text); alter table test.foo add column f2 text; select relation::regclass, * from public.tracking; drop table test.foo; select * from public.tracking; select * from public.tracking; -[ RECORD 1 ]+------------------------------ relation | tracking relname | tracking relnamespace | public created | 2012-12-27 17:02:13.567979+01 altered | dropped | -[ RECORD 2 ]+------------------------------ relation | 25139 relname | foo relnamespace | test created | 2012-12-27 17:02:26.696039+01 altered | 2012-12-27 17:02:29.105241+01 dropped | 2012-12-27 17:02:37.834997+01 Maybe the best way to reconciliate both your views would be to provide the previous example in the event trigger docs? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers