Why is there no object create date is the catalogs?
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*
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
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
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<http://www.niwa.co.nz>
[NIWA]<http://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
Attachments:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
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.
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?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 formCREATE 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
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.