Re: [GENERAL] Table create time

On 31/08/2017 16:12, Achilleas Mantzios wrote:
> On 31/08/2017 14:03, hamann.w@t-online.de wrote:
>>>> On 31/08/2017 09:56, hamann.w@t-online.de wrote:
>>>>> Hi,
>>>>>
>>>>> is there a way to add a table create (and perhaps schema modify) timestamp to the system?
>>>>> I do occasionally create semi-temporary tables (meant to live until a problem is solved, i.e. longer
>>>>> than a session) with conveniently short names.
>>>> In FreeBSD you'd do smth like this to find the file creation time :
>>>> ls -lU <path to your cluster>/data/PG_9.3_201306121/16425/12344
>>>>
>>>> where 12344 is the filenode of the relation in question. In ext4 you may do this albeit with more difficulty.
>>>>
>> Hello Achilleas,
>>
>> many thanks for responding. There are two problems;
>> a) accessing the filesystem will likely require some extra effort (e.g. installing an untrusted programming
>> language)
> No need for this. You may use builtin pg_stat_file function . I see it supports a "OUT creation timestamp with time
zone"parameter. 

Sorry, just tested that against both FreeBSD pgsql9.3 and Ubuntu/ext4 10beta3, and .creation returns null in all tests.
Soyes you might need to write your own function . 

>> b) a dump/restore will modify the dates
> That would be a problem, but this is not a common use case. Anyways you can always write an event trigger and store
somemessage in a log file. This should survive dump/restores . 
>
>>
>> best regards
>> Wolfgang Hamann
>>
>>
>>
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt