Re: Add CREATE support to event triggers

On Tue, Feb 4, 2014 at 12:11 AM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> I have run into some issues, though:
>
> 1. certain types, particularly timestamp/timestamptz but really this
> could happen for any type, have unusual typmod output behavior. For
> those one cannot just use the schema-qualified catalog names and then
> append the typmod at the end; because what you end up is something like
> pg_catalog.timestamptz(4) with time zone
> because, for whatever reason, the "with time zone" is part of typmod
> output. But this doesn't work at all for input. I'm not sure how to
> solve this.

How about doing whatever pg_dump does?

> 2. I have been having object definitions be emitted complete; in
> particular, sequences have OWNED BY clauses when they have an owning
> column. But this doesn't work with a SERIAL column, because we get
> output like this:
>
> alvherre=# CREATE TABLE public.hijo (b serial);
> NOTICE: expanded: CREATE SEQUENCE public.hijo_b_seq INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1 NO CYCLE OWNED BY public.hijo.b
> NOTICE: expanded: CREATE TABLE public.hijo (b pg_catalog.int4 DEFAULT nextval('hijo_b_seq'::regclass) NOT NULL )
>
> which is all nice, except that the sequence is using the column name as
> owner before the column has been created in the first place. Both these
> command will, of course, fail, because both depend on the other to have
> been executed first. The tie is easy to break in this case: just don't
> emit the OWNED BY clause .. but it makes me a bit nervous to be
> hardcoding the decision of parts that might depend on others. OTOH
> pg_dump already knows how to split objects in constituent parts as
> necessary; maybe it's not so bad.

Well, the sequence can't depend on a table column that doesn't exist
yet, so if it's in effect doing what you've shown there, it's
"cheating" by virtue of knowing that nobody can observe the
intermediate state. Strictly speaking, there's nothing "wrong" with
emitting those commands just as you have them there; they won't run,
but if what you want to do is log what's happened rather than replay
it, that's OK. Producing output that is actually executable is a
strictly harder problem than producing output that accurately
describes what happened. As you say, pg_dump already splits things
and getting executable output out of this facility will require the
same kinds of tricks here. This gets back to my worry about
maintaining two or three copies of the code that solve many of the
same problems in quite different ways...

> 3. It is possible to coerce ruleutils.c to emit always-qualified names
> by using PushOverrideSearchPath() facility; but actually this doesn't
> always work, because some places in namespace.c believe that
> PG_CATALOG_NAMESPACE is always visible and so certain objects are not
> qualified. In particular, text columns using default collation will be
> emitted as having collation "default" and not pg_catalog.default as I
> would have initially expected. Right now it doesn't seem like this is a
> problem, but it is unusual.

We have a quote_all_identifiers flag. We could have a
schema_qualify_all_identifiers flag, too. Then again, why is the
behavior of schema-qualifying absolutely everything even desirable?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company