Thread: system_information.triggers & truncate triggers
Hello hackers: I need a list of all triggers created in my database, but the view system_information.triggers does not show truncate triggers, but it does for insert, update and delete triggers. The same problem is found in versions 9.1 and 9.2. Regards. 10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS... CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION http://www.uci.cu http://www.facebook.com/universidad.uci http://www.flickr.com/photos/universidad_uci
On Wed, Sep 26, 2012 at 12:17 AM, Daymel Bonne Solís <dbonne@uci.cu> wrote: > Hello hackers: > > I need a list of all triggers created in my database, but the view > system_information.triggers does not show truncate triggers, but it does for > insert, update and delete triggers. > > The same problem is found in versions 9.1 and 9.2. > The definition of information_schema.triggers contains this: """ FROM pg_namespace n, pg_class c, pg_trigger t, -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE, -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE (VALUES (4, 'INSERT'), (8, 'DELETE'), (16, 'UPDATE')) AS em (num, text) """ so it seems that we are not showing TRUNCATE triggers intentionally, but that comment fails to explain why -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On Tue, Sep 25, 2012 at 10:55 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > On Wed, Sep 26, 2012 at 12:17 AM, Daymel Bonne Solís <dbonne@uci.cu> wrote: >> Hello hackers: >> >> I need a list of all triggers created in my database, but the view >> system_information.triggers does not show truncate triggers, but it does for >> insert, update and delete triggers. >> >> The same problem is found in versions 9.1 and 9.2. >> > > The definition of information_schema.triggers contains this: > """ > FROM pg_namespace n, pg_class c, pg_trigger t, > -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE, > -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE > (VALUES (4, 'INSERT'), > (8, 'DELETE'), > (16, 'UPDATE')) AS em (num, text) > """ > > 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? -- fdr
On 09/26/2012 03:08 AM, Daniel Farina wrote: > On Tue, Sep 25, 2012 at 10:55 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: >> On Wed, Sep 26, 2012 at 12:17 AM, Daymel Bonne Solís <dbonne@uci.cu> wrote: >>> Hello hackers: >>> >>> I need a list of all triggers created in my database, but the view >>> system_information.triggers does not show truncate triggers, but it does for >>> insert, update and delete triggers. >>> >>> The same problem is found in versions 9.1 and 9.2. >>> >> The definition of information_schema.triggers contains this: >> """ >> FROM pg_namespace n, pg_class c, pg_trigger t, >> -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE, >> -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE >> (VALUES (4, 'INSERT'), >> (8, 'DELETE'), >> (16, 'UPDATE')) AS em (num, text) >> """ >> >> 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? > I think this case should be explicitly stated in the documentation. Regards. 10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS... CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION http://www.uci.cu http://www.facebook.com/universidad.uci http://www.flickr.com/photos/universidad_uci
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.)
regards, tom lane
On 26 September 2012 15:02, 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.) 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
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 bybackends, 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> -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
On 26-09-2012 11:08, Simon Riggs wrote: > I suggest we implement that with some kind of switch/case in the view > definition. > -- parameter can be set in a session and defaults to on SET compliance_information_schema TO off; -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte24x7 e Treinamento
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. [...]
On Wed, Sep 26, 2012 at 10:59 AM, Christopher Browne <cbbrowne@gmail.com> wrote: > 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. pg_proc.oid::regprocedure::text has been pretty good to me for this sort of thing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company