Postgres=# ALTER TABLE testtable DROP COLUMN middle_name Postgres=# ALTER TABLE testtable RENAME COLUMN midlname TO middle_name Postgres=# ALTER TABLE testtable ADD COLUMN midlname text Postgres=# ALTER TABLE testtable ADD COLUMN last_name text Once these triggers are in place, we can proceed to test with our DDL: postgres=# CREATE TABLE testtable (id int, first_name text) Once these two functions are created, we create the event triggers: CREATE EVENT TRIGGER log_ddl_info ON ddl_command_end EXECUTE PROCEDURE log_ddl() ĬREATE EVENT TRIGGER log_ddl_drop_info ON sql_drop EXECUTE PROCEDURE log_ddl_drop() INSERT INTO ddl_history (ddl_date, ddl_tag, object_name) VALUES (statement_timestamp(), tg_tag, r.object_identity) ĬREATE OR REPLACE FUNCTION log_ddl_drop()įOR r IN SELECT * FROM pg_event_trigger_ddl_commands() Then, we need two functions: one to track the creation and alteration of objects, and another to track drops of objects: CREATE OR REPLACE FUNCTION log_ddl() To leverage this, we first create a table that will store all the DDL history: CREATE TABLE ddl_history ( With event triggers, there is now a mechanism to update a table when DDL occurs. As such, the “pg_class” table needs to be frequently polled if a running history of changes is to be available there’s no automated way to generate this table. However, the problem with detecting DDL in this way is that the alterations to the table are tracked based on changes to the “xmin” value, and therefore only the **latest** DDL event is recorded. By checking the “xmin” value of the row belonging to the table, users can see when the last DDL was performed. Under the hood, when DDL is performed on a table, the table’s entry in “pg_class” is updated. Postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname = 'students' Postgres=# alter table students add column phone_number text Postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname = students Postgres=# create table students (id int, name text) This paved the way for DDL tracking by peering into changes to the “pg_class” table: postgres=# show track_commit_timestamp 9.1, “track_commit_timestamp” became an available option for those who wanted to have a timestamp associated with their transactions. Another old way: Tracking commit timestamps Any attempt to parse the logs would be time-consuming and tedious, not to mention that with log rotation some DDL history could be lost. The primary drawback to this mechanism is that-especially for a very busy database with lots of DML logging-a DDL statement would be buried in the logs. Prior to the implementation of event triggers, DDL could be detected and monitored by setting “log_statement = ‘ddl’” (or “log_statement = ‘all’”), which would in turn record the DDL statement into the PostgreSQL logs. With the concept of event triggers introduced in v. One of the missing features in PostgreSQL’s implementation of triggers was that DDL could not be detected very reliably. For example, triggers can be used to detect changes on one table and perform related changes on another table a common form of this usage is a centralized audit table, wherein all changes to relevant tables are monitored and then recorded to the audit table with triggers. PostgreSQL provides a very useful event-based trigger system to detect changes to data and automatically perform subsequent tasks. It covers differences in using event triggers in earlier and more recent versions of PostgreSQL:Ģ. SUMMARY: This article covers event triggers in PostgreSQL and how to use them.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |