Re: Proposal: Store "timestamptz" of database creation on "pg_database" - Mailing list pgsql-hackers

Hi,

Tom Lane <tgl@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 <sfrost@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
caseit 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;
elsiftg_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
('createtable', 'alter table', 'drop table')             and context in ('toplevel', 'generated', 'subcommand')
executeprocedure public.track_table_activity();      drop schema if exists test cascade;      create schema test
createtable 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 *
frompublic.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-2717: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