Thread: Why is there no object create date is the catalogs?

Why is there no object create date is the catalogs?

From

Melvin Davidson

Date:

12 May 2015, 22:52:02

Can anyone tell me why there is no "relcreated" column in pg_class to track the creation date of an object?

It seems to me it would make sense to have one as it would facilitate auditing of when objects are created. In addition, it would also facilitate the dropping of objects that have exceeded a certain age.

EG: SELECT 'DELETE TABLE ' || relname || ';'
      FROM pg_class
    WHERE relkind = 'r'
       AND relcreated > current_timestamp - INTERVAL ' 1 year';

Adding that column should be relatively easy and would not break backwards compatiblity with previous versions.
--

Melvin Davidson

Re: Why is there no object create date is the catalogs?

From

Alvaro Herrera

Date:

12 May 2015, 23:13:55

Melvin Davidson wrote:
> Can anyone tell me why there is no "relcreated" column in pg_class to track
> the creation date of an object?
>
> It seems to me it would make sense to have one as it would facilitate
> auditing of when objects are created. In addition, it would also facilitate
> the dropping of objects that have exceeded a certain age.

But why -- you can implement that using event triggers.  See the
pg_event_trigger_ddl_commands() function in the docs.
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b488c580aef4e05f39be5daaab6464da5b22a494
http://www.postgresql.org/docs/devel/static/event-trigger-definition.html

... oh, the facility is only two days old, I forgot.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Why is there no object create date is the catalogs?

From

Adrian Klaver

Date:

13 May 2015, 00:53:32

On 05/12/2015 12:51 PM, Melvin Davidson wrote:
>
> Can anyone tell me why there is no "relcreated" column in pg_class to
> track the creation date of an object?

So what date would it track?:

1) The date in the original database?
2) The date the table was restored to another database cluster?
3) The date it was replicated to a standby?
4) The date it went through a DROP TABLE IF EXISTS some_table, CREATE
TABLE some_table cycle?

I could go on. I imagine that most people that want to track that sort
of thing keep their schema definitions under version control and keep
track of the dates there.

>
> It seems to me it would make sense to have one as it would facilitate
> auditing of when objects are created. In addition, it would also
> facilitate the dropping of objects that have exceeded a certain age.

Now, that just scares me:)

That is often handled through partitioning:
www.postgresql.org/docs/9.4/static/ddl-partitioning.html

Otherwise I am not sure how an object being past a certain date equates
to dropping it?

>
> EG: SELECT 'DELETE TABLE ' || relname || ';'
>        FROM pg_class
>       WHERE relkind = 'r'
>         AND relcreated > current_timestamp - INTERVAL ' 1 year';
>
> Adding that column should be relatively easy and would not break
> backwards compatiblity with previous versions.
> --
> *Melvin Davidson*

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Why is there no object create date is the catalogs?

From

Adrian Klaver

Date:

13 May 2015, 01:01:04

On 05/12/2015 12:51 PM, Melvin Davidson wrote:
>
> Can anyone tell me why there is no "relcreated" column in pg_class to
> track the creation date of an object?

Meant to add to my previous post, back before I 'discovered' version
control I use to put the creation date in the table COMMENT:

http://www.postgresql.org/docs/9.4/interactive/sql-comment.html

>
> It seems to me it would make sense to have one as it would facilitate
> auditing of when objects are created. In addition, it would also
> facilitate the dropping of objects that have exceeded a certain age.
>
> EG: SELECT 'DELETE TABLE ' || relname || ';'
>        FROM pg_class
>       WHERE relkind = 'r'
>         AND relcreated > current_timestamp - INTERVAL ' 1 year';
>
> Adding that column should be relatively easy and would not break
> backwards compatiblity with previous versions.
> --
> *Melvin Davidson*


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Why is there no object create date is the catalogs?

From

Melvin Davidson

Date:

13 May 2015, 01:44:56

Adrian,

You are over thinking this. An object is only "created" once! That is what I meant by relcreatedate. If it is dropped, then it is deleted from the catalogs. If it is modified, then it does NOT affect the creation date. Everything else is superfluous.

It is also not unusual for tables to have an end of cycle in certain application, hence the need to be dropped after a certain time. EG. Tables that track data only for a specific year.

Since PostgreSQL already tracks when tables are vacuum, auto vacuumed, analyzed and auto analyzed ( pg_stat_all_tables ), I don't see why it is such a big deal ( or so hard ) to track when an object is created. It should be a very simple patch to the catalogs.

On Tue, May 12, 2015 at 6:00 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 05/12/2015 12:51 PM, Melvin Davidson wrote:

Can anyone tell me why there is no "relcreated" column in pg_class to
track the creation date of an object?

Meant to add to my previous post, back before I 'discovered' version control I use to put the creation date in the table COMMENT:

http://www.postgresql.org/docs/9.4/interactive/sql-comment.html

It seems to me it would make sense to have one as it would facilitate
auditing of when objects are created. In addition, it would also
facilitate the dropping of objects that have exceeded a certain age.

EG: SELECT 'DELETE TABLE ' || relname || ';'
FROM pg_class
WHERE relkind = 'r'
AND relcreated > current_timestamp - INTERVAL ' 1 year';

Adding that column should be relatively easy and would not break
backwards compatiblity with previous versions.
--
*Melvin Davidson*

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Re: Why is there no object create date is the catalogs?

From

Tom Lane

Date:

13 May 2015, 02:26:24

Melvin Davidson <melvin6925@gmail.com> writes:
> You are over thinking this. An object is only "created" once!

Yeah?  Would you expect that pg_dump followed by pg_restore would preserve
the original creation date?  What about pg_upgrade?

This has come up many times before, and we've always decided that it was
not as simple as it seems at first glance, and that it would be difficult
to satisfy all use-cases.  Try searching the archives for previous threads.

            regards, tom lane

Re: Why is there no object create date is the catalogs?

From

Adrian Klaver

Date:

13 May 2015, 03:08:13

On 05/12/2015 03:44 PM, Melvin Davidson wrote:
> Adrian,
>
> You are over thinking this. An object is only "created" once! That is
> what I meant by relcreatedate. If it is dropped, then it is deleted from
> the catalogs. If it is modified, then it does NOT affect the creation
> date. Everything else is superfluous.

See my original post and Tom Lanes response.

>
> It is also not unusual for tables to have an end of cycle in certain
> application, hence the need to be dropped after a certain time. EG.
> Tables that track data only for a specific year.

Hence my link to the partitioning part of the manual.

>
> Since PostgreSQL already tracks when tables are vacuum, auto vacuumed,
> analyzed and auto analyzed ( pg_stat_all_tables ), I don't see why it is
> such a big deal ( or so hard ) to track when an object is created. It
> should be a very simple patch to the catalogs.

It is probably not a big deal to create a timestamp field and populate
it. The issues arise when you start asking what it really means. The
Postgres catalogs are not part of dump file, so the data in them will
not transfer when you restore to another database. So on restore the
create date will be the date the table is restored, not the date the
table was originally created. For some people that is okay, for others
not okay.

>
> On Tue, May 12, 2015 at 6:00 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 05/12/2015 12:51 PM, Melvin Davidson wrote:
>
>
>         Can anyone tell me why there is no "relcreated" column in
>         pg_class to
>         track the creation date of an object?
>
>
>     Meant to add to my previous post, back before I 'discovered' version
>     control I use to put the creation date in the table COMMENT:
>
>     http://www.postgresql.org/docs/9.4/interactive/sql-comment.html
>
>
>         It seems to me it would make sense to have one as it would
>         facilitate
>         auditing of when objects are created. In addition, it would also
>         facilitate the dropping of objects that have exceeded a certain age.
>
>         EG: SELECT 'DELETE TABLE ' || relname || ';'
>                 FROM pg_class
>                WHERE relkind = 'r'
>                  AND relcreated > current_timestamp - INTERVAL ' 1 year';
>
>         Adding that column should be relatively easy and would not break
>         backwards compatiblity with previous versions.
>         --
>         *Melvin Davidson*
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Why is there no object create date is the catalogs?

From

Melvin Davidson

Date:

13 May 2015, 04:33:34

I thank everyone for their feedback regarding the omission of object creation date from the catalog.

I do respect the various reasons for not including it, but I feel it is my duty to draw out this issue a bit longer.

