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

On 12/26/12 4:48 PM, Fabrízio de Royes Mello wrote:

Hi all,

This proposal is about add a column "datcreated" on "pg_database" to store
the "timestamp" of the database creation.

I agree that it would be useful. However, if we're going to get into
created dates, we should at least consider adding them to the other
catalogs, particularly pg_class.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Josh Berkus (josh@agliodbs.com) wrote:

On 12/26/12 4:48 PM, Fabrízio de Royes Mello wrote:

This proposal is about add a column "datcreated" on "pg_database" to store
the "timestamp" of the database creation.

I agree that it would be useful. However, if we're going to get
into created dates, we should at least consider adding them to the
other catalogs, particularly pg_class.

I was thinking more-or-less the same thing. Along those lines, however,
perhaps we should put them into a separate catalog to avoid the
increased size of pg_class and friends..? Also, we'd probably have 2 of
those, one for global and one for per-database objects, ala pg_depend
and pg_shdepend, and then a view that brings it all together, resolves
the OIDs to names, etc.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* Josh Berkus (josh@agliodbs.com) wrote:

On 12/26/12 4:48 PM, Fabr�zio de Royes Mello wrote:

This proposal is about add a column "datcreated" on "pg_database" to store
the "timestamp" of the database creation.

I agree that it would be useful. However, if we're going to get
into created dates, we should at least consider adding them to the
other catalogs, particularly pg_class.

I was thinking more-or-less the same thing.

This has been debated, and rejected, before.

To mention just one problem, are we going to add nonstandard,
non-backwards-compatible syntax to every single kind of CREATE to allow
pg_dump to preserve the creation dates? Another interesting question is
whether we should likewise track the last ALTER time, or perhaps whether
a sufficiently major ALTER redefinition should update the creation time.

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.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

To mention just one problem, are we going to add nonstandard,
non-backwards-compatible syntax to every single kind of CREATE to allow
pg_dump to preserve the creation dates?

Perhaps 'ALTER' would be a better place to put it, but concerns around
how to make pg_dump work with it hardly strikes me as a serious argument
against this. I agree that we may be overloading ourselves with syntax
but that's a compromise we made long ago in order to have pg_dump be
able to act like a regular 'user'.

Another interesting question is
whether we should likewise track the last ALTER time, or perhaps whether
a sufficiently major ALTER redefinition should update the creation time.

Yes, tracking the last 'ALTER' time would be useful as well, as it's own
field. 'ALTER' wouldn't change the 'CREATE' time, except perhaps if it
has an explicit 'make the CREATE time X' option.

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.

I considered that and rejected it. Event triggers will be great to
allow people to customize and/or specialize exactly what is tracked and
how, but I dislike that they would be the only way to get this
information. I'm on the fence about if, assuming event triggers go in,
we provide this kind of information through a 'default' set of event
triggers. I wouldn't want users to be able to modify those event
triggers and I'd expect the results to go into a system table that we
wouldn't want users messing with either.

This information could be extremely useful for forensics, debugging, ETL
processes (many of which create tables as part of their processes), etc.

Thanks,

Stephen

This information could be extremely useful for forensics, debugging, ETL
processes (many of which create tables as part of their processes), etc.

I'd say "moderately useful" at best. Quite a number of things could
make the creation dates misleading or not distinctive (think partition
replacement, restore from pg_dump, replicas, etc.). ALTER dates would
be more useful, but as Tom points out, would need the
user-configurability which can only be delivered by something like event
triggers.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Josh Berkus (josh@agliodbs.com) wrote:

This information could be extremely useful for forensics, debugging, ETL
processes (many of which create tables as part of their processes), etc.

I'd say "moderately useful" at best. Quite a number of things could
make the creation dates misleading or not distinctive (think
partition replacement, restore from pg_dump, replicas, etc.).
ALTER dates would be more useful, but as Tom points out, would need
the user-configurability which can only be delivered by something
like event triggers.

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

Thanks,

Stephen

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Dec 27, 2012 at 2:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

This has been debated, and rejected, before.

I know this discussion...

To mention just one problem, are we going to add nonstandard,
non-backwards-compatible syntax to every single kind of CREATE to allow
pg_dump to preserve the creation dates? Another interesting question is
whether we should likewise track the last ALTER time, or perhaps whether
a sufficiently major ALTER redefinition should update the creation time.

I agree with you because now we have Event Triggers...

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.

Exactly, but Event Triggers [1]http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html don't cover "CREATE DATABASE" statement,
and for this reason I propose the patch to add a single column "datcreated"
on shared catalog "pg_database".

[1]: http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On Thu, Dec 27, 2012 at 2:04 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr>
wrote:

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.

+1

Maybe the best way to reconciliate both your views would be to provide
the previous example in the event trigger docs?

+1

If all of you agree I can improve the event trigger docs...

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Dimitri,

* Dimitri Fontaine (dimitri@2ndQuadrant.fr) wrote:

Here's a complete test case that works with my current branch, with a
tricky test while at it, of course:

Apparently I've managed to miss the tricky case..?

Sure, dropping tables, schemas, etc, would have an impact on the values.
Dropping a table and then recreating it would be akin to deleteing a row
and then inserting a new one- the create value would be set to the time
of the new table being created and information about the dropped table
would be lost.

I'm not thinking of this as audit tracking where every action is logged.

I agree that what I was suggesting would be possible to implement with
event triggers, but I see that as a rather advanced feature that most
users aren't going to understand or implement. At the same time, those
more novice users are likely to be looking for this kind of information-
being told "oh, well, you *could* have been collecting it all along if
you knew about event triggers" isn't a particularly satisfying answer.

That's my 2c on it.

I agree that having the example in the docs would be nice- examples are
always good things to include.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

Apparently I've managed to miss the tricky case..?

That shouldn't be tricky as a user, but has been a tricky subject every
time we've been talking about implement Event Triggers in the past two
years, so I though I would include it:

create schema test
create table foo(id serial primary key, f1 text);

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();

The trick is that you then want to fire the event trigger for a command
in a 'subcommand' context, as seen in the logs provided by the "snitch"
example:

NOTICE: snitch event: ddl_command_end, context: SUBCOMMAND
NOTICE: tag: CREATE TABLE, operation: CREATE, type: TABLE
NOTICE: oid: 25139, schema: test, name: foo

Sure, dropping tables, schemas, etc, would have an impact on the values.

we don't have, as of yet, support for a 'cascade' context. We will need
some heavy refactoring to get there, basically forcing the cascade drops
to happen via ProcessUtility(), but having a single DropStmt to handle
that I guess it shouldn't be very hard to do.

being told "oh, well, you *could* have been collecting it all along if
you knew about event triggers" isn't a particularly satisfying answer.

True that.

Now, having at least a way to do that without resorting to hacking the
backend or writing a C coded extension sure feels nice enough an answer
to me here.

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

Hi all,

And about proposal that originated this thread... I proposed only to add a
column on shared catalog "pg_database" with timestamp of its creation.

Event triggers don't cover "CREATE DATABASE" statement.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On 12/28/12 4:05 AM, Fabrízio de Royes Mello wrote:

Hi all,

And about proposal that originated this thread... I proposed only to add a
column on shared catalog "pg_database" with timestamp of its creation.

Event triggers don't cover "CREATE DATABASE" statement.

Works for me, in that case.

You'd need something a lot more mature than checking the file timestamp
on PG_VERSION, though.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Fabrízio de Royes Mello <fabriziomello@gmail.com> writes:

Event triggers don't cover "CREATE DATABASE" statement.

The reason why is because you create Event Triggers in a specific
database and they only get run when you happen to be connected to that
specific database.

So for example say you install your Event Trigger in the "postgres"
database but then do a CREATE DATABASE while connected to "mydb", the
Event Trigger is not installed and will not fire.

It's the same analysis about tablespaces and roles, for all global
objects in fact. I don't think there's much of a technical
implementation reason why not supporting Event Triggers on those, it's
all about POLA violation.

I personnaly think that given a good documentation coverage having Event
Trigger on global objects could be useful enough, even if you would have
to install them in every database when you want them to fire no matter
what.

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

Dimitri,

* Dimitri Fontaine (dimitri@2ndQuadrant.fr) wrote:

I personnaly think that given a good documentation coverage having Event
Trigger on global objects could be useful enough, even if you would have
to install them in every database when you want them to fire no matter
what.

