CODEWITHSUNDEEP
sqlpostgresqltriggerspostgresql-9.4
Tajinder
Tajinder

Reputation: 2338

How to track changes in any function in PostgreSQL

I want track changes in any function in PostgreSQL.

Example - Suppose, i have function fun_name() in postgesql database and i am doing modifications in function.

Now, i want to track records like 

DateTime,Schema_name,Function_name,old_func_text,new_func_text

Please suggest the best possible way to achieve this in postgresql.

I am studying event triggers in https://www.postgresql.org/docs/9.3/static/sql-createeventtrigger.html

Thanks.

Upvotes: 4

Views: 3089

Answers (3)

Alexey
Alexey

Reputation: 1

Full version with drop and trigger traking

create schema hist;
create table hist.functions (
    datetime timestamp, 
    schema_name text, 
    function_name text, 
    oper char(1), 
    function_body text,
    ip text);

  create or replace function hist.on_function_event_hist()
    returns event_trigger
    language plpgsql
as $function$
begin

   insert into hist.functions
   select now(),
   coalesce(nspname, t.trigger_schema),
   coalesce(proname, t.trigger_name),
   command_tag::char(1),
   coalesce(pg_get_functiondef(p.oid),
   'create trigger ' || trigger_name || ' ' || action_timing || ' ' || event_manipulation ||
        E'\non\n' || quote_ident(event_object_schema) || '.' || quote_ident(event_object_table) || ' for ' || action_orientation || coalesce(E'\rwhen (' || action_condition || E')\r', E'\r')  || action_statement || ';'),
   coalesce(inet_client_addr()::text, '::1')
    from pg_event_trigger_ddl_commands() e
     left join pg_proc p on p.oid = e.objid
    left    join pg_namespace n on n.oid = pronamespace
    left join information_schema.triggers t on object_identity like t.trigger_name || '%';
end
$function$;

create event trigger on_function_event
on ddl_command_end
when tag in ('CREATE FUNCTION', 'ALTER FUNCTION', 'ALTER TRIGGER', 'CREATE TRIGGER')
execute procedure hist.on_function_event_hist();

  create or replace function hist.on_function_drop_func()
    returns event_trigger
    language plpgsql
as $function$
begin

   insert into hist.functions
   SELECT now(),
   schema_name,
   object_identity,
   'D',
   null,
    coalesce(inet_client_addr()::text, '::1')
    FROM pg_event_trigger_dropped_objects();
end
$function$;

CREATE EVENT TRIGGER on_function_drop
   ON sql_drop
   EXECUTE PROCEDURE hist.on_function_drop_func();

Upvotes: 0

klin
klin

Reputation: 121534

In Postgres 9.5 there is a function pg_event_trigger_ddl_commands() which can be used in an event trigger to get an oid of inserted/altered object.

Log table:

create table function_log (
    datetime timestamp, 
    schema_name text, 
    function_name text, 
    tag text, 
    function_body text);

Event function and trigger: 

create or replace function public.on_function_event()
    returns event_trigger
    language plpgsql
as $function$
begin
    insert into function_log
    select now(), nspname, proname, command_tag, prosrc
    from pg_event_trigger_ddl_commands() e
    join pg_proc p on p.oid = e.objid
    join pg_namespace n on n.oid = pronamespace;
end
$function$;

create event trigger on_function_event
on ddl_command_end 
when tag in ('CREATE FUNCTION', 'ALTER FUNCTION')
execute procedure on_function_event();

Example:

create or replace function test()
returns int as $$ select 1; $$ language sql;

create or replace function test()
returns int as $$ select 2; $$ language sql;

alter function test() immutable;

select *
from function_log;

          datetime          | schema_name | function_name |       tag       | function_body 
----------------------------+-------------+---------------+-----------------+---------------
 2017-02-26 13:05:15.353879 | public      | test          | CREATE FUNCTION |  select 1; 
 2017-02-26 13:05:15.353879 | public      | test          | CREATE FUNCTION |  select 2; 
 2017-02-26 13:05:15.353879 | public      | test          | ALTER FUNCTION  |  select 2; 
(3 rows)

You can add DROP FUNCTION command tag to the trigger and then use the function pg_event_trigger_dropped_objects() in analogous way to pg_event_trigger_ddl_commands().

Unfortunately, there is no pg_event_trigger_ddl_commands() in Postgres 9.4. You could try to get an inserted/altered object using current_query() or write a trigger function in C. I think the easier way would be to upgrade Postgres to 9.5+.

Upvotes: 8

Chris Travers
Chris Travers

Reputation: 26454

A few notes on constraints.

  1. You cannot put triggers on system tables in PostgreSQL so there s no way of creating an audit trail in that regard.
  2. You probably need to use something external to PostgreSQL to manage versioning. So I would recommend something like sqitch.

One major issue with pgdump is that ordering of things is not guaranteed to be unique so simply dump and version will give you a pretty lousy signal-to-noise ratio.

Now one thing you could do is use event triggers on ddl to capture the event and then log and handle. This still requires a lot of effort on the design side from you but would be entirely possible. If you go with event triggers, please thoroughly test on a staging system first!

I will say when I have needed to do things like this I have usually used event triggers and copies of the system tables so I update the table from the old catalog and log the changes.

Upvotes: 1

Related Questions