Re: system_information.triggers & truncate triggers - Mailing list pgsql-hackers
From | Gavin Flower |
---|---|
Subject | Re: system_information.triggers & truncate triggers |
Date | |
Msg-id | 50637C4B.4060005@archidevsys.co.nz Whole thread Raw |
In response to | Re: system_information.triggers & truncate triggers (Christopher Browne <cbbrowne@gmail.com>) |
List | pgsql-hackers |
On 27/09/12 02:59, Christopher Browne wrote: > On Wed, Sep 26, 2012 at 10:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Daniel Farina <daniel@heroku.com> writes: >>> On Tue, Sep 25, 2012 at 10:55 PM, Jaime Casanova <jaime@2ndquadrant.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. [...]
pgsql-hackers by date: