pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix psql \d's query for identifying parent triggers. 3+ messages / 1 participants [nested] [flat]
* pgsql: Fix psql \d's query for identifying parent triggers. @ 2022-01-18 02:19 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2022-01-18 02:19 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix psql \d's query for identifying parent triggers. The original coding (from c33869cc3) failed with "more than one row returned by a subquery used as an expression" if there were unrelated triggers of the same tgname on parent partitioned tables. (That's possible because statement-level triggers don't get inherited.) Fix by 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 at all when the trigger is visibly non-inherited. Aside from saving some cycles, this avoids the need for a confusing and undocumented NULLIF(). While here, tweak the format of the emitted query to look a bit nicer 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 Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/3886785b4ce59147b92319e7204ab64a9d8801cb Modified Files -------------- src/bin/psql/describe.c | 45 +++++++++++++++++++++++++--------- src/test/regress/expected/triggers.out | 14 +++++++++++ src/test/regress/sql/triggers.sql | 5 ++++ 3 files changed, 53 insertions(+), 11 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix psql \d's query for identifying parent triggers. @ 2022-01-18 02:19 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2022-01-18 02:19 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix psql \d's query for identifying parent triggers. The original coding (from c33869cc3) failed with "more than one row returned by a subquery used as an expression" if there were unrelated triggers of the same tgname on parent partitioned tables. (That's possible because statement-level triggers don't get inherited.) Fix by 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 at all when the trigger is visibly non-inherited. Aside from saving some cycles, this avoids the need for a confusing and undocumented NULLIF(). While here, tweak the format of the emitted query to look a bit nicer 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 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/9007d4ea774edd815efeb0e0ef954127841beb2f Modified Files -------------- src/bin/psql/describe.c | 38 +++++++++++++++++++++++++++------- src/test/regress/expected/triggers.out | 14 +++++++++++++ src/test/regress/sql/triggers.sql | 5 +++++ 3 files changed, 49 insertions(+), 8 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix psql \d's query for identifying parent triggers. @ 2022-01-18 02:19 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2022-01-18 02:19 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix psql \d's query for identifying parent triggers. The original coding (from c33869cc3) failed with "more than one row returned by a subquery used as an expression" if there were unrelated triggers of the same tgname on parent partitioned tables. (That's possible because statement-level triggers don't get inherited.) Fix by 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 at all when the trigger is visibly non-inherited. Aside from saving some cycles, this avoids the need for a confusing and undocumented NULLIF(). While here, tweak the format of the emitted query to look a bit nicer 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 Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/90e0f9fd8cae7cfb09c11534f690edde6d6ee06c Modified Files -------------- src/bin/psql/describe.c | 45 +++++++++++++++++++++++++--------- src/test/regress/expected/triggers.out | 14 +++++++++++ src/test/regress/sql/triggers.sql | 5 ++++ 3 files changed, 53 insertions(+), 11 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-01-18 02:19 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-01-18 02:19 pgsql: Fix psql \d's query for identifying parent triggers. Tom Lane <tgl@sss.pgh.pa.us> 2022-01-18 02:19 pgsql: Fix psql \d's query for identifying parent triggers. Tom Lane <tgl@sss.pgh.pa.us> 2022-01-18 02:19 pgsql: Fix psql \d's query for identifying parent triggers. Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox