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 <[email protected]>
> 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 <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
