Re: BUG #16015: information_schema.triggers lack of truncate trigger

On Fri, Sep 20, 2019 at 8:02 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> writes:
> > This is what I found in the definition of triggers views in
> > information_schema.sql which says we are intentionally omitting
> > truncate triggers but then there is no clear cut explanation given for
> > this omission.
>
> The reason is that TRUNCATE triggers aren't in the SQL standard, so
> they can't be shown by a spec-compliant view. Concretely, SQL:2011
> part 11 section 6.62 ("TRIGGERS base table") shows that column as
> having this definition:
>
> EVENT_MANIPULATION INFORMATION_SCHEMA.CHARACTER_DATA
> CONSTRAINT TRIGGERS_EVENT_MANIPULATION_CHECK
> CHECK ( EVENT_MANIPULATION IN
> ( 'INSERT', 'DELETE', 'UPDATE' ) ),
>
> Some other DBMSes think it's okay to bend the definition of the
> information_schema views so that they can show non-spec-compliant
> objects, but we don't. The point of those views, according to our
> policy, is to be useful to portable (spec-compliant) applications
> which presumably will only be creating spec-compliant objects.
> If somebody makes non-spec-compliant objects in the same database,
> and we show them in the information_schema, such an application
> could get mightily confused by what it sees there.
>
> In short, if you want to make Postgres-specific objects like
> ON TRUNCATE triggers, use Postgres-specific system catalog
> queries to introspect them.
>

Thanks for the explanation. I could also see this in the postgres docs
for CREATE TRIGGER - [1]

"The ability to fire triggers for TRUNCATE is a PostgreSQL extension
of the SQL standard, as is the ability to define statement-level
triggers on views."

[1] - https://www.postgresql.org/docs/devel/sql-createtrigger.html

> regards, tom lane