I would like to counter the argument that a restore from a dump will override the create date.
That cannot happen.
If an object already exists, it cannot be created again. The worst case scenario is that an object must be dropped due to some terrible corruption or other disaster, in which case the creation date is not a major consideration.

Further to the point, why is it that both Oracle and SQL Server _do_ have the object create date in the catalogs?

http://stackoverflow.com/questions/4442323/how-to-find-out-when-a-particular-table-was-created-in-oracle

https://msdn.microsoft.com/en-us/library/ms190324.aspx

All I have heard so far is that the ONLY reason there is no object create date in pg_class is because there is no general agreement as to what create date means. Well I am giving it right now. When you execute the SQL statement of the form

CREATE TABLE ...
CREATE INDEX ...
CREATE SEQUENCE ...
CREATE MATERIALIZED VIEW ...
CREATE TYPE ...
CREATE FOREIGN TABLE ...

then that is when clock_timestamp() should be recorded as relcreatedate or relcreatetime.

Providing, of course, that the column is added to pg_class. :)

Is there some other overwhelming _technical_ reason that I am overlooking that prevents this from being done?

On Tue, May 12, 2015 at 8:08 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 05/12/2015 03:44 PM, Melvin Davidson wrote:
Adrian,

You are over thinking this. An object is only "created" once! That is
what I meant by relcreatedate. If it is dropped, then it is deleted from
the catalogs. If it is modified, then it does NOT affect the creation
date. Everything else is superfluous.

See my original post and Tom Lanes response.

It is also not unusual for tables to have an end of cycle in certain
application, hence the need to be dropped after a certain time. EG.
Tables that track data only for a specific year.

Hence my link to the partitioning part of the manual.

Since PostgreSQL already tracks when tables are vacuum, auto vacuumed,
analyzed and auto analyzed ( pg_stat_all_tables ), I don't see why it is
such a big deal ( or so hard ) to track when an object is created. It
should be a very simple patch to the catalogs.

It is probably not a big deal to create a timestamp field and populate it. The issues arise when you start asking what it really means. The Postgres catalogs are not part of dump file, so the data in them will not transfer when you restore to another database. So on restore the create date will be the date the table is restored, not the date the table was originally created. For some people that is okay, for others not okay.

On Tue, May 12, 2015 at 6:00 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 05/12/2015 12:51 PM, Melvin Davidson wrote:

Can anyone tell me why there is no "relcreated" column in
pg_class to
track the creation date of an object?

Meant to add to my previous post, back before I 'discovered' version
control I use to put the creation date in the table COMMENT:

http://www.postgresql.org/docs/9.4/interactive/sql-comment.html

It seems to me it would make sense to have one as it would
facilitate
auditing of when objects are created. In addition, it would also
facilitate the dropping of objects that have exceeded a certain age.

EG: SELECT 'DELETE TABLE ' || relname || ';'
FROM pg_class
WHERE relkind = 'r'
AND relcreated > current_timestamp - INTERVAL ' 1 year';

Adding that column should be relatively easy and would not break
backwards compatiblity with previous versions.
--
*Melvin Davidson*

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Re: Why is there no object create date is the catalogs?

From

Adrian Klaver

Date:

13 May 2015, 04:56:37

On 05/12/2015 06:33 PM, Melvin Davidson wrote:
> I thank everyone for their feedback regarding the omission of object
> creation date from the catalog.
>
> I do respect the various reasons for not including it, but I feel it is
> my duty to draw out this issue a bit longer.
>
> I would like to counter the argument that a restore from a dump will
> override the create date.
> That cannot happen.

Yes it can. You are asking for date field in a system catalog. The
system catalog data is not included in the dump/restore cycle. So when
you restore the schema objects to the 'new' database what do you propose
to do with the create_date field? I see two options, use the restore
time as the create_date or set it to NULL. In either case you have
changed the date. The same holds for pg_upgrade, as Tom stated.

> If an object already exists, it cannot be created again.

When you dump/restore, from the viewpoint of the new database instance,
it is created again.


