Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3886785

Browse files
committed
Fix psql \d's query for identifying parent triggers.
The original coding (fromc33869c) failed with "more than one rowreturned by a subquery used as an expression" if there were unrelatedtriggers of the same tgname on parent partitioned tables. (That'spossible because statement-level triggers don't get inherited.) Fixby applying LIMIT 1 after sorting the candidates by inheritance level.Also, wrap the subquery in a CASE so that we don't have to execute it atall when the trigger is visibly non-inherited. Aside from saving somecycles, this avoids the need for a confusing and undocumented NULLIF().While here, tweak the format of the emitted query to look a bitnicer for "psql -E", and add some explanation of this subquery,because it badly needs it.Report and patch by Justin Pryzby (with some editing by me).Back-patch to v13 where the faulty code came in.Discussion:https://postgr.es/m/20211217154356.GJ17618@telsasoft.com
1 parent4e87265 commit3886785

File tree

3 files changed

+53
-11
lines changed

3 files changed

+53
-11
lines changed

‎src/bin/psql/describe.c

Lines changed: 34 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -3250,22 +3250,45 @@ describeOneTableDetails(const char *schemaname,
32503250
printfPQExpBuffer(&buf,
32513251
"SELECT t.tgname, "
32523252
"pg_catalog.pg_get_triggerdef(t.oid%s), "
3253-
"t.tgenabled, %s, %s\n"
3254-
"FROM pg_catalog.pg_trigger t\n"
3255-
"WHERE t.tgrelid = '%s' AND ",
3253+
"t.tgenabled, %s,\n",
32563254
(pset.sversion >=90000 ?", true" :""),
32573255
(pset.sversion >=90000 ?"t.tgisinternal" :
32583256
pset.sversion >=80300 ?
32593257
"t.tgconstraint <> 0 AS tgisinternal" :
3260-
"false AS tgisinternal"),
3261-
(pset.sversion >=130000 ?
3262-
"(SELECT (NULLIF(a.relid, t.tgrelid))::pg_catalog.regclass"
3263-
" FROM pg_catalog.pg_trigger AS u, "
3264-
" pg_catalog.pg_partition_ancestors(t.tgrelid) AS a"
3265-
" WHERE u.tgname = t.tgname AND u.tgrelid = a.relid"
3266-
" AND u.tgparentid = 0) AS parent" :
3267-
"NULL AS parent"),
3258+
"false AS tgisinternal"));
3259+
3260+
/*
3261+
* Detect whether each trigger is inherited, and if so, get the name
3262+
* of the topmost table it's inherited from. We have no easy way to
3263+
* do that pre-v13, for lack of the tgparentid column. Even with
3264+
* tgparentid, a straightforward search for the topmost parent would
3265+
* require a recursive CTE, which seems unduly expensive. We cheat a
3266+
* bit by assuming parent triggers will match by tgname; then, joining
3267+
* with pg_partition_ancestors() allows the planner to make use of
3268+
* pg_trigger_tgrelid_tgname_index if it wishes. We ensure we find
3269+
* the correct topmost parent by stopping at the first-in-partition-
3270+
* ancestry-order trigger that has tgparentid = 0. (There might be
3271+
* unrelated, non-inherited triggers with the same name further up the
3272+
* stack, so this is important.)
3273+
*/
3274+
if (pset.sversion >=130000)
3275+
appendPQExpBufferStr(&buf,
3276+
" CASE WHEN t.tgparentid != 0 THEN\n"
3277+
" (SELECT u.tgrelid::pg_catalog.regclass\n"
3278+
" FROM pg_catalog.pg_trigger AS u,\n"
3279+
" pg_catalog.pg_partition_ancestors(t.tgrelid) WITH ORDINALITY AS a(relid, depth)\n"
3280+
" WHERE u.tgname = t.tgname AND u.tgrelid = a.relid\n"
3281+
" AND u.tgparentid = 0\n"
3282+
" ORDER BY a.depth LIMIT 1)\n"
3283+
" END AS parent\n");
3284+
else
3285+
appendPQExpBufferStr(&buf," NULL AS parent\n");
3286+
3287+
appendPQExpBuffer(&buf,
3288+
"FROM pg_catalog.pg_trigger t\n"
3289+
"WHERE t.tgrelid = '%s' AND ",
32683290
oid);
3291+
32693292
if (pset.sversion >=110000)
32703293
appendPQExpBufferStr(&buf,"(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D') \n"
32713294
" OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid \n"

‎src/test/regress/expected/triggers.out

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2122,6 +2122,20 @@ Triggers:
21222122
alter table trigpart attach partition trigpart3 FOR VALUES FROM (2000) to (3000); -- fail
21232123
ERROR: trigger "trg1" for relation "trigpart3" already exists
21242124
drop table trigpart3;
2125+
-- check display of unrelated triggers
2126+
create trigger samename after delete on trigpart execute function trigger_nothing();
2127+
create trigger samename after delete on trigpart1 execute function trigger_nothing();
2128+
\d trigpart1
2129+
Table "public.trigpart1"
2130+
Column | Type | Collation | Nullable | Default
2131+
--------+---------+-----------+----------+---------
2132+
a | integer | | |
2133+
b | integer | | |
2134+
Partition of: trigpart FOR VALUES FROM (0) TO (1000)
2135+
Triggers:
2136+
samename AFTER DELETE ON trigpart1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_nothing()
2137+
trg1 AFTER INSERT ON trigpart1 FOR EACH ROW EXECUTE FUNCTION trigger_nothing(), ON TABLE trigpart
2138+
21252139
drop table trigpart;
21262140
drop function trigger_nothing();
21272141
--

‎src/test/regress/sql/triggers.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1428,6 +1428,11 @@ create trigger trg1 after insert on trigpart3 for each row execute procedure tri
14281428
altertable trigpart attach partition trigpart3 FORVALUESFROM (2000) to (3000);-- fail
14291429
droptable trigpart3;
14301430

1431+
-- check display of unrelated triggers
1432+
createtriggersamename afterdeleteon trigpart execute function trigger_nothing();
1433+
createtriggersamename afterdeleteon trigpart1 execute function trigger_nothing();
1434+
\d trigpart1
1435+
14311436
droptable trigpart;
14321437
dropfunction trigger_nothing();
14331438

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp