Thread: BUG #15359: event_trigger via pg_event_trigger_ddl_commands() notreturning "CREATE SEQUENCE" command

The following bug has been logged on the website:

Bug reference:      15359
Logged by:          Tarek Heiland
Email address:      tarek_heiland@debortoli.com.au
PostgreSQL version: 9.6.9
Operating system:   windows/linux
Description:

1) Event Trigger Function (redacted to raise notice only for proscribed
events)

CREATE OR REPLACE FUNCTION admin.trg_create_set_owner()
  RETURNS event_trigger AS
$BODY$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() 
    WHERE command_tag in ('CREATE TABLE','CREATE TABLE AS','CREATE SEQUENCE')
LOOP
         RAISE  NOTICE 'event  % for object %',obj.command_tag,
obj.object_identity;
    
  END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION admin.trg_create_set_owner()
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION admin.trg_create_set_owner() TO public;

2) Test with CREATE TABLE

CREATE TABLE test (id integer);

NOTICE:  event  CREATE TABLE for object historian.test
CONTEXT:  PL/pgSQL function admin.trg_create_set_owner() line 7 at RAISE

Query returned successfully with no result in 11 msec

3) Test with CREATE SEQUENCE

CREATE SEQUENCE test_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Query returned successfully with no result in 11 msec.

No event raised.

4) https://www.postgresql.org/docs/9.6/static/event-trigger-matrix.html
indicates the CREATE SEQUENCE is supported in this context and
https://www.postgresql.org/docs/9.6/static/event-trigger-definition.html
indicates pg_event_trigger_ddl_commands() is the correct function to call.



Regards

Tarek

On 2018-Aug-30, PG Bug reporting form wrote:

> 3) Test with CREATE SEQUENCE
> 
> CREATE SEQUENCE test_seq
>     START WITH 1
>     INCREMENT BY 1
>     NO MINVALUE
>     NO MAXVALUE
>     CACHE 1;
> 
> Query returned successfully with no result in 11 msec.
> 
> No event raised.

Works for me, after adding the CREATE EVENT TRIGGER command that you did
not list.  (Without it, it doesn't emit the notice for CREATE TABLE
either, obviously.)

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> 1) Event Trigger Function (redacted to raise notice only for
 PG> proscribed events)

I don't see the CREATE EVENT TRIGGER command included in your test case?

When I do your test with the addition of:

CREATE EVENT TRIGGER foo ON ddl_command_end
  EXECUTE PROCEDURE admin.trg_create_set_owner();

then I see the NOTICE message for both table and sequence creation:

postgres=# CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
NOTICE:  event  CREATE SEQUENCE for object public.test_seq
CREATE SEQUENCE

-- 
Andrew (irc:RhodiumToad)