Thread: ALTER TABLE DDL Triggers?
I don't believe that DDL Triggers exist, correct? Actually I am really curious about what options exist to automatically keep table definitions in sink (for two table) over time: for example a table and history table pair. I realise that History-Entity-Attribute-Value tables don't need this kind of maintenance, but I am depending on the ability to frequently query the history tables for the previous values of a particular record. I don't believe that History-EAV tables can do this. -- Regards, Richard Broersma Jr.
richard.broersma@gmail.com ("Richard Broersma") writes:
> I don't believe that DDL Triggers exist, correct?
That is correct.[1]
The usual point is that you cannot attach triggers to pg_catalog
tables, which would be the "obvious" way of trying to notice DDL
changes. (e.g. - by having triggers that would fire when pg_class or
pg_attribute are changed)
It would presumably not be _completely_ implausible to run a trigger
when a table was ALTERed; the trouble would be in evaluating the
semantics what OLD.* and NEW.* ought to contain, _if anything_.
If you took the [1] TRUNCATE approach, there isn't _anything_ (in
terms of OLD.*/NEW.* values) passed to the function; in that case, it
is sufficient to have something (like a function parameter) to
identify the table, and thus pass that info downstream (e.g. - with
replication, passing the TRUNCATE on to downstream nodes). TRUNCATE
is pretty simple; we know well that all it's doing is to get rid of
all the contents of the table at a point in time.
In contrast, the effect of ALTER TABLE is to make near-arbitrary
alterations to pg_class, pg_attribute, and such, and there is, on the
one hand, no obvious semantic of what data to even imagine passing on,
and, on the other, a grand problem of reconstructing the change if you
*did* have access to those underlying tables. That's usually where
the discussion peters out when people propose DDL triggers.
[1] Or about 99% so. There is a change committed for 8.4 where
TRUNCATE can fire a trigger. But it's somewhat disputable whether
TRUNCATE should properly be considered DDL or not.
--
let name="cbbrowne" and tld="linuxfinances.info" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/advocacy.html
When aiming for the common denominator, be prepared for the occasional
division by zero.
On Wed, Apr 16, 2008 at 2:49 PM, Chris Browne <cbbrowne@acm.org> wrote:
> richard.broersma@gmail.com ("Richard Broersma") writes:
> > I don't believe that DDL Triggers exist, correct?
>
> That is correct.[1]
>
> The usual point is that you cannot attach triggers to pg_catalog
> tables, which would be the "obvious" way of trying to notice DDL
> changes. (e.g. - by having triggers that would fire when pg_class or
> pg_attribute are changed)
Thanks for taking the time to explain the situation.
--
Regards,
Richard Broersma Jr.
On Wed, 2008-04-16 at 17:49 -0400, Chris Browne wrote: > It would presumably not be _completely_ implausible to run a trigger > when a table was ALTERed; the trouble would be in evaluating the > semantics what OLD.* and NEW.* ought to contain, _if anything_. Agreed. If there was a simple use case and some fairly straightforward definition of what is needed, that would help move things forward. For example, would it be useful if the trigger only had access to the text of the DDL statement? Which DDL statements would it apply to? As Chris points out, saying "we need everything" just becomes a blocker to progress. So if somebody listening would like to research, detail and *justify* a useful set of additional behaviour then it may be possible to add it. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com