Re: system_information.triggers & truncate triggers

On Wed, Sep 26, 2012 at 10:02 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Daniel Farina <daniel(at)heroku(dot)com> writes:
>> On Tue, Sep 25, 2012 at 10:55 PM, Jaime Casanova <jaime(at)2ndquadrant(dot)com> wrote:
>>> The definition of information_schema.triggers contains this:
>>> -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
>>> so it seems that we are not showing TRUNCATE triggers intentionally,
>>> but that comment fails to explain why
>
>> Wouldn't it be because TRUNCATE is a PostgreSQL language extension?
>
> Yeah. The SQL standard specifies the allowed values in that column,
> and TRUNCATE is not among them.
>
> For similar reasons, you won't find exclusion constraints represented
> in the information_schema views, and there are some other cases that
> I don't recall this early in the morning.
>
> The point of the information_schema (at least IMHO) is to present
> standard-conforming information about standard-conforming database
> objects in a standard-conforming way, so that cross-DBMS applications
> can rely on what they'll see there. If you are doing anything that's
> not described by the SQL standard, you will get at best an incomplete
> view of it from the information_schema. In that case you're a lot
> better off looking directly at the underlying catalogs.
>
> (Yes, I'm aware that some other DBMS vendors have a more liberal
> interpretation of what standards compliance means in this area.)

Let me grouse about this a bit... <grouse>

I appreciate that standards compliance means that information_schema
needs to be circumspect as to what it includes.

But it is irritating that information_schema provides a representation
of (for instance) triggers that, at first, looks nice and clean and
somewhat version-independent, only to fall over because there's a
class of triggers that it consciously ignores.

If I'm wanting to do schema analytics on this (and I do), I'm left
debating between painful choices:

a) Use information_schema for what it *does* have, and then add in a
surprising-looking hack that's pretty version-dependent to draw in the
other triggers that it left out

b) Ignore the seeming-nice information_schema representation, and
construct a version-dependent extraction covering everything that more
or less duplicates the work being done by information_schema.triggers.

I'd really like to have something like

c) Something like information_schema that "takes the
standards-conformance gloves off" and gives a nice representation of
all the triggers.

Make no mistake, I'm not casting aspersions at how pg_trigger was
implemented; I have no complaint there, as it's quite fair that the
internal representation won't be totally "human-readability-friendly."
That is a structure that is continuously accessed by backends, and it
is entirely proper to bias implementation to internal considerations.
But I'd sure like ways to get at more analytically-friendly
representations.

A different place where I wound up having to jump through considerable
hoops when doing schema analytics was vis-a-vis identifying functions.
I need to be able to compare schemas across databases, so oid-based
identification of functions is a total non-starter. It appears that
the best identification of a function would be based on the
combination of schema name, function name, and the concatenation of
argument data types. It wasn't terribly difficult to construct that
third bit, but it surely would be nice if there was a view capturing
it, and possibly even serializing it into a table to enable indexing
on it. Performance-wise, function comparisons turned out to be one of
the most expensive things I did, specifically because of that mapping
surrounding arguments.

</grouse>
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"