Event Triggers: adding information - Mailing list pgsql-hackers

Hi,

Please find attached v3 of the patch to add information to event
triggers, including command string. This work has mostly been part of
the original goal and its design is the result of much reviewing here,
in particular from Robert and Tom (rewriting a command before is done is
called magic, not code, and that's why we have ddl_command_trace now).
Many thanks.

I hope that Robert will be in a position to continue reviewing and
taking care of that patch series. He's clearly interested but really
busy these days, so if another commiter wants to see for himself, please
don't be shy!

The current patch provides the following features:

  - New events: ddl_command_end and ddl_command_trace

  - New information in the TG_VARIABLE:
     object name, schema name, object id, object kind,
     operation (CREATE|ALTER|DROP), context (GENERATED|TOPLEVEL|…)
     normalized command string

  - New event filtering on CONTEXT (see after sig for an example)

   + create event trigger regress_event_trigger_end on ddl_command_end
   +    when context in ('toplevel', 'generated', 'query', 'subcommand')
   +    execute procedure test_event_trigger();

  - Documentation of listed features

  - pg_dump support of the new features (context support)

The command string normalization is a necessary feature and takes most
of this patch footprint. The main use case of this feature is in-core
logical replication support, the main non-core users will be the usual
replication suspects (Slony, Londiste, Bucardo) and the audit systems.
We might be able to also support some advanced Extension capabilities if
we get "command diverting" support in next commit fest, and we could
already deprecate the extension whitelisting code I had to do for 9.1
and 9.2.

As we need to support all of CREATE, ATLER and DROP cases, I can't see
another way than to work from a very loose notion of the parse tree. Up
to now I've seen 3 cases worth reporting:

  - simple commands, where you can rely on the parsetree as is, and that
    need no transform stage (create extension, drop object, alter
    sequence, text search *, most commands really)

  - medium complex commands, which transform the parsetree in some ways
    but for which it still is easy to use it directly if you take care
    of only using the transformed parse tree (create table, create
    domain, create view — oh, the work was already done)

  - complex commands where you need to hack them to return the actual
    parse tree like structure they prepared out of the transformed parse
    tree so that you can get to kmow what they've just done, and that's
    only ALTER TABLE really, up to now

So my guess is that it will get real easier from now, and I will add
some more de parsing support with stats to have some ideas about that.

I've been told that the maintenance cost is going to be so great as to
limit our abilities to continue adding features to existing DDLs and
coming up with new ones. I have two answers: first, I don't suppose
we're going to include logical replication without DDL support in core
and I don't see another realistic way to implement it, second, let's
have some data to think about the case at hand.

For example, I added CREATE SCHEMA objectid and command normalisation
and here's the footprint:

  git diff --stat
   src/backend/commands/schemacmds.c   |    6 ++++--
   src/backend/tcop/utility.c          |    4 ++--
   src/backend/utils/adt/ddl_rewrite.c |   31 +++++++++++++++++++++++++++++++
   src/include/commands/schemacmds.h   |    2 +-
   4 files changed, 38 insertions(+), 5 deletions(-)

For the whole DefineStmt command set (CREATE AGGREGATE, OPERATOR, TYPE,
TEXT SEARCH *, COLLATION), we're talking about:

    11 files changed, 186 insertions(+), 70 deletions(-)
    95 lines of those in ddl_rewrite.c

For the normalisation of CREATE CONVERSATION command string, this time
it's

    4 files changed, 40 insertions(+), 5 deletions(-)

And CREATE DOMAIN, which is more involved as you need to take care of
rewriting DEFAULT and CHECK constraints, we have

    4 files changed, 163 insertions(+), 3 deletions(-)

The other commands I need to be working on if we want more data are
ALTER kind of commands, and I've already worked on the most complex one
of those, namely ALTER TABLE. I don't expect most DROP commands to offer
any difficulty here, as I already have support for DropStmt.

The question for DropStmt is what to do about object specific
information when a single command can have as many targets as you want
to? Current patch is filling-in information for the "main" target which
would be the first object in the sequence, but that's a very weak
position and I would easily agree to just leave the fields NULL in that
case.

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

A mildly interesting example that you can have now:

create schema baz
    authorization dim
  create table distributors
    (did serial primary key,
     name varchar(40),
     f2 text check (upper(f2) = f2),
     unique(name) with (fillfactor=70)
    )
    with (fillfactor=70);

I added the blank lines to help the reader in that console paste:

NOTICE:  snitch event: ddl_command_end, context: GENERATED, tag: CREATE SEQUENCE, operation: CREATE, type: SEQUENCE
NOTICE:           oid: 41633, schema: baz, name: distributors_did_seq
NOTICE:       command: CREATE SEQUENCE baz.distributors_did_seq;

NOTICE:  snitch event: ddl_command_end, context: SUBCOMMAND, tag: CREATE TABLE, operation: CREATE, type: TABLE
NOTICE:           oid: 41635, schema: baz, name: distributors
NOTICE:       command: CREATE TABLE baz.distributors (did integer, name pg_catalog.varchar, f2 text,  CHECK ((upper(f2)
=f2))) WITH (fillfactor=70); 

NOTICE:  snitch event: ddl_command_end, context: GENERATED, tag: CREATE INDEX, operation: CREATE, type: INDEX
NOTICE:           oid: 41643, schema: baz, name: distributors_pkey
NOTICE:       command: CREATE UNIQUE INDEX distributors_pkey ON baz.distributors USING btree (did);

NOTICE:  snitch event: ddl_command_end, context: GENERATED, tag: CREATE INDEX, operation: CREATE, type: INDEX
NOTICE:           oid: 41645, schema: baz, name: distributors_name_key
NOTICE:       command: CREATE UNIQUE INDEX distributors_name_key ON baz.distributors USING btree (name) WITH
(fillfactor=70);

NOTICE:  snitch event: ddl_command_end, context: GENERATED, tag: ALTER SEQUENCE, operation: ALTER, type: SEQUENCE
NOTICE:           oid: 41633, schema: baz, name: distributors_did_seq
NOTICE:       command: ALTER SEQUENCE baz.distributors_did_seq OWNED BY baz.distributors.did;

NOTICE:  snitch event: ddl_command_end, context: TOPLEVEL, tag: CREATE SCHEMA, operation: CREATE, type: SCHEMA
NOTICE:           oid: 41632, schema: <NULL>, name: baz
NOTICE:       command: CREATE SCHEMA baz AUTHORIZATION dim;
CREATE SCHEMA