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 whyWouldn'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 whyWouldn'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 whyWouldn'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>
--
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, Suporte 24x7 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 whyWouldn'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 outb) 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