BUG #15359: event_trigger via pg_event_trigger_ddl_commands() not returning "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)