Thread: Get the date of creation of objects in the database

Hello,

How to get the date of creation of objects in batabase?
For example: The date of creation of tables and trigger.

Thank you;

Edson

On 02/22/2016 02:00 PM, Edson F. Lidorio wrote:
> Hello,
>
> How to get the date of creation of objects in batabase?
> For example: The date of creation of tables and trigger.

AFAIK that is not recorded by default. If you are using Postgres 9.3+
you could roll your own audit system, going forward, using event triggers:

http://www.postgresql.org/docs/9.5/interactive/event-triggers.html

Take a look at matrix below for what is included and not included:

http://www.postgresql.org/docs/9.5/interactive/event-trigger-matrix.html

PL/pgSQL example:

http://www.postgresql.org/docs/9.5/interactive/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER

>
> Thank you;
>
> Edson
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Feb 22, 2016 at 5:18 PM, Zlatko Asenov <zlatko.asenov@gmail.com> wrote:

You may find useful a SIEM to record activity like this.

---

From: David G. Johnston
Sent: ‎2/‎23/‎2016 0:14
To: Edson F. Lidorio
Cc: pgsql-general
Subject: Re: [GENERAL] Get the date of creation of objects in the database

On Mon, Feb 22, 2016 at 3:00 PM, Edson F. Lidorio <edson@openmailbox.org> wrote:

Hello,

How to get the date of creation of objects in batabase?
For example: The date of creation of tables and trigger.

​ The only source of data for that question is the local filesystem.  If that is acceptable you can find examples online provided to others who have asked this question.

If you want something internal to the database the answer is no.  Those same searches will turn up the various reasons why such a feature has not gotten any strong traction from the developers.

This was my search query:  "postgresql object creation time"

David J.

Long ago, I requested that capability be added to PostgreSQL, as both Oracle and MS SQL Server store the creation date in their catalogs.

Perhaps if you care to add your support to mine, you can help influence the developers and it will be added in a future version.

See the link below.

Add relcreated to pg_class catalog
--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

On 02/22/2016 03:24 PM, Edson F. Lidorio wrote:
>
>

> + 1
>
> Listen11similar discussionat [1]
>
> [1]
> http://pgsql-hackers.postgresql.narkive.com/TQSHWw1l/proposal-store-timestamptz-of-database-creation-on-pg-database

Where the above leads to is implementing a version control system inside
the database. Not necessarily a bad idea, but something I personally see
as better handled by dedicated version control software. Myself, I use
sqitch(http://sqitch.org/) in combination with Mercurial to keep track
of object creation and changes. Being independent of the database it
handles dealing with multiple instances(think dev, testing, production)
easier.

--
Adrian Klaver
adrian.klaver@aklaver.com

David,

* David G. Johnston (david.g.johnston@gmail.com) wrote:
> The only source of data for that question is the local filesystem.  If
> that is acceptable you can find examples online provided to others who have
> asked this question.

What on the local filesystem would help here..?  All you know from that
is when the relfilenode was created, but that's not the same as when the
table was created in the face of various commands that we have which
change the relfilenode..

> If you want something internal to the database the answer is no.  Those
> same searches will turn up the various reasons why such a feature has not
> gotten any strong traction from the developers.

> This was my search query:  "postgresql object creation time"

At least on a first blush look through the threads linked from such a
search, I'm unimpressed by the arguments against and note that there are
quite a few arguments for.

The summary of comments seems to be:

1) We don't know what we want

  Doesn't seem terribly hard to define.

  Object creation time (aka: CREATE TABLE time)
  Object modification time (aka: ALTER TABLE time)

  We could provide a function for 'last data modification time' which
  simply uses the filesystem modification time.

2) It'll be expensive to keep track of

  We often claim that we aren't too worried about DDL cost, and this
  would be a very small additional cost on top of that.  If it's
  measurable then perhaps we could avoid doing it for temp tables, but
  that strikes me as really the only case that it might be worthwhile
  and I'm not convinced it is.

Thanks!

Stephen

Stephen Frost <sfrost@snowman.net> writes:
> At least on a first blush look through the threads linked from such a
> search, I'm unimpressed by the arguments against and note that there are
> quite a few arguments for.

I think you missed the worries around what dump/reload semantics would be.

>   We could provide a function for 'last data modification time' which
>   simply uses the filesystem modification time.

As far as tables go, the filesystem mod time would not be anything of
great use to users.  Consider that (1) there might be committed but
unwritten data sitting in shared buffers, so the filesystem mod time could
be too old by as much as the max checkpoint interval; while (2) writes for
hint bit setting or xid freezing could happen long after the last data
write, so the filesystem mod time could be almost arbitrarily later than
what the user thinks is the last mod time.  Not to mention whether
physical rewrites such as CLUSTER ought to count as data mods.

But I thought this request was about DDL timestamps, not data timestamps.
The filesystem will help you not at all there, because at best it would
know about the last mod time on the relevant system catalog, not any
individual object.

Anyway, my main objection to this idea is that it would be a sinkhole for
arguments over what the detailed semantics would be.  Should dump/reload
result in a new DDL timestamp?  (If not, the only way to prevent it would
be to invent a new "ALTER object SET TIMESTAMP" family of DDL, which would
not merely be a lot of work but would mean that the timestamps would have
exactly 0 value for any sort of forensic purposes.)  Should, eg, COMMENT
ON cause a DDL timestamp update on the referenced object?  How about
REINDEX or VACUUM or ANALYZE?  How about something like creating a foreign
key reference to a table?  I think that you could make credible arguments
either way on each of these issues, depending on what you assume the true
use-case is for having the timestamps; which means that trying to support
them is a mug's game.  We won't satisfy anybody, least of all the users
who don't care and don't need the additional overhead.

Lastly, even if we had a DDL timestamp, it wouldn't tell you anything
about what that last change was.  So I think logging/auditing DDL
operations is a far better path to pursue.

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > At least on a first blush look through the threads linked from such a
> > search, I'm unimpressed by the arguments against and note that there are
> > quite a few arguments for.
>
> I think you missed the worries around what dump/reload semantics would be.

No, I just wasn't impressed by those concerns.  That's about like asking
"what should rsync do with filesystem timestamps?"  It's really not a
hugely difficult issue to address.

> >   We could provide a function for 'last data modification time' which
> >   simply uses the filesystem modification time.
>
> As far as tables go, the filesystem mod time would not be anything of
> great use to users.  Consider that (1) there might be committed but
> unwritten data sitting in shared buffers, so the filesystem mod time could
> be too old by as much as the max checkpoint interval; while (2) writes for
> hint bit setting or xid freezing could happen long after the last data
> write, so the filesystem mod time could be almost arbitrarily later than
> what the user thinks is the last mod time.  Not to mention whether
> physical rewrites such as CLUSTER ought to count as data mods.

It's not an auditing system, no, it'd be a "this is the last time we
wrote to this relation."  We'd have to caveat that accordingly, to point
out that we might have data in shared buffers, etc, but this is a
definitional concern for the function, not an argument that such a
capability wouldn't be useful to some people.

> But I thought this request was about DDL timestamps, not data timestamps.

This specific one, the question about data modification was brought up
on at least the thread which I was reading.

> The filesystem will help you not at all there, because at best it would
> know about the last mod time on the relevant system catalog, not any
> individual object.

Agreed, which is why I only suggested a function that looks at the
filesystem for the "last data modification time" case.  The other cases,
which addressed the question brought up on this thread but you didn't
quote into your response, would depend on new fields in the relevant
system catalogs.

> Anyway, my main objection to this idea is that it would be a sinkhole for
> arguments over what the detailed semantics would be.  Should dump/reload
> result in a new DDL timestamp?  (If not, the only way to prevent it would
> be to invent a new "ALTER object SET TIMESTAMP" family of DDL, which would
> not merely be a lot of work but would mean that the timestamps would have
> exactly 0 value for any sort of forensic purposes.)

Yes, we would need such timestamps, no, I don't agree that having such
makes it useless (are the timestamps on unix filesystems similairly
"useless"?  I'd say no.)

> Should, eg, COMMENT
> ON cause a DDL timestamp update on the referenced object?  How about
> REINDEX or VACUUM or ANALYZE?  How about something like creating a foreign
> key reference to a table?  I think that you could make credible arguments
> either way on each of these issues, depending on what you assume the true
> use-case is for having the timestamps; which means that trying to support
> them is a mug's game.  We won't satisfy anybody, least of all the users
> who don't care and don't need the additional overhead.

We're back to the argument about the additional overhead and I simply
don't buy into that.  Yes, we'd have to come up with reasonable answers
to the above questions, but I don't see that as beyond our capabilities
or impossible to have reasonable answers which most users will
appreciate (COMMENT => yes, REINDEX => no, VACUUM => no, ANALYZE => no,
ALTER TABLE => yes, CREATE POLICY => yes, FOREIGN KEY REFERENCE => no;
note that this list is, essentially, "would a schema-only pg_dump for
*this* table be different?").

> Lastly, even if we had a DDL timestamp, it wouldn't tell you anything
> about what that last change was.  So I think logging/auditing DDL
> operations is a far better path to pursue.

I certainly don't mean to imply that this is more valuable or important
than a proper in-core auditing solution.  I don't believe our current
logging implementation is a credible answer to this request.  Given that
we don't have an in-core auditing solution, it's hard to say if it would
be easy to handle such a request.

Ultimately, I don't see what's been done by the other RDBM systems as
being particularly novel or difficult for us to provide and, in this
case, I don't believe they're just providing a feature which no one uses
or understands but rather are responding to entirely reasonable user
requests and have a reasonable solution that's continued to be useful
over time.

Even so, I'm not so enamored with this request that I'd spend resources
on it, but I wouldn't throw away a patch which implemented it out of
hand either.

Thanks!

Stephen