Command Triggers - Mailing list pgsql-hackers

Hi,

As proposed by Jan Wieck on hackers and discussed in Ottawa at the
Clusters Hackers Meeting, the most (only?) workable way to ever have DDL
triggers is to have "command triggers" instead.

Rather than talking about catalogs and the like, it's all about firing a
registered user-defined function before or after processing the utility
command, or instead of processing it.  This naturally involves a new
catalog (pg_cmdtrigger) and some new subcommands of CREATE and DROP
TRIGGER, and some support code for calling the function at the right
time.

The right place to hook this code in seems to be ProcessUtility(), which
is a central place for about all the commands that we handle.  An
exception to that rule would be SELECT INTO and CREATE TABLE AS SELECT,
and my proposal would be to add specific call sites to the functions
I've provided in the attached patch rather than try to contort them into
being a good citizen as a utility command.

The ProcessUtility() integration currently looks like that:

    const char *commandTag = CreateCommandTag(parsetree);
    if (ExecBeforeOrInsteadOfCommandTriggers(parsetree, commandTag) > 0)
        return;

        ... current code ...

    ExecAfterCommandTriggers(parsetree, commandTag);

So I guess adding some call sites is manageable.


Now, the patch contains a Proof Of Concept implementation with a small
level of documentation and regression tests in order to get an agreement
on the principles that we already discussed.


The other part of the command trigger facility is what information we
should give to the user-defined functions, and in which form.  I've
settled on passing always 4 text arguments:  the command string, the
parse tree as a nodeToString() representation (Jan believes that this is
the easiest form we can provide for code consumption, and I tend to
agree), the schema name or NULL if the targeted object of the command is
not qualified, and the object name (or NULL if that does not apply).

    CREATE FUNCTION cmdtrigger_notice
     (
       IN cmd_string     text,
       IN cmd_nodestring text,
       IN schemaname     text,
       IN relname        text
     )
     RETURNS void
     LANGUAGE plpgsql
    AS $$
    BEGIN
      RAISE NOTICE 'cmd_string: %', cmd_string;
    END;
    $$;

    CREATE TRIGGER cmdtrigger_notice
             AFTER COMMAND CREATE TABLE
           EXECUTE PROCEDURE cmdtrigger_notice();

The v1 patch attached contains implementation for some commands only.
We need to add rewriting facilities for those commands we want to
support in the proposed model, because of multi-queries support in the
protocol and dynamic queries in EXECUTE e.g. (though I admit not having
had a look at EXECUTE implementation).

So each supported command has a cost, and the ones I claim to support in
the grammar in the patch are not seeing a complete support: first, I'm
missing some outfuncs and readfuncs (but I believe we can complete that
using a script on the source code) so that the cmd_nodestring argument
is currently always NULL. Second, I didn't complete the rewriting of
some more complex commands such as CREATE TABLE and ALTER TABLE.

Note that we can't reuse that much of ruleutils.c because it's written
to work from what we store in the catalogs rather than from a parsetree
object.


So, any comment on the approach before I complete the rewriting of the
commands, the out/read funcs, and add more commands to the trigger
support code?


  https://github.com/dimitri/postgres/compare/master...command_triggers


$ git diff --stat master..
 doc/src/sgml/ref/create_trigger.sgml       |   97 +++++-
 doc/src/sgml/ref/drop_trigger.sgml         |   19 +-
 src/backend/catalog/Makefile               |    2 +-
 src/backend/catalog/dependency.c           |   47 ++-
 src/backend/catalog/objectaddress.c        |    8 +
 src/backend/commands/Makefile              |    4 +-
 src/backend/commands/cmdtrigger.c          |  580 ++++++++++++++++++++++++++
 src/backend/nodes/copyfuncs.c              |   32 ++
 src/backend/nodes/equalfuncs.c             |   28 ++
 src/backend/nodes/outfuncs.c               |  405 ++++++++++++++++++
 src/backend/parser/gram.y                  |   70 +++-
 src/backend/tcop/utility.c                 |   44 ++
 src/backend/utils/adt/ruleutils.c          |  613 +++++++++++++++++++++++++++-
 src/include/catalog/dependency.h           |    1 +
 src/include/catalog/indexing.h             |    5 +
 src/include/catalog/pg_cmdtrigger.h        |   59 +++
 src/include/commands/cmdtrigger.h          |   43 ++
 src/include/commands/defrem.h              |   14 +
 src/include/nodes/nodes.h                  |    2 +
 src/include/nodes/parsenodes.h             |   28 ++
 src/include/parser/kwlist.h                |    1 +
 src/test/regress/expected/sanity_check.out |    3 +-
 src/test/regress/expected/triggers.out     |   35 ++
 src/test/regress/sql/triggers.sql          |   35 ++
 24 files changed, 2157 insertions(+), 18 deletions(-)



Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support