The worst case
> scenario is that an object must be dropped due to some terrible
> corruption or other disaster, in which case the creation date is not a
> major consideration.
>
> Further to the point, why is it that both Oracle and SQL Server _do_
> have the object create date in the catalogs?
>
> http://stackoverflow.com/questions/4442323/how-to-find-out-when-a-particular-table-was-created-in-oracle
>
> https://msdn.microsoft.com/en-us/library/ms190324.aspx
>
> All I have heard so far is that the ONLY reason there is no object
> create date in pg_class is because there is no general agreement as to
> what create date means. Well I am giving it right now. When you execute
> the SQL statement of the form
>
> CREATE TABLE ...
> CREATE INDEX ...
> CREATE SEQUENCE ...
> CREATE MATERIALIZED VIEW ...
> CREATE TYPE ...
> CREATE FOREIGN TABLE ...
>
> then that is when clock_timestamp() should be recorded as relcreatedate
> or relcreatetime.
> Providing, of course, that the column is added to pg_class. :)
>
> Is there some other overwhelming _technical_ reason that I am
> overlooking that prevents this from being done?

There is no technical reason. There is the 'camel nose under the tent'
problem. The create_date gets added, then the petitions start for an
update_date column and before you know it the move is on for an entire
schema versioning system in the system catalogs. This is something that
is already handled by other programs. What it comes down is the old
problem of time and money and where to spend either/or in the project.
As Alvaro said there is some functionality on the horizon that will make
this easier and I could see someone in the future rolling an extension
that does this by creating an audit trail in a non-system table.

>
> On Tue, May 12, 2015 at 8:08 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 05/12/2015 03:44 PM, Melvin Davidson wrote:
>
>         Adrian,
>
>         You are over thinking this. An object is only "created" once!
>         That is
>         what I meant by relcreatedate. If it is dropped, then it is
>         deleted from
>         the catalogs. If it is modified, then it does NOT affect the
>         creation
>         date. Everything else is superfluous.
>
>
>     See my original post and Tom Lanes response.
>
>
>         It is also not unusual for tables to have an end of cycle in certain
>         application, hence the need to be dropped after a certain time. EG.
>         Tables that track data only for a specific year.
>
>
>     Hence my link to the partitioning part of the manual.
>
>
>         Since PostgreSQL already tracks when tables are vacuum, auto
>         vacuumed,
>         analyzed and auto analyzed ( pg_stat_all_tables ), I don't see
>         why it is
>         such a big deal ( or so hard ) to track when an object is
>         created. It
>         should be a very simple patch to the catalogs.
>
>
>     It is probably not a big deal to create a timestamp field and
>     populate it. The issues arise when you start asking what it really
>     means. The Postgres catalogs are not part of dump file, so the data
>     in them will not transfer when you restore to another database. So
>     on restore the create date will be the date the table is restored,
>     not the date the table was originally created. For some people that
>     is okay, for others not okay.
>
>
>         On Tue, May 12, 2015 at 6:00 PM, Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.com>>> wrote:
>
>              On 05/12/2015 12:51 PM, Melvin Davidson wrote:
>
>
>                  Can anyone tell me why there is no "relcreated" column in
>                  pg_class to
>                  track the creation date of an object?
>
>
>              Meant to add to my previous post, back before I
>         'discovered' version
>              control I use to put the creation date in the table COMMENT:
>
>         http://www.postgresql.org/docs/9.4/interactive/sql-comment.html
>
>
>                  It seems to me it would make sense to have one as it would
>                  facilitate
>                  auditing of when objects are created. In addition, it
>         would also
>                  facilitate the dropping of objects that have exceeded a
>         certain age.
>
>                  EG: SELECT 'DELETE TABLE ' || relname || ';'
>                          FROM pg_class
>                         WHERE relkind = 'r'
>                           AND relcreated > current_timestamp - INTERVAL
>         ' 1 year';
>
>                  Adding that column should be relatively easy and would
>         not break
>                  backwards compatiblity with previous versions.
>                  --
>                  *Melvin Davidson*
>
>
>
>              --
>              Adrian Klaver
>         adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.com>>
>
>
>
>
>         --
>         *Melvin Davidson*
>         I reserve the right to fantasize.  Whether or not you
>         wish to share my fantasy is entirely up to you.
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Why is there no object create date is the catalogs?

From

Brent Wood

Date:

13 May 2015, 05:23:43

Yep.

Still "created" once - instantiated repeated times, but "created" once. Try federated metadata records.... only one "original creation date" which is an explicit attribute of a record. Last copied, updated, edited are different.

Creation date can be when first entered into a spreadsheet, or written down... insert date pertains to "creation of the record as a database tuple", etc...

A replica can be copied - but that is a date this instance was created, not the original record.

One question - does an edit explicitly destroy the original object and create a new (child? linked?) object, or a modified version of the original? Answer "yeah/nah" - whichever you decide is correct for your use case - there no universal yes or no answer.

The real issue is confusion about what "created" means - for data audit tracking/provenance, etc - very important in best practice data mgmt in many domains - all these are dates representing different actions which can be defined & maintained - but by the user rather than the system (albeit often by triggers representing local business rules). Postgres has all the tools you need to implement whatever audit trails you need for create (when first written on a piece of paper), inserts, updates/edits, etc... but doing this in a standard way to meet all users needs is a long standing, unsolved & probably unsolvable issue.

Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529

Brent Wood

Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery

+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz

To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.

________________________________________
From: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org] on behalf of Tom Lane [tgl@sss.pgh.pa.us]
Sent: Wednesday, May 13, 2015 11:26 AM
To: Melvin Davidson
Cc: Adrian Klaver; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why is there no object create date is the catalogs?

Melvin Davidson <melvin6925@gmail.com> writes:
> You are over thinking this. An object is only "created" once!

Yeah? Would you expect that pg_dump followed by pg_restore would preserve
the original creation date? What about pg_upgrade?

This has come up many times before, and we've always decided that it was
not as simple as it seems at first glance, and that it would be difficult
to satisfy all use-cases. Try searching the archives for previous threads.

regards, tom lane

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

Attachment

imagea118b3.JPG

Re: Why is there no object create date is the catalogs?

From

"David G. Johnston"

Date:

13 May 2015, 06:39:37

On Tue, May 12, 2015 at 6:33 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

I thank everyone for their feedback regarding the omission of object creation date from the catalog.

I do respect the various reasons for not including it, but I feel it is my duty to draw out this issue a bit longer.

I would like to counter the argument that a restore from a dump will override the create date.
That cannot happen.

You can restore a database into an empty (and newer) cluster. This is possible because instead of archiving and restoring the catalog the restoration script recreates everything by issuing CREATE and INSERT/COPY statements. Perform an SQL dump and look at it if you need affirmation.

Now, saying that this field is marginalized for people who use pg_dump/pg_restore instead of pg_upgrade is a viable decision but the use cases put forth so far don't scream for something like this to exist.

Specifically, if an application has a requirement for something like this then the application should take pains to manage it. It can be placed into a system catalog but if the system is not going to use the information then it shouldn't be responsible for it. I guess "comments" would be an exception to this rule - but there you are dealing with constants that are dumped and restored.

Further to the point, why is it that both Oracle and SQL Server _do_ have the object create date in the catalogs?

http://stackoverflow.com/questions/4442323/how-to-find-out-when-a-particular-table-was-created-in-oracle

https://msdn.microsoft.com/en-us/library/ms190324.aspx

If a good why can be provided maybe we'd be convinced to add the field...

Pondering the above I rather think to implement this as a "comment" table but where there are two "comment" fields - one text and one timestamptz. Like with comments pg_dump would treat this as user data to be included in the dump and restored as-is. During the restoration new "created" entries would be added and so then there would be two. Though at this point you are basically saying the project should provide official storage and set of event triggers and for the "CREATE" events.

Going that far it must be argued why -core should be responsible for such a feature instead of interested parties maintaining it on PGXN.

In the end there is no technical reason to exclude the field but such a field is arguably application data and should not be present on a system catalog table. Personally, I could see an argument for such information being valuable during schema exploration - just like comments are.

The dump/restore problem should be solvable - just export "UPDATE pg_class SET creationdate = '2015-05-12T00:00:00UTC'::timestamptz WHERE oid = xxx" as part of the dump - just after the COMMENT ON statements. New objects will be created during restoration but then the old timstamp will replace the newly assigned one. Not that I've thought this through in great detail - the event-based setup, with history maintained across dumps - definitely is more appealing if quite a bit more work.

I don't see this field being an end of itself but something that would be added if some other feature required it - thus basically making it a system field instead of an application one...

David J.