On 7th of December, Simon Riggs committed patch:
Event Trigger for table_rewrite Generate a table_rewrite event when ALTER TABLE attempts to rewrite a table. Provide helper functions to identify table and reason. Intended use case is to help assess or to react to schema changes that might hold exclusive locks for long periods. Dimitri Fontaine, triggering an edit by Simon Riggs Reviewed in detail by Michael Paquier
In 9.3 we got event triggers. Their functionality grew over time with new things.
The newest addition is ability to run trigger when rewrite of table happens.
Let's see how it works.
Let's create some test table:
$ CREATE TABLE test (i int4 NOT NULL); CREATE TABLE
Now, let's create trigger on rewrite (I used code of the trigger from docs):
$ CREATE FUNCTION test_trg() RETURNS event_trigger LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'rewriting table % for reason %', pg_event_trigger_table_rewrite_oid()::regclass, pg_event_trigger_table_rewrite_reason(); END; $$; $ CREATE EVENT TRIGGER test_trg ON table_rewrite EXECUTE PROCEDURE test_trg();
And now let's make pg rewrite the table. For example by changing datatype:
$ ALTER TABLE test ALTER COLUMN i TYPE text; NOTICE: rewriting TABLE test FOR reason 4 ALTER TABLE
Currently, from what I understand, there are four potential codes that can be used:
- 1 – AT_REWRITE_ALTER_PERSISTENCE
- 2 – AT_REWRITE_DEFAULT_VAL
- 4 – AT_REWRITE_COLUMN_REWRITE
- 8 – AT_REWRITE_ALTER_OID
So, let's test the values. First – AT_REWRITE_ALTER_PERSISTENCE:
$ ALTER TABLE test SET unlogged; NOTICE: rewriting TABLE test FOR reason 1 ALTER TABLE
$ ALTER TABLE test ADD COLUMN j text DEFAULT 'yy'; NOTICE: rewriting TABLE test FOR reason 2 ALTER TABLE
and the last one, AT_REWRITE_ALTER_OID:
$ ALTER TABLE test SET WITH OIDS; NOTICE: rewriting TABLE test FOR reason 8 ALTER TABLE
Of course we can combine several changes:
$ ALTER TABLE test SET WITHOUT OIDS, ADD COLUMN k int4 DEFAULT 123, ALTER COLUMN z TYPE int8, SET LOGGED; NOTICE: rewriting TABLE test FOR reason 15 ALTER TABLE
Docs say that the reason codes are version-dependent, but I think these should be pretty stable. All in all – looks definitely cool.