Table create time

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 of the 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

--
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

--
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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 some message in a log file. This should survive dump/restores .

best regards
Wolfgang Hamann

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 NULLORDER 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.

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. So yes 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 some message in a log file. This should survive dump/restores .

best regards
Wolfgang Hamann

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 be
more 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.

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 is not
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

--
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 18:20, Melvin Davidson 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 is not
available during an event trigger, albeit perhaps I am missing something?

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.

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 be
more 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 <mailto:michael.paquier@gmail.com>> wrote:

On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6925@gmail.com <mailto: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 <https://www.postgresql.org/docs/9.6/static/sql-createeventtrigger.html&gt;
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.

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.

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 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?

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.

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 be
more 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

*>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.

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 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?

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.

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 be
more 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

*>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
<https://pgday.ru/files/papers/22/pgday.2015.alvaro.herrera.capturing-ddl.pdf&gt;*
*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.