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

On Fri, Sep 20, 2019 at 8:02 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ashutosh Sharma <ashu.coek88@gmail.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