Re: system_information.triggers & truncate triggers

On 26 September 2012 15:02, 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.)

While I understand and even agree with that, I think we also need
another view: information schema as a standard way of representing all
data, even that which extends the standard. Especially so, since
others take the latter view also.

I suggest we implement that with some kind of switch/case in the view
definition.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services