Re: Proposal: Store "timestamptz" of database creation on "pg_database"

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 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