I disagree. If we're going to have what are essentially 'global' event
triggers, then they should go into a shared catalog- the user shouldn't
be required to install them everywhere to get coverage. In addition,
they should always fire in the same database (eg: postgres), so you
could reasonably have a single log of 'CREATE DATABASE' commands being
run. Of course, then we get into the technical issues which prevent
that, such as having one backend connected to database xyz but needing
to run commands in the postgres database.

So, for my 2c, I do think there's a technical challenge which would have
to be overcome to have global event triggers.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

I disagree. If we're going to have what are essentially 'global' event
triggers, then they should go into a shared catalog- the user shouldn't
be required to install them everywhere to get coverage. In addition,

I understand your view point, and if we think we will be able to get
that in the future, then I think we should be careful not to implement
something else in the mean time.

they should always fire in the same database (eg: postgres), so you
could reasonably have a single log of 'CREATE DATABASE' commands being
run. Of course, then we get into the technical issues which prevent
that, such as having one backend connected to database xyz but needing
to run commands in the postgres database.

So, for my 2c, I do think there's a technical challenge which would have
to be overcome to have global event triggers.

It sounds to me like either autonomous transaction with the capability
to run the independant transaction in another database, or some dblink
creative use case. Another approach would be to get plproxy into core
as a Foreign Data Wrapper for FOREIGN FUNCTION that would target
PostgreSQL.

Given that, we could maybe have an internal setup that allows us to run
foreign functions in the postgres database from any other one, providing
what we need for Global Event Triggers.

Oh, I don't see that happening in 9.3.

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

* Dimitri Fontaine (dimitri@2ndQuadrant.fr) wrote:

It sounds to me like either autonomous transaction with the capability
to run the independant transaction in another database, or some dblink
creative use case. Another approach would be to get plproxy into core
as a Foreign Data Wrapper for FOREIGN FUNCTION that would target
PostgreSQL.

Given that, we could maybe have an internal setup that allows us to run
foreign functions in the postgres database from any other one, providing
what we need for Global Event Triggers.

Of those, I'd think autonomous transactions is by far the most likely
and also useful for other sitatuions. I don't see dblink or plproxy
being used for this. Having some internal setup which allows us to run
foreign functions in other databases seems more-or-less akin to
autonomous transactions also.

Oh, I don't see that happening in 9.3.

I agree, didn't mean to imply otherwise.

Thanks,

Stephen

On 2012-12-29 09:59:49 -0500, Stephen Frost wrote:

* Dimitri Fontaine (dimitri@2ndQuadrant.fr) wrote:

It sounds to me like either autonomous transaction with the capability
to run the independant transaction in another database, or some dblink
creative use case. Another approach would be to get plproxy into core
as a Foreign Data Wrapper for FOREIGN FUNCTION that would target
PostgreSQL.

Given that, we could maybe have an internal setup that allows us to run
foreign functions in the postgres database from any other one, providing
what we need for Global Event Triggers.

Of those, I'd think autonomous transactions is by far the most likely
and also useful for other sitatuions. I don't see dblink or plproxy
being used for this. Having some internal setup which allows us to run
foreign functions in other databases seems more-or-less akin to
autonomous transactions also.

I don't think autonomous transactions are the biggest worry
here. Transactions essentially already span multiple databases, so thats
not really a problem in this context. Making it possible to change
catalogs while still being active in another database seems *far*
harder. To the point where I would say its not really feasible.

A shared table for event triggers sounds like it would be the far easier
solution (9.4+ that is).

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Andres Freund (andres@2ndquadrant.com) wrote:

I don't think autonomous transactions are the biggest worry
here. Transactions essentially already span multiple databases, so thats
not really a problem in this context. Making it possible to change
catalogs while still being active in another database seems *far*
harder. To the point where I would say its not really feasible.

There's two pieces- one is changing catalogs and the other is being able
to have multiple top-level transactions running in a single backend. I
agree that transactions already span multiple databases but I was
expecting the global event trigger to need to run in its own transaction
in the other database, similar to autonomous transactions (though those
could be running in the same database, thus omitting the catalog switch
issue). Spawning a new backend which connects to any database and gets
a new transaction would handle both, which is what I was thinking about.

A shared table for event triggers sounds like it would be the far easier
solution (9.4+ that is).

But what happens when it fires and tries to insert a record into a
table..? Does that table have to exist in every database or the event
fails? If it exists in every database, the admin/user/whomever has to
go hunting through all the databases to get a complete picture..
Neither is very good, imv.

Thanks,

Stephen