Re: Deparsing DDL command strings

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> I'll show some examples of very involved command (CREATE and ALTER TABLE
> are the most complex we have I think) and some very simple commands
> (DROP TABLE is one of the simplest), so that we can make up our minds on
> that angle.

So please find attached a demo patch to show up what it takes to deparse
complex command strings, and here's inline some example of why that's a
good idea to actually deparse them rather than hand out whatever the
user typed in:

\dy
List of event triggers
-[ RECORD 1 ]--------------------------
Name | regress_event_trigger_trace
Event | ddl_command_trace
Owner | dim
Enabled | enabled
Procedure | test_event_trigger
Tags |

foo=# drop table foo;
NOTICE: test_event_trigger: ddl_command_start DROP TABLE
NOTICE: test_event_trigger: DROP, TABLE
NOTICE: test_event_trigger: DROP TABLE public.foo RESTRICT;
DROP TABLE

foo=# create table foo(id serial primary key,
f2 text default 'plop' check (f2 != ''));
NOTICE: test_event_trigger: ddl_command_end CREATE TABLE
NOTICE: test_event_trigger: CREATE, TABLE
NOTICE: test_event_trigger: CREATE TABLE public.foo (id integer PRIMARY KEY DEFAULT nextval('foo_id_seq'::regclass) NOT NULL, f2 text DEFAULT 'plop' CHECK ((f2 <> ''::text)), CHECK ((f2 <> ''::text)));
CREATE TABLE

The user of that command string still has to know what to look for and
maybe should include a proper SQL parser, but at least it doesn't need
to do much guesswork about how the serial attached sequence will get
named by the system and such oddities.

The attached patch also includes support for the complete ALTER TABLE
command and some more (CREATE SEQUENCE, CREATE EXTENSION).

> Doing the same thing at ddl_command_end would allow us have all the
> information we need and leave nothing to magic guesses: full schema
> qualification of all objects involved, main object(s) OIDs available,
> all the jazz.

That's what is happening now in the attached patch, also with a new
event called 'ddl_command_trace' which will either map to _start or _end
depending on the operation (we want _start when doing DROP TABLE, we
want the operation to be complete before tracing it when talking about a
CREATE or an ALTER table).

And here's the scope we're talking about, including new command types,
new information passed down to user triggers, and the rewrite support
itself, isolated away:

git diff --stat postgres/master..
src/backend/catalog/heap.c | 5 +-
src/backend/commands/event_trigger.c | 241 ++++-
src/backend/tcop/utility.c | 187 ++--
src/backend/utils/adt/Makefile | 2 +-
src/backend/utils/adt/ddl_rewrite.c | 1415 +++++++++++++++++++++++++++
src/backend/utils/adt/ruleutils.c | 9 +-
src/backend/utils/cache/evtcache.c | 4 +
src/include/catalog/heap.h | 4 +
src/include/commands/event_trigger.h | 43 +-
src/include/utils/builtins.h | 14 +
src/include/utils/evtcache.h | 4 +-
src/pl/plpgsql/src/pl_comp.c | 40 +
src/pl/plpgsql/src/pl_exec.c | 53 +-
src/pl/plpgsql/src/plpgsql.h | 5 +
src/test/regress/expected/event_trigger.out | 40 +-
src/test/regress/sql/event_trigger.sql | 36 +-
16 files changed, 1938 insertions(+), 164 deletions(-)

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