From 618c9430a82860c84a3be2711eec2c3b43573b2a Mon Sep 17 00:00:00 2001 From: Simon Riggs Date: Mon, 8 Dec 2014 00:55:28 +0900 Subject: 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 --- doc/src/sgml/event-trigger.sgml | 150 ++++++++++++++++++++++++++++++++++++++++ doc/src/sgml/func.sgml | 85 ++++++++++++++++++++--- 2 files changed, 227 insertions(+), 8 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml index 6f71a27855e..02199727720 100644 --- a/doc/src/sgml/event-trigger.sgml +++ b/doc/src/sgml/event-trigger.sgml @@ -64,6 +64,16 @@ system catalogs, so it's not possible to look them up anymore. + + The table_rewrite event occurs just before a table is + rewritten by the command ALTER TABLE. While other + control statements are available to rewrite a table, + like CLUSTER and VACUUM, + the table_rewrite event is currently only triggered by + the ALTER TABLE command, and only when that command + attempts to rewrite the table. + + Event triggers (like other functions) cannot be executed in an aborted transaction. Thus, if a DDL command fails with an error, any associated @@ -120,6 +130,7 @@ ddl_command_start ddl_command_end sql_drop + table_rewrite @@ -128,510 +139,595 @@ X X - + - ALTER COLLATION X X - + - ALTER CONVERSION X X - + - ALTER DOMAIN X X - + - ALTER EXTENSION X X - + - ALTER FOREIGN DATA WRAPPER X X - + - ALTER FOREIGN TABLE X X X + - ALTER FUNCTION X X - + - ALTER LANGUAGE X X - + - ALTER OPERATOR X X - + - ALTER OPERATOR CLASS X X - + - ALTER OPERATOR FAMILY X X - + - ALTER POLICY X X - + - ALTER SCHEMA X X - + - ALTER SEQUENCE X X - + - ALTER SERVER X X - + - ALTER TABLE X X X + X ALTER TEXT SEARCH CONFIGURATION X X - + - ALTER TEXT SEARCH DICTIONARY X X - + - ALTER TEXT SEARCH PARSER X X - + - ALTER TEXT SEARCH TEMPLATE X X - + - ALTER TRIGGER X X - + - ALTER TYPE X X - + - ALTER USER MAPPING X X - + - ALTER VIEW X X - + - CREATE AGGREGATE X X - + - CREATE CAST X X - + - CREATE COLLATION X X - + - CREATE CONVERSION X X - + - CREATE DOMAIN X X - + - CREATE EXTENSION X X - + - CREATE FOREIGN DATA WRAPPER X X - + - CREATE FOREIGN TABLE X X - + - CREATE FUNCTION X X - + - CREATE INDEX X X - + - CREATE LANGUAGE X X - + - CREATE OPERATOR X X - + - CREATE OPERATOR CLASS X X - + - CREATE OPERATOR FAMILY X X - + - CREATE POLICY X X - + - CREATE RULE X X - + - CREATE SCHEMA X X - + - CREATE SEQUENCE X X - + - CREATE SERVER X X - + - CREATE TABLE X X - + - CREATE TABLE AS X X - + - CREATE TEXT SEARCH CONFIGURATION X X - + - CREATE TEXT SEARCH DICTIONARY X X - + - CREATE TEXT SEARCH PARSER X X - + - CREATE TEXT SEARCH TEMPLATE X X - + - CREATE TRIGGER X X - + - CREATE TYPE X X - + - CREATE USER MAPPING X X - + - CREATE VIEW X X - + - DROP AGGREGATE X X X + - DROP CAST X X X + - DROP COLLATION X X X + - DROP CONVERSION X X X + - DROP DOMAIN X X X + - DROP EXTENSION X X X + - DROP FOREIGN DATA WRAPPER X X X + - DROP FOREIGN TABLE X X X + - DROP FUNCTION X X X + - DROP INDEX X X X + - DROP LANGUAGE X X X + - DROP OPERATOR X X X + - DROP OPERATOR CLASS X X X + - DROP OPERATOR FAMILY X X X + - DROP OWNED X X X + - DROP POLICY X X X + - DROP RULE X X X + - DROP SCHEMA X X X + - DROP SEQUENCE X X X + - DROP SERVER X X X + - DROP TABLE X X X + - DROP TEXT SEARCH CONFIGURATION X X X + - DROP TEXT SEARCH DICTIONARY X X X + - DROP TEXT SEARCH PARSER X X X + - DROP TEXT SEARCH TEMPLATE X X X + - DROP TRIGGER X X X + - DROP TYPE X X X + - DROP USER MAPPING X X X + - DROP VIEW X X X + - IMPORT FOREIGN SCHEMA X X - + - SELECT INTO X X - + - @@ -843,4 +939,58 @@ COMMIT; event triggers.) + + + A Table Rewrite Event Trigger Example + + + Thanks to the table_rewrite event, it is possible to implement + a table rewriting policy only allowing the rewrite in maintenance windows. + + + + Here's an example implementing such a policy. + +CREATE OR REPLACE FUNCTION no_rewrite() + RETURNS event_trigger + LANGUAGE plpgsql AS +$$ +--- +--- Implement local Table Rewriting policy: +--- public.foo is not allowed rewriting, ever +--- other tables are only allowed rewriting between 1am and 6am +--- unless they have more than 100 blocks +--- +DECLARE + table_oid oid := pg_event_trigger_table_rewrite_oid(); + current_hour integer := extract('hour' from current_time); + pages integer; + max_pages integer := 100; +BEGIN + IF pg_event_trigger_table_rewrite_oid() = 'public.foo'::regclass + THEN + RAISE EXCEPTION 'you''re not allowed to rewrite the table %', + table_oid::regclass; + END IF; + + SELECT INTO pages relpages FROM pg_class WHERE oid = table_oid; + IF pages > max_pages + THEN + RAISE EXCEPTION 'rewrites only allowed for table with less than % pages', + max_pages; + END IF; + + IF current_hour NOT BETWEEN 1 AND 6 + THEN + RAISE EXCEPTION 'rewrites only allowed between 1am and 6am'; + END IF; +END; +$$; + +CREATE EVENT TRIGGER no_rewrite_allowed + ON table_rewrite + EXECUTE PROCEDURE no_rewrite(); + + + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 62ec275a9e5..c3b61269438 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17607,15 +17607,23 @@ FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); Event Trigger Functions - - pg_event_trigger_dropped_objects - + + PostgreSQL provides these helper functions + to retrieve information from event triggers. + - Currently PostgreSQL provides one built-in event trigger - helper function, pg_event_trigger_dropped_objects. + For more information about event triggers, + see . + + Processing objects dropped by a DDL command. + + + pg_event_trigger_dropped_objects + + pg_event_trigger_dropped_objects returns a list of all objects dropped by the command in whose sql_drop event it is called. @@ -17709,11 +17717,72 @@ CREATE EVENT TRIGGER test_event_trigger_for_drops EXECUTE PROCEDURE test_event_trigger_for_drops(); + - - For more information about event triggers, - see . + + Handling a Table Rewrite Event + + + The functions shown in + + provide information about a table for which a + table_rewrite event has just been called. + If called in any other context, an error is raised. + + + + Table Rewrite information + + + Name Return Type Description + + + + + + pg_event_trigger_table_rewrite_oid + pg_event_trigger_table_rewrite_oid() + + Oid + The Oid of the table about to be rewritten. + + + + + pg_event_trigger_table_rewrite_reason + pg_event_trigger_table_rewrite_reason() + + int + + The reason code(s) explaining the reason for rewriting. The exact + meaning of the codes is release dependent. + + + + +
+ + + The pg_event_trigger_table_rewrite_oid function can be used + in an event trigger like this: + +CREATE FUNCTION test_event_trigger_table_rewrite_oid() + 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_table_rewrite_oid + ON table_rewrite + EXECUTE PROCEDURE test_event_trigger_table_rewrite_oid(); + + -- cgit v1.2.3