psql \d: Display table where trigger is defined, if inherited

It's important to know that a trigger is cloned from a parent table,
because of the behavior that the trigger is dropped on detach.  Make
psql's \d display it.

We'd like to backpatch, but lack of the pg_trigger.tgparentid column
makes it more difficult.  Punt for now.  If somebody wants to volunteer
an implementation that reads pg_depend on older versions, that can
probably be backpatched.

Authors: Justin Pryzby, Amit Langote, Álvaro Herrera
Discussion: https://postgr.es/m/20200419002206.GM26953@telsasoft.com

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f05e914b4de52d9333f5b11b79078bc6c3d31d3d..8dca6d8bb4382212c470b19a024fc8a9db53a4e0 100644 (file)
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2939,14 +2939,22 @@ describeOneTableDetails(const char *schemaname,
        printfPQExpBuffer(&buf,
                          "SELECT t.tgname, "
                          "pg_catalog.pg_get_triggerdef(t.oid%s), "
-                         "t.tgenabled, %s\n"
+                         "t.tgenabled, %s, %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);
+                          "false AS tgisinternal"),
+                         (pset.sversion >= 130000 ?
+                          "(SELECT (NULLIF(a.relid, t.tgrelid))::pg_catalog.regclass"
+                          " FROM pg_catalog.pg_trigger AS u, "
+                          "      pg_catalog.pg_partition_ancestors(t.tgrelid) AS a"
+                          " WHERE u.tgname = t.tgname AND u.tgrelid = a.relid"
+                          "       AND u.tgparentid = 0) AS parent" :
+                          "NULL AS parent"),
+                         oid);
        if (pset.sversion >= 110000)
            appendPQExpBufferStr(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D') \n"
                                 "    OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid \n"
@@ -3062,6 +3070,12 @@ describeOneTableDetails(const char *schemaname,
                        tgdef = usingpos + 9;
 
                    printfPQExpBuffer(&buf, "    %s", tgdef);
+
+                   /* Visually distinguish inherited triggers */
+                   if (!PQgetisnull(result, i, 4))
+                       appendPQExpBuffer(&buf, ", ON TABLE %s",
+                               PQgetvalue(result, i, 4));
+
                    printTableAddFooter(&cont, buf.data);
                }
            }

diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index c1482e185bd3527a725be639090d128114409486..5e76b3a47e7da7b2d7403fbec0c91ed5ddc9592d 100644 (file)
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -2033,7 +2033,7 @@ create trigger trg1 after insert on trigpart for each row execute procedure trig
  b      | integer |           |          | 
 Partition of: trigpart FOR VALUES FROM (2000) TO (3000)
 Triggers:
-    trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE FUNCTION trigger_nothing()
+    trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE FUNCTION trigger_nothing(), ON TABLE trigpart
 
 alter table trigpart detach partition trigpart3;
 drop trigger trg1 on trigpart3; -- fail due to "does not exist"