Re: Command Triggers, patch v11 - Mailing list pgsql-hackers

On 4 March 2012 15:50, Andres Freund <andres@anarazel.de> wrote:
> Ok, I rebased my patch ontop of dim's current HEAD. There was only one trivial
> conflict in tablecmds.h. I had written the patch independently of the command
> triggers stuff because I though, and still do, that would make applying it
> easier.
>
> Attached are two versions of the patch, one based on command triggers and one
> without. Both pass regression tests for me.

I have conducted testing against Dimitri’s latest patch, along with
the incremental patch to fix the build, and also Andres’ CTAS patch.
I've attached a copy of how I configured the command triggers
(command_trigger_test_setup.txt), and also the set of tests I've been
running against the changes (command_trigger_regression.sql).  I've
left comments in that last file where I haven't been able to conduct a
test for particular commands.

Creating a command trigger using ANY COMMAND results in oid,
schemaname, objectname (function parameters 4 & 5) not being set for
either BEFORE or AFTER.

There is no support for ALTER CONVERSION.

When trying to create an AFTER command trigger on CREATE INDEX, I get
the warning:

WARNING:  CREATE INDEX CONCURRENTLY is not supported
DETAIL:  The command trigger will not get fired.

This should probably say that it’s not supported on AFTER command
triggers yet rather than the general DDL itself.

Command triggers for AFTER creating rules don’t return OIDs.

thom@test=# CREATE RULE "_RETURN" AS
    ON SELECT TO test2
    DO INSTEAD
        SELECT 234 id, 'test'::text stuff;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='CREATE
RULE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='CREATE RULE'
objectid=<NULL> schemaname='public' objectname='_RETURN'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='CREATE RULE'
objectid=<NULL> schemaname='public' objectname='_RETURN'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='CREATE RULE'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
CREATE RULE


Command triggers for creating sequences don’t show the schema:

thom@test=# CREATE TEMP SEQUENCE test_seq2;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='CREATE
SEQUENCE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='CREATE SEQUENCE'
objectid=<NULL> schemaname='<NULL>' objectname='test_seq2'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='CREATE SEQUENCE'
objectid=25130 schemaname='<NULL>' objectname='test_seq2'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='CREATE
SEQUENCE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
CREATE SEQUENCE


Command triggers for AFTER creating extensions with IF NOT EXISTS
don’t fire, but do in the ANY COMMAND instance:

thom@test=# CREATE EXTENSION IF NOT EXISTS file_fdw;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='CREATE
EXTENSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='CREATE EXTENSION'
objectid=<NULL> schemaname='<NULL>' objectname='file_fdw'
NOTICE:  extension "file_fdw" already exists, skipping
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='CREATE
EXTENSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
CREATE EXTENSION


Command triggers on CREATE TEXT SEARCH DICTIONARY show the name as garbage:

thom@test=# CREATE TEXT SEARCH DICTIONARY test_stem (
test(#    TEMPLATE = snowball,
test(#    language = 'english', stopwords = 'english'
test(# );
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='CREATE TEXT
SEARCH DICTIONARY' objectid=<NULL> schemaname='<NULL>'
objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='CREATE TEXT SEARCH
DICTIONARY' objectid=<NULL> schemaname='thom' objectname='�Ч�l '
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='CREATE TEXT SEARCH
DICTIONARY' objectid=25139 schemaname='thom' objectname='�Ч�l '
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='CREATE TEXT
SEARCH DICTIONARY' objectid=<NULL> schemaname='<NULL>'
objectname='<NULL>'
CREATE TEXT SEARCH DICTIONARY

Command triggers for BEFORE CREATE TYPE (exluding ANY COMMAND) don’t
fire if the type isn’t created due to an error:

thom@test=# CREATE TYPE thom.type_test AS
   (a integer,
    b integer,
    c text);
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='CREATE
TYPE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ERROR:  type "type_test" already exists


The ANY COMMAND trigger fires on creating roles, but there’s no
corresponding allowance to create the trigger explicitly for creating
roles.


Command triggers for AFTER CREATE VIEW don’t show the schema:

thom@test=# CREATE VIEW view_test AS SELECT id, stuff FROM public.test;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='CREATE
VIEW' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='CREATE VIEW'
objectid=<NULL> schemaname='<NULL>' objectname='view_test'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='CREATE VIEW'
objectid=25155 schemaname='<NULL>' objectname='view_test'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='CREATE VIEW'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
CREATE VIEW


Command triggers for BEFORE and AFTER ALTER DOMAIN show a garbage name
and no schema when dropping a constraint:

thom@test=# ALTER DOMAIN us_postal_code DROP CONSTRAINT dummy_constraint;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
DOMAIN' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='ALTER DOMAIN'
objectid=25085 schemaname='<NULL>' objectname='�'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='ALTER DOMAIN'
objectid=25085 schemaname='<NULL>' objectname='�'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
DOMAIN' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER DOMAIN

Continuing with this same trigger, we do get a schema but a garbage
name for OWNER TO:

thom@test=# ALTER DOMAIN us_postal_code OWNER TO test;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
DOMAIN' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='ALTER DOMAIN'
objectid=25085 schemaname='public' objectname='�'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='ALTER DOMAIN'
objectid=25085 schemaname='public' objectname='�'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
DOMAIN' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER DOMAIN


When an ALTER EXTENSION fails to upgrade, the AFTER ANY COMMAND
trigger fires, but not command triggers specifically for ALTER
EXTENSION:

thom@test=# ALTER EXTENSION file_fdw UPDATE TO '1.0';
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
EXTENSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='ALTER EXTENSION'
objectid=25087 schemaname='<NULL>' objectname='file_fdw'
NOTICE:  version "1.0" of extension "file_fdw" is already installed
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
EXTENSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER EXTENSION


Same on ALTER EXTENSION, when failing to add a member, the BEFORE ANY
COMMAND trigger fires, but not the one specifically for ALTER
EXTENSION:

thom@test=# ALTER EXTENSION file_fdw ADD COLLATION en_gb_test;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
EXTENSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ERROR:  collation "en_gb_test" for encoding "UTF8" does not exist


Specific command triggers against ALTER FOREIGN TABLE (i.e. not ANY
COMMAND) for BEFORE and AFTER aren’t working when renaming columns:

thom@test=# ALTER FOREIGN TABLE test.dict2 RENAME COLUMN word TO words;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
TABLE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER TABLE'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER TABLE

Specific command triggers agains ALTER FUNCTION (i.e. not ANY COMMAND)
don’t fire for any changes except renaming, changing owner or changing
schema.  Everything else fails to trigger (cost, rows, setting
configuration parameters, setting strict, security invoker etc.).:

thom@test=# ALTER FUNCTION test.testfunc2() COST 77;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER FUNCTION
thom@test=# ALTER FUNCTION srf_test() ROWS 5;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER FUNCTION
thom@test=# ALTER FUNCTION srf_test() RESET ALL;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER FUNCTION
thom@test=# ALTER FUNCTION srf_test() SET work_mem TO '1MB';
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER FUNCTION



There doesn’t appear to be command trigger support for ALTER LARGE OBJECT.

Specific command triggers on ALTER SEQUENCE don’t fire:

thom@test=# ALTER SEQUENCE test_seq OWNER TO test;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
SEQUENCE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
SEQUENCE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER SEQUENCE


Specific command triggers on ALTER TABLE don’t fire for renaming columns:

thom@test=# ALTER TABLE testnew.test9 RENAME COLUMN stuff TO things;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
TABLE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER TABLE'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER TABLE


Command triggers on ALTER TYPE when changing owner produce a garbage name:

thom@test=# ALTER TYPE testnew.type_test OWNER TO test;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER TYPE'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='ALTER TYPE'
objectid=25170 schemaname='testnew' objectname='�'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='ALTER TYPE'
objectid=25170 schemaname='testnew' objectname='�'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER TYPE'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER TYPE

Also renaming attributes doesn’t fire specific triggers:

thom@test=# ALTER TYPE public.type_test2 RENAME ATTRIBUTE a TO z;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER TYPE'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER TYPE'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER TYPE


Specific command triggers on ALTER VIEW don’t fire for any type of change:

thom@test=# ALTER VIEW view_test OWNER TO test;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER VIEW'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER VIEW'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER VIEW
thom@test=# ALTER VIEW testnew.view_test2 ALTER COLUMN id SET DEFAULT 9;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER VIEW'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER VIEW'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER VIEW


Specific command triggers on DROP AGGREGATE don’t fire in the IF
EXISTS scenario if the target object doesn’t exist:

thom@test=# DROP AGGREGATE IF EXISTS avgtest2(bigint);
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='DROP
AGGREGATE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  aggregate avgtest2(pg_catalog.int8) does not exist, skipping
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='DROP
AGGREGATE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
DROP AGGREGATE

This appears to be a general thing, as the same occurs for DROP CAST
IF EXISTS and DROP COLLATION IF EXISTS.  These do, however, fire if
the object does exist.


When adding objects to an extension, then dropping the extension with
a cascade, the objects are dropped with it, but triggers aren’t fired
to the removal of those dependant objects:

thom@test=# ALTER EXTENSION file_fdw ADD TABLE dep_test;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
EXTENSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='ALTER EXTENSION'
objectid=25207 schemaname='<NULL>' objectname='file_fdw'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='ALTER EXTENSION'
objectid=25207 schemaname='<NULL>' objectname='file_fdw'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
EXTENSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
ALTER EXTENSION
thom@test=# DROP EXTENSION file_fdw CASCADE;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='DROP
EXTENSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='DROP EXTENSION'
objectid=25207 schemaname='<NULL>' objectname='file_fdw'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='DROP EXTENSION'
objectid=<NULL> schemaname='<NULL>' objectname='file_fdw'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='DROP
EXTENSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
DROP EXTENSION


Using DROP OWNED BY allows objects to be dropped without their
respective specific triggers firing.

thom@test=# \dt
              List of relations
 Schema |      Name       | Type  |   Owner
--------+-----------------+-------+-----------
 thom   | role_test_table | table | role_test
 thom   | seq_table       | table | test
(2 rows)

thom@test=# DROP OWNED BY role_test;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='DROP OWNED'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='DROP OWNED'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
DROP OWNED


Using DROP SCHEMA … CASACDE also allows objects to be dropped without
their respective specific triggers firing:

thom@test=# DROP SCHEMA test6 CASCADE;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='DROP
SCHEMA' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='DROP SCHEMA'
objectid=25250 schemaname='<NULL>' objectname='test6'
NOTICE:  drop cascades to table test6.test
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='DROP SCHEMA'
objectid=<NULL> schemaname='<NULL>' objectname='test6'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='DROP SCHEMA'
objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
DROP SCHEMA


Command triggers on all DROP commands for TEXT SEARCH
CONFIGURATION/DICTIONARY/PARSER/TEMPLATE show the schema name as the
relation name:

thom@test=# DROP TEXT SEARCH PARSER testnew.test_parser2;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='DROP TEXT
SEARCH PARSER' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='DROP TEXT SEARCH
PARSER' objectid=25265 schemaname='testnew' objectname='testnew'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='DROP TEXT SEARCH
PARSER' objectid=<NULL> schemaname='testnew' objectname='testnew'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='DROP TEXT
SEARCH PARSER' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>'
DROP TEXT SEARCH PARSER


Still no command triggers firing for CREATE TABLE AS:

thom@test=# CREATE TABLE ctas_test AS SELECT 1::int id, ''::text test;
CREATE TABLE AS

Or for SELECT * INTO... :

thom@test=# SELECT * INTO another_test FROM ctas_test;
SELECT INTO


Regards

--
Thom