Tracking object modification time using event triggers

From: Alexander Levsha <outcastatsabre(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: codekeeper(at)chelny(dot)taximaxim(dot)ru
Subject: Tracking object modification time using event triggers
Date: 2018-02-19 12:37:21
Message-ID: CA+FA-0rjkY1ptjFo-CiEKLOo91nzgbR+5YNaB7pFKKbZ=xWOyA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all.
I'm lead developer for pgCodeKeeper which is a tool for PostgreSQL database
schema comparison.

In our tool we have a pg_dump-like schema reader for comparing against live
DB instances.
This reader consumes majority of the time the comparison operation takes
and we had an idea to speed it up.
To do this we need to be able to track last modification time of every DB
object and an extension with event triggers seems like a suitable tool for
this.
The extension we've implemented is available, for anyone interested:
https://github.com/pgcodekeeper/pg_dbo_timestamp/

However, we've discovered that event triggers provide almost no data for
GRANT/REVOKE commands, in particular, there's no way to find out which
objects were altered by these commands.
pg_event_trigger_ddl_commands() does provide a pg_ddl_command data which
seems to contain objects list for GRANT, however it seems totally
inaccessible in plpgsql.

This leads to my question: do we need to dive into writing a C function for
our extension to access pg_ddl_command or some other lower-lever
representation? Or can we use something else to solve our task, maybe
avoiding event triggers entirely?

Thanks.
Alexander Levsha

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Verite 2018-02-19 13:21:47 Re: NEXT VALUE FOR sequence
Previous Message Justin Pryzby 2018-02-19 12:16:02 Re: [doc fix] Correct calculation of vm.nr_hugepages