Re: Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 8:01 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 04/20/2016 04:33 PM, Melvin Davidson wrote:
>
>>
>>
>>
>
>>
>> "Not until pg_upgrade is done or replication is started, in either case
>> a new cluster is started probably at a different time from the original
>> cluster."
>> Not true, whether an upgrade or rep[lication, the relcreatedat time will
>> not/cannot change. It will only change if a new database is created.
>>
>
> We will have to agree to disagree.
>
> I just cannot see that in the following:
>
> pg_upgrade 9.4 --> 9.5
>
> The 9.5 database is the same as the 9.4 one.
>
> Also in replication case:
>
> Master --> Standby
> Master dies
> Standby gets promoted to new Master.
> STONITH the original Master
> Work continues on the new Master.
> The old Master is resurrected as a new Standby.
>
> To me it would be important to know when the objects actually appeared in
> the various databases as a way of figuring what the above timeline was.
>
>
>>
>> Second thing:
>>
>> "pg_class does not track all the objects in a database, so what other
>> system catalogs should be included. With the same questions as above."
>> I am only concerned with the objects in pg_class as id'd by relkind.
>> IE:
>> tables, indexes, sequences, etc.
>>
>>
>> "Again, that is your wish and is fairly simple. Now I usually do not
>> make guarantees, but in this case I will. If pg_class gets an object
>> creation time, the clamor will start immediately for the same thing to
>> be done to the other relevant system catalogs."
>>
>> Fine. As per precedent set today, that is exactly what this list is for.
>> Now that I have initialized the request and started the discussion, that
>> is exactly the kind of feedback I want and the developers should take
>> note of.
>>
>> --
>> *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(dot)klaver(at)aklaver(dot)com
>

"I just cannot see that in the following:
pg_upgrade 9.4 --> 9.5"
The 9.5 database is the same as the 9.4 one."

You are speaking of the case where relcreatedat did not/does not exist in
the previous database?
True, but the whole point of this request is to "start obtaining creation
dates". Once we are at a point
where we have pg_class with relccreatedat, then all subsequent create dates
will be correct. I cannot think
of a single case where having incorrect creation dates from
previous/upgraded databases will cause any harm
or hinder operation of the PostgreSQL. I can however, cite instances where
users create their own tables but do
not notify the dba as such, then cry when something happens because they
are not replicated, as in the case of
using slony. In which case having relcreatedat would go a long ways to
preventing that.

"Master dies
Standby gets promoted to new Master.
STONITH the original Master
Work continues on the new Master.
The old Master is resurrected as a new Standby.
To me it would be important to know when the objects actually appeared in
the various databases as a way of figuring what the above timeline was"

That is the whole point of relcreatedat. A properly replicated database
brings over ALL needed data from the master to the slave(s), including
created objects. It works when promoting the slave, and restoring the
master providing you follow correct procedure. Otherwise, your replication
is useless. I know when working with slony this can cause a problem, but
that is a weakness of slony, not of relcreatedat. Besides, are you more
concerned with keeping the database on line, or tracking object creation
dates when PosgreSQL crashes? You are quoting the corner case. That's like
saying "I refuse to wear a seat belt because I may pass out and drive into
a lake", even though you are in Kansas and driving I-70.

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