Thread: [GENERAL] Table create time
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. Also, is there a simple query to identify tables without a table comment? (so a weekly cron could remind me of tables that are already a few days old but have no explanatory comment) I am running PG 9.3 Best regards Wolfgang Hamann
Hello > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of hamann.w@t- > online.de > Sent: Donnerstag, 31. August 2017 08:56 > To: pgsql-general@postgresql.org > Subject: [GENERAL] Table create time > > > 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. Unfortunately I can't help with this. > Also, is there a simple query to identify tables without a table comment? (so a weekly cron could remind me of > tables that are already a few days old but have no explanatory comment) Comments are stored in pg_catalog.pg_description. If there is an entry for your table then it has comments, otherwise not. Example: SELECT * FROM pg_description WHERE objoid = to_regclass('survey_control'); objoid | classoid | objsubid | description ---------+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2523030 | 1259 | 0 | Main table for survey management and control. 2523030 | 1259 | 1 | The name of the survey. 2523030 | 1259 | 2 | The year of conduction. [...] For tables the objsubid is 0 for the table comment and 1..n for column description. The number refers to the position ofthe column in the table. Hope this helps. Regards Charles > > I am running PG 9.3 > > Best regards > Wolfgang Hamann > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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. > Also, is there a simple query to identify tables without a table comment? (so a weekly cron > could remind me of tables that are already a few days old but have no explanatory comment) > > I am running PG 9.3 > > Best regards > Wolfgang Hamann > > > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
>> 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) b) a dump/restore will modify the dates best regards Wolfgang Hamann
On Wednesday, August 30, 2017, <hamann.w@t-online.de> wrote:
Hi,
is there a way to add a table create (and perhaps schema modify) timestamp to the system?
There is not. You may wish to search the archives for discussions as to why previous requests for this feature have not resulted in patches.
David J.
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. > 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
On Thu, Aug 31, 2017 at 8:29 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, August 30, 2017, <hamann.w@t-online.de> wrote:
Hi,
is there a way to add a table create (and perhaps schema modify) timestamp to the system?There is not. You may wish to search the archives for discussions as to why previous requests for this feature have not resulted in patches.David J.
Wolfgang, as David said, a column in pg_class for the creation time of a table does not exist. I long ago requested that feature as it is
in other DB's (Oracle & MS SQL Server), but the main reason that it was not done was that no one was interested in doing it.
As for finding tables with no comments, you can use the following query:
SELECT DISTINCT ON (n.nspname, c.relname)
n.nspname as schema,
c.relname,
a.rolname as owner,
d.description as comment
FROM pg_class c
LEFT JOIN pg_attribute col ON (col.attrelid = c.oid)
LEFT JOIN pg_description d ON (d.objoid = col.attrelid AND d.objsubid = 0)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE n.nspname NOT LIKE 'information%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind = 'r'
AND d.description IS NULL
ORDER BY 1, 2;
n.nspname as schema,
c.relname,
a.rolname as owner,
d.description as comment
FROM pg_class c
LEFT JOIN pg_attribute col ON (col.attrelid = c.oid)
LEFT JOIN pg_description d ON (d.objoid = col.attrelid AND d.objsubid = 0)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE n.nspname NOT LIKE 'information%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind = 'r'
AND d.description IS NULL
ORDER BY 1, 2;
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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
On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6925@gmail.com> wrote: > Wolfgang, as David said, a column in pg_class for the creation time of a table does not exist. I long ago requested thatfeature as it is > in other DB's (Oracle & MS SQL Server), but the main reason that it was not done was that no one was interested in doingit. Is there any need for a column in pg_class for that? You could just create an event trigger looking for CREATE TABLE as filter_value: https://www.postgresql.org/docs/9.6/static/sql-createeventtrigger.html And then have this event trigger just save the timestamp value of now() in a custom table with the name and/or OID of the relation involved. -- Michael
Melvin Davidson <melvin6925@gmail.com> writes: > Wolfgang, as David said, a column in pg_class for the creation time of a > table does not exist. I long ago requested that feature as it is > in other DB's (Oracle & MS SQL Server), but the main reason that it was not > done was that no one was interested in doing it. No, I'd say the big reason it hasn't happened is lack of consensus on details of how the feature should work --- notably, ought the creation date be preserved across a dump/restore? Doing that would require exposing some method to set the creation date from SQL, whereupon it loses value for some purposes because you can never be sure that what you're looking at is the "real" date and not something somebody frobbed later. OTOH, losing all your creation date info during dump/restore is annoying too. regards, tom lane
>you could just create an event trigger looking for CREATE TABLE as filter_value:
I have tried that. Unfortunately, I have been unable to extract the table name from the event because TG_TABLE_NAME is not available during an event trigger, albeit perhaps I am missing something?
That being said, I still believe it is extra work that could easily be avoided and should be added to the postgresql catalogs simply to beOn Thu, Aug 31, 2017 at 10:54 AM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
> Wolfgang, as David said, a column in pg_class for the creation time of a table does not exist. I long ago requested that feature as it is
> in other DB's (Oracle & MS SQL Server), but the main reason that it was not done was that no one was interested in doing it.
Is there any need for a column in pg_class for that? You could just
create an event trigger looking for CREATE TABLE as filter_value:
https://www.postgresql.org/docs/9.6/static/sql- createeventtrigger.html
And then have this event trigger just save the timestamp value of
now() in a custom table with the name and/or OID of the relation
involved.
--
Michael
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Fri, Sep 1, 2017 at 12:20 AM, Melvin Davidson <melvin6925@gmail.com> wrote: > >you could just create an event trigger looking for CREATE TABLE as filter_value: > > I have tried that. Unfortunately, I have been unable to extract the table name from the event because TG_TABLE_NAME isnot > available during an event trigger, albeit perhaps I am missing something? The function pg_event_trigger_ddl_commands() returns classid and objid, which should map to respectively pg_class and the relation created for a CREATE TABLE query, no? -- Michael
On 31/08/2017 18:20, Melvin Davidson wrote:
You need to use ddl_command_end event and then select from pg_event_trigger_ddl_commands() . Search for some example how to do this.>you could just create an event trigger looking for CREATE TABLE as filter_value:I have tried that. Unfortunately, I have been unable to extract the table name from the event because TG_TABLE_NAME is notavailable during an event trigger, albeit perhaps I am missing something?
That being said, I still believe it is extra work that could easily be avoided and should be added to the postgresql catalogs simply to bemore feature competitive with Oracle & SQL Server, as well as a boost to the PostgreSQL community.On Thu, Aug 31, 2017 at 10:54 AM, Michael Paquier <michael.paquier@gmail.com> wrote:On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
> Wolfgang, as David said, a column in pg_class for the creation time of a table does not exist. I long ago requested that feature as it is
> in other DB's (Oracle & MS SQL Server), but the main reason that it was not done was that no one was interested in doing it.
Is there any need for a column in pg_class for that? You could just
create an event trigger looking for CREATE TABLE as filter_value:
https://www.postgresql.org/docs/9.6/static/sql- createeventtrigger.html
And then have this event trigger just save the timestamp value of
now() in a custom table with the name and/or OID of the relation
involved.
--
Michael
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
On Thu, Aug 31, 2017 at 11:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Melvin Davidson <melvin6925@gmail.com> writes:
> Wolfgang, as David said, a column in pg_class for the creation time of a
> table does not exist. I long ago requested that feature as it is
> in other DB's (Oracle & MS SQL Server), but the main reason that it was not
> done was that no one was interested in doing it.
No, I'd say the big reason it hasn't happened is lack of consensus on
details of how the feature should work --- notably, ought the creation
date be preserved across a dump/restore? Doing that would require
exposing some method to set the creation date from SQL, whereupon it loses
value for some purposes because you can never be sure that what you're
looking at is the "real" date and not something somebody frobbed later.
OTOH, losing all your creation date info during dump/restore is annoying
too.
regards, tom lane
>lack of consensus on details of how the feature should work --- notably, ought the creation
>date be preserved across a dump/restore?
Tom, I have already countered that there is no need to preserve a creation date on dump/restore.
A. Only the creation time of an object is tracked, not mods.
B. When doing a dump, the catalogs (and relcreated time) are NOT included in the dump.
C. When restoring an object that was deleted, or to a new database, the
relcreated = now();
D. When restoring data to an existing object, relcreated is not/cannot be updated.
B. When doing a dump, the catalogs (and relcreated time) are NOT included in the dump.
C. When restoring an object that was deleted, or to a new database, the
relcreated = now();
D. When restoring data to an existing object, relcreated is not/cannot be updated.
The _only_ time it would be a factor is restoring to a _new_ database. In that case, C (from above) takes effect, as new tables/objects
are being created.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Thu, Aug 31, 2017 at 11:26 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 31/08/2017 18:20, Melvin Davidson wrote:You need to use ddl_command_end event and then select from pg_event_trigger_ddl_commands(>you could just create an event trigger looking for CREATE TABLE as filter_value:I have tried that. Unfortunately, I have been unable to extract the table name from the event because TG_TABLE_NAME is notavailable during an event trigger, albeit perhaps I am missing something?) . Search for some example how to do this. That being said, I still believe it is extra work that could easily be avoided and should be added to the postgresql catalogs simply to bemore feature competitive with Oracle & SQL Server, as well as a boost to the PostgreSQL community.On Thu, Aug 31, 2017 at 10:54 AM, Michael Paquier <michael.paquier@gmail.com> wrote:On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
> Wolfgang, as David said, a column in pg_class for the creation time of a table does not exist. I long ago requested that feature as it is
> in other DB's (Oracle & MS SQL Server), but the main reason that it was not done was that no one was interested in doing it.
Is there any need for a column in pg_class for that? You could just
create an event trigger looking for CREATE TABLE as filter_value:
https://www.postgresql.org/docs/9.6/static/sql-createeventtr igger.html
And then have this event trigger just save the timestamp value of
now() in a custom table with the name and/or OID of the relation
involved.
--
Michael
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
>You need to use ddl_command_end event and then select from pg_event_trigger_ddl_commands( )
I have, but the result for CREATE TABLE is an error.
CREATE OR REPLACE FUNCTION public.fn_notify_ddl()
RETURNS event_trigger AS
$BODY$
DECLARE
obj record;
BEGIN
RAISE NOTICE 'Type: %', TG_TAG;
RAISE NOTICE 'Command: %', current_query();
RAISE NOTICE 'Table: %', (pg_event_trigger_ddl_commands()).objid; -- < causes an error?
RAISE NOTICE 'DB Name: %', current_database();
RAISE NOTICE 'DB User: %', session_user;
RAISE NOTICE 'DB Port: %', inet_server_port();
RAISE NOTICE 'Server Host: %', inet_server_addr();
RAISE NOTICE 'Client Host: %', inet_client_addr();
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.fn_notify_ddl()
OWNER TO postgres;
RETURNS event_trigger AS
$BODY$
DECLARE
obj record;
BEGIN
RAISE NOTICE 'Type: %', TG_TAG;
RAISE NOTICE 'Command: %', current_query();
RAISE NOTICE 'Table: %', (pg_event_trigger_ddl_commands()).objid; -- < causes an error?
RAISE NOTICE 'DB Name: %', current_database();
RAISE NOTICE 'DB User: %', session_user;
RAISE NOTICE 'DB Port: %', inet_server_port();
RAISE NOTICE 'Server Host: %', inet_server_addr();
RAISE NOTICE 'Client Host: %', inet_client_addr();
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.fn_notify_ddl()
OWNER TO postgres;
( id_col varchar(5),
col2 varchar(1),
CONSTRAINT sneaky_pete_pk PRIMARY KEY (id_col)
);
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Thu, Aug 31, 2017 at 11:46 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
On Thu, Aug 31, 2017 at 11:26 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/08/2017 18:20, Melvin Davidson wrote:You need to use ddl_command_end event and then select from pg_event_trigger_ddl_commands(>you could just create an event trigger looking for CREATE TABLE as filter_value:I have tried that. Unfortunately, I have been unable to extract the table name from the event because TG_TABLE_NAME is notavailable during an event trigger, albeit perhaps I am missing something?) . Search for some example how to do this. That being said, I still believe it is extra work that could easily be avoided and should be added to the postgresql catalogs simply to bemore feature competitive with Oracle & SQL Server, as well as a boost to the PostgreSQL community.On Thu, Aug 31, 2017 at 10:54 AM, Michael Paquier <michael.paquier@gmail.com> wrote:On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
> Wolfgang, as David said, a column in pg_class for the creation time of a table does not exist. I long ago requested that feature as it is
> in other DB's (Oracle & MS SQL Server), but the main reason that it was not done was that no one was interested in doing it.
Is there any need for a column in pg_class for that? You could just
create an event trigger looking for CREATE TABLE as filter_value:
https://www.postgresql.org/docs/9.6/static/sql-createeventtr igger.html
And then have this event trigger just save the timestamp value of
now() in a custom table with the name and/or OID of the relation
involved.
--
Michael
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt>You need to use ddl_command_end event and then select from pg_event_trigger_ddl_commands() I have, but the result for CREATE TABLE is an error.CREATE OR REPLACE FUNCTION public.fn_notify_ddl()
RETURNS event_trigger AS
$BODY$
DECLARE
obj record;
BEGIN
RAISE NOTICE 'Type: %', TG_TAG;
RAISE NOTICE 'Command: %', current_query();
RAISE NOTICE 'Table: %', (pg_event_trigger_ddl_commands()).objid; -- < causes an error?
RAISE NOTICE 'DB Name: %', current_database();
RAISE NOTICE 'DB User: %', session_user;
RAISE NOTICE 'DB Port: %', inet_server_port();
RAISE NOTICE 'Server Host: %', inet_server_addr();
RAISE NOTICE 'Client Host: %', inet_client_addr();
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.fn_notify_ddl()
OWNER TO postgres;CREATE TABLE sneaky_pete
( id_col varchar(5),
col2 varchar(1),
CONSTRAINT sneaky_pete_pk PRIMARY KEY (id_col)
);ERROR: query "SELECT (pg_event_trigger_ddl_commands()).objid" returned more than one row
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Wolfgang,
Kudos to Álvaro Herrera! Thanks to his slide presentation Capturing DDL Events
I was able to figure out how to trap and log table creates. See the attached log_tbl_cre8.sql for my solution
that I am passing on to help you and others.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
