Re: Table create time

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Thu, Aug 31, 2017 at 11:46 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Thu, Aug 31, 2017 at 11:26 AM, Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxx> wrote:

On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6925@xxxxxxxxx> 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

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.

CREATE SEQUENCE public.log_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.log_seq
  OWNER TO postgres;

DROP TABLE public.tbl_create_log
CREATE TABLE public.tbl_create_log
(
  log_id  bigint NOT NULL DEFAULT nextval('log_seq'::regclass),
  log_table_schema name NOT NULL,
  log_table_name name NOT NULL,
  log_table_cre8_time timestamp without time zone NOT NULL DEFAULT clock_timestamp(),
  CONSTRAINT tbl_create_log_pk PRIMARY KEY (log_table_schema, log_table_name)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.tbl_create_log
  OWNER TO postgres;


-- Function: public.fn_notify_ddl()

-- DROP FUNCTION public.fn_notify_ddl();

CREATE OR REPLACE FUNCTION public.fn_notify_ddl()
  RETURNS event_trigger AS
$BODY$
DECLARE
	obj record;
BEGIN

    FOR obj IN SELECT * 
                 FROM pg_event_trigger_ddl_commands() LOOP
	IF obj.command_tag = 'CREATE TABLE' THEN
          RAISE INFO 'we got a % event for object "%"', obj.command_tag, obj.object_identity;
          INSERT INTO tbl_create_log
          (log_table_schema, log_table_name)
          SELECT n.nspname, c.relname
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
           WHERE c.oid = obj.objid
             AND c.relkind = 'r';
        END IF;
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.fn_notify_ddl()
  OWNER TO postgres;


CREATE EVENT TRIGGER tg_event_audit_all ON ddl command end 
  EXECUTE PROCEDURE public.fn_notify_ddl();  
  
 -- Now test the event & trigger function
CREATE TABLE public.sneaky_pete
(
  pk_col character varying(5) NOT NULL,
  col2 character varying(10),
  CONSTRAINT sneaky_pete_pk PRIMARY KEY (pk_col)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.sneaky_pete
  OWNER TO postgres;

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