Обсуждение: 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
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.
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
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.
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 databaseHello,
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.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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
On 22-02-2016 19:42, Melvin Davidson wrote:
+ 1On 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 databaseHello,
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.See the link below.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.
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.
Listen 1 1 similar discussion at [1]
[1] http://pgsql-hackers.postgresql.narkive.com/TQSHWw1l/proposal-store-timestamptz-of-database-creation-on-pg-database
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