Re: system_information.triggers & truncate triggers

On 27/09/12 02:59, Christopher Browne wrote:
> 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>

I agree with your comments, but I couldn't helping thinking about Grouse
shooting! :-)

http://www.telegraph.co.uk/news/features/7944546/Grouse-shooting-season.html
[...]
Grouse shooting season
Grouse-shooters have been looking forward to mid-August with bridal
excitement since the Game Act of 1831 made it illegal to shoot out of
season.
[...]