Horton

Thread

Commits

  1. Doc: Fix event trigger firing table

  1. BUG #15883: Event Trigger Firing Matrix Table is incomplete

    The Post Office <noreply@postgresql.org> — 2019-07-02T14:57:50Z

    The following bug has been logged on the website:

Bug reference:      15883
Logged by:          Jeremy Smith
Email address:      jeremy@musicsmith.net
PostgreSQL version: 11.4
Operating system:   All
Description:        

The event trigger firing matrix table in the documentation:
https://www.postgresql.org/docs/11/event-trigger-matrix.html#EVENT-TRIGGER-BY-COMMAND-TAG
claims to list all commands for which event triggers are supported.

However, there are several triggers in the source code
(https://github.com/postgres/postgres/blob/REL_11_STABLE/src/backend/commands/event_trigger.c#L290)
that are not listed in this table.

They are:
 - REFRESH MATERIALIZED VIEW
 - ALTER DEFAULT PRIVILEGES
 - ALTER LARGE OBJECT 
 - DROP OWNED
 - IMPORT FOREIGN SCHEMA

I have tried creating a trigger on refresh materialized view, which seems to
work, so I believe this is a documentation issue:

CREATE TABLE public.mview_refresh_log(
  mview regclass PRIMARY KEY,
  last_refresh timestamp without time zone
);

CREATE OR REPLACE FUNCTION public.log_mview_refresh()
  RETURNS event_trigger AS
$$
BEGIN
  INSERT INTO mview_refresh(mview, last_refresh)
  SELECT objid, now()
  FROM pg_event_trigger_ddl_commands()
  ON CONFLICT (mview) DO UPDATE SET last_refresh = EXCLUDED.last_refresh;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER log_mview_refresh_trig
ON ddl_command_end
WHEN TAG IN ('REFRESH MATERIALIZED VIEW')
EXECUTE FUNCTION log_mview_refresh();

  2. Re: BUG #15883: Event Trigger Firing Matrix Table is incomplete

    Michael Paquier <michael@paquier.xyz> — 2019-07-28T10:45:55Z

    Hi Jeremy,

On Tue, Jul 02, 2019 at 02:57:50PM +0000, PG Bug reporting form wrote:
> The event trigger firing matrix table in the documentation:
> https://www.postgresql.org/docs/11/event-trigger-matrix.html#EVENT-TRIGGER-BY-COMMAND-TAG
> claims to list all commands for which event triggers are supported.

Sorry for the late reply.

> They are:
>  - REFRESH MATERIALIZED VIEW
>  - ALTER DEFAULT PRIVILEGES
>  - ALTER LARGE OBJECT 

There are more missing entries:
CREATE MATERIALIZED VIEW (missing for ages)
DROP MATERIALIZED VIEW (missing for ages)
ALTER MATERIALIZED VIEW (missing for ages)
CREATE ACCESS METHOD (down to 9.6)
DROP ACCESS METHOD (down to 9.6)
CREATE PROCEDURE (down to 11)
DROP PROCEDURE (down to 11)
CREATE PUBLICATION (down to 10)
ALTER PUBLICATION (down to 10)
DROP PUBLICATION (down to 10)
CREATE SUBSCRIPTION (down to 10)
ALTER SUBSCRIPTION (down to 10)
DROP SUBSCRIPTION (down to 10)
ALTER STATISTICS (down to 10)

>  - DROP OWNED
>  - IMPORT FOREIGN SCHEMA

These two ones are correctly listed in the table.

If you use this set of event triggers it is easy enough to check all
of them:
CREATE OR REPLACE FUNCTION log_any_command()
  RETURNS event_trigger
  LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE NOTICE 'command % for event %', tg_tag, tg_event;
END;
$$;
CREATE EVENT TRIGGER ddl_start ON ddl_command_start
   EXECUTE FUNCTION log_any_command();
CREATE EVENT TRIGGER ddl_end ON ddl_command_end
   EXECUTE FUNCTION log_any_command();
CREATE EVENT TRIGGER ddl_drop ON sql_drop
   EXECUTE FUNCTION log_any_command();
CREATE EVENT TRIGGER ddl_rewrite ON table_rewrite
   EXECUTE FUNCTION log_any_command();

I'll go fix all the ones I have noticed, down to where they apply,
thanks!
--
Michael

  3. Re: BUG #15883: Event Trigger Firing Matrix Table is incomplete

    Michael Paquier <michael@paquier.xyz> — 2019-07-28T13:04:51Z

    On Sun, Jul 28, 2019 at 07:45:55PM +0900, Michael Paquier wrote:
> I'll go fix all the ones I have noticed, down to where they apply,
> thanks!

And done as of 44460d7 and friends.
--
Michael