Thread: [BUGS] BUG #14815: event trigger in extension

The following bug has been logged on the website:

Bug reference:      14815
Logged by:          Galiev Mansur
Email address:      gomer94@yandex.ru
PostgreSQL version: 9.6.5
Operating system:   Ubuntu 16.04.3 LTS x64
Description:

I want to create extension with sql like:

CREATE OR REPLACE FUNCTION keep_any_ddl_command() RETURNS event_trigger   LANGUAGE plpgsql   AS $$   BEGIN       SELECT
classid,objid, objsubid FROM 
pg_event_trigger_ddl_commands();
       -- some insert code;   END;
$$;

CREATE TABLE ddl_events();

CREATE EVENT TRIGGER keep_all_ddl ON ddl_command_end  EXECUTE PROCEDURE keep_any_ddl_command();



but when i'm use CREATE EXTENSION i have:

ERROR:  pg_event_trigger_ddl_commands() can only be called in an event
trigger function
CONTEXT:  SQL statement "SELECT classid, objid, objsubid FROM
pg_event_trigger_ddl_commands()"
PL/pgSQL function keep_any_ddl_command() line 3 at SQL statement

if create this objects without extension, all ok 



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

gomer94@yandex.ru writes:
> I want to create extension with sql like:

> CREATE OR REPLACE FUNCTION keep_any_ddl_command() RETURNS event_trigger
>     LANGUAGE plpgsql
>     AS $$
>     BEGIN
>         SELECT classid, objid, objsubid FROM
> pg_event_trigger_ddl_commands();

>         -- some insert code;
>     END;
> $$;

> CREATE TABLE ddl_events();

> CREATE EVENT TRIGGER keep_all_ddl ON ddl_command_end
>    EXECUTE PROCEDURE keep_any_ddl_command();

I think the reason this fails is that on the way out of the CREATE
EXTENSION command, the event trigger code sees that an event trigger
exists, so it tries to call it.  But because no event trigger existed
when we started the CREATE EXTENSION command, the necessary
infrastructure hasn't been set up --- in particular,
EventTriggerBeginCompleteQuery did nothing at the time.

This seems like it would be an equal hazard for any situation where
CREATE EVENT TRIGGER is executed not at top level.  Don't see an
easy fix right offhand.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Sun, Sep 17, 2017 at 4:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think the reason this fails is that on the way out of the CREATE
> EXTENSION command, the event trigger code sees that an event trigger
> exists, so it tries to call it.  But because no event trigger existed
> when we started the CREATE EXTENSION command, the necessary
> infrastructure hasn't been set up --- in particular,
> EventTriggerBeginCompleteQuery did nothing at the time.

That's rather tricky, the failure happens when doing the CREATE EVENT
TRIGGER command within the CREATE EXTENSION command which is done
within ProcessUtilitySlow. I am wondering if a somewhat-elegant way to
solve such problems would be to introduce a function able to
temporarily disable and enable event triggers to happen in the context
of an extension creation, in a design similar to
pg_extension_config_dump.
-- 
Michael


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs