On Thu, Nov 21, 2013 at 11:59:51PM -0200, Fabrízio de Royes Mello wrote: > On Fri, Oct 25, 2013 at 3:37 PM, fabriziomello <fabriziome...@gmail.com> > wrote: > > > > On 2013-09-18 15:15:55 +0200, Bernd Helmle wrote: > > > On 2013-09-18 15:15:55 +0200, Bernd Helmle wrote: > > > > --On 18. September 2013 13:52:29 +0200 Andres Freund > > > > <andres@> wrote: > > > > > > > > >If you do ALTER TABLE ... DISABLE TRIGGER ALL; and then individually > > > > >re-enable the disabled triggers it's easy to miss internal triggers. > > > > >A \d+ tablename will not show anything out of the ordinary for that > > > > >situation since we don't show internal triggers. But foreign key checks > > > > >won't work. > > > > >So, how about displaying disabled internal triggers in psql? > > > > > > > > Hi had exactly the same concerns this morning while starting to look at > > > the > > > > ENABLE/DISABLE constraint patch. However, i wouldn't display them as > > > > triggers, but maybe more generally as "disabled constraints" or such. > > > > > > Well, that will lead the user in the wrong direction, won't it? They > > > haven't disabled the constraint but the trigger. Especially as we > > > already have NOT VALID and might grow DISABLED for constraint > > > themselves... > > > > > > > Hi, > > > > The attached patch [1] enable PSQL to list internal disabled triggers in \d > > only in versions >= 9.0. > > > > [1] psql-display-all-triggers-v1.patch > > <http://postgresql.1045698.n5.nabble.com/file/n5775954/ > psql-display-all-triggers-v1.patch>
As others, I am concerned about people being confused when funny-looking trigger names suddenly appearing when you disable all table triggers. What I ended up doing is to create a user and internal section when displaying disabled triggers: Disabled user triggers: check_update BEFORE UPDATE ON orders FOR EACH ROW EXECUTE PROCEDURE trigf() Disabled internal triggers: "RI_ConstraintTrigger_c_16409" AFTER INSERT ON orders FROM customer NOT DEF ... "RI_ConstraintTrigger_c_16410" AFTER UPDATE ON orders FROM customer NOT DEF ... I kept the "Triggers" section unchanged, showing only user triggers. I also updated the code to handle 8.3+ servers. Patch attached. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c new file mode 100644 index 3cb8df2..a194ce7 *** a/src/bin/psql/describe.c --- b/src/bin/psql/describe.c *************** describeOneTableDetails(const char *sche *** 2090,2104 **** printfPQExpBuffer(&buf, "SELECT t.tgname, " "pg_catalog.pg_get_triggerdef(t.oid%s), " ! "t.tgenabled\n" "FROM pg_catalog.pg_trigger t\n" "WHERE t.tgrelid = '%s' AND ", (pset.sversion >= 90000 ? ", true" : ""), ! oid); if (pset.sversion >= 90000) ! appendPQExpBufferStr(&buf, "NOT t.tgisinternal"); else if (pset.sversion >= 80300) ! appendPQExpBufferStr(&buf, "t.tgconstraint = 0"); else appendPQExpBufferStr(&buf, "(NOT tgisconstraint " --- 2090,2108 ---- printfPQExpBuffer(&buf, "SELECT t.tgname, " "pg_catalog.pg_get_triggerdef(t.oid%s), " ! "t.tgenabled, %s\n" "FROM pg_catalog.pg_trigger t\n" "WHERE t.tgrelid = '%s' AND ", (pset.sversion >= 90000 ? ", true" : ""), ! (pset.sversion >= 90000 ? "t.tgisinternal" : ! pset.sversion >= 80300 ? ! "t.tgconstraint <> 0 AS tgisinternal" : ! "false AS tgisinternal"), oid); if (pset.sversion >= 90000) ! /* display/warn about disabled internal triggers */ ! appendPQExpBuffer(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))"); else if (pset.sversion >= 80300) ! appendPQExpBufferStr(&buf, "(t.tgconstraint = 0 OR (t.tgconstraint <> 0 AND t.tgenabled = 'D'))"); else appendPQExpBufferStr(&buf, "(NOT tgisconstraint " *************** describeOneTableDetails(const char *sche *** 2124,2130 **** * disabled triggers and the two special ALWAYS and REPLICA * configurations. */ ! for (category = 0; category < 4; category++) { have_heading = false; for (i = 0; i < tuples; i++) --- 2128,2134 ---- * disabled triggers and the two special ALWAYS and REPLICA * configurations. */ ! for (category = 0; category <= 4; category++) { have_heading = false; for (i = 0; i < tuples; i++) *************** describeOneTableDetails(const char *sche *** 2133,2143 **** --- 2137,2149 ---- const char *tgdef; const char *usingpos; const char *tgenabled; + const char *tgisinternal; /* * Check if this trigger falls into the current category */ tgenabled = PQgetvalue(result, i, 2); + tgisinternal = PQgetvalue(result, i, 3); list_trigger = false; switch (category) { *************** describeOneTableDetails(const char *sche *** 2146,2159 **** list_trigger = true; break; case 1: ! if (*tgenabled == 'D' || *tgenabled == 'f') list_trigger = true; break; case 2: ! if (*tgenabled == 'A') list_trigger = true; break; case 3: if (*tgenabled == 'R') list_trigger = true; break; --- 2152,2171 ---- list_trigger = true; break; case 1: ! if ((*tgenabled == 'D' || *tgenabled == 'f') && ! *tgisinternal == 'f') list_trigger = true; break; case 2: ! if ((*tgenabled == 'D' || *tgenabled == 'f') && ! *tgisinternal == 't') list_trigger = true; break; case 3: + if (*tgenabled == 'A') + list_trigger = true; + break; + case 4: if (*tgenabled == 'R') list_trigger = true; break; *************** describeOneTableDetails(const char *sche *** 2170,2181 **** printfPQExpBuffer(&buf, _("Triggers:")); break; case 1: ! printfPQExpBuffer(&buf, _("Disabled triggers:")); break; case 2: ! printfPQExpBuffer(&buf, _("Triggers firing always:")); break; case 3: printfPQExpBuffer(&buf, _("Triggers firing on replica only:")); break; --- 2182,2199 ---- printfPQExpBuffer(&buf, _("Triggers:")); break; case 1: ! if (pset.sversion >= 80300) ! printfPQExpBuffer(&buf, _("Disabled user triggers:")); ! else ! printfPQExpBuffer(&buf, _("Disabled triggers:")); break; case 2: ! printfPQExpBuffer(&buf, _("Disabled internal triggers:")); break; case 3: + printfPQExpBuffer(&buf, _("Triggers firing always:")); + break; + case 4: printfPQExpBuffer(&buf, _("Triggers firing on replica only:")); break;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers