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
