Waiting for 9.5 – Event Trigger for table_rewrite

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

next AT_REWRITE_DEFAULT_VAL

$ 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.