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.kla...@aklaver.com <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
<mailto:adrian.kla...@aklaver.com
<mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
<mailto:adrian.kla...@aklaver.com
<mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@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.kla...@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
