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

Hi,

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)snowman(dot)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