Обсуждение: [GENERAL] Table create time
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.
>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 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.