On 2/6/18 02:15, Michael Paquier wrote:
>> - CAST(null AS cardinal_number) AS action_order,
>> -- XXX strange hacks follow
>> + CAST(rank() OVER (PARTITION BY n.oid, c.oid, em.num, (t.tgtype &
>> 1 & 66) ORDER BY t.tgname) AS cardinal_number) AS action_order,
>
> Better to use parenthesis for (t.tgtype & 1 & 66) perhaps? You may want
> to comment that this is to filter per row-statement first, and then with
> after/before/instead of, which are what the 1 and the 66 are for.
Added more comments.
>> - CAST(null AS sql_identifier) AS action_reference_old_table,
>> - CAST(null AS sql_identifier) AS action_reference_new_table,
>> + CAST(tgoldtable AS sql_identifier) AS action_reference_old_table,
>> + CAST(tgnewtable AS sql_identifier) AS action_reference_new_table,
>
>> +SELECT trigger_name, event_manipulation, event_object_schema,
>> event_object_table, action_order, action_condition,
>> action_orientation, action_timing, action_reference_old_table,
>> action_reference_new_table FROM information_schema.triggers ORDER BY
>> 1, 2;
>
> Writing those SQL queries across multiple lines would make them easier
> to read...
done
How about the attached version?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 3b8f1ebb8f8a89c802d1fdeeb839a9a26998917e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Tue, 6 Feb 2018 12:28:12 -0500
Subject: [PATCH v2] Add more information_schema columns
- table_constraints.enforced
- triggers.action_order
- triggers.action_reference_old_table
- triggers.action_reference_new_table
Reviewed-by: Michael Paquier <michael.paqu...@gmail.com>
---
doc/src/sgml/information_schema.sgml | 20 ++++++++--
src/backend/catalog/information_schema.sql | 18 ++++++---
src/test/regress/expected/triggers.out | 63 ++++++++++++++++++++++++++++++
src/test/regress/sql/triggers.sql | 14 +++++++
4 files changed, 106 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/information_schema.sgml
b/doc/src/sgml/information_schema.sgml
index 0faa72f1d3..09ef2827f2 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -5317,6 +5317,13 @@ <title><literal>table_constraints</literal>
Columns</title>
<entry><type>yes_or_no</type></entry>
<entry><literal>YES</literal> if the constraint is deferrable and
initially deferred, <literal>NO</literal> if not</entry>
</row>
+ <row>
+ <entry><literal>enforced</literal></entry>
+ <entry><type>yes_or_no</type></entry>
+ <entry>Applies to a feature not available in
+ <productname>PostgreSQL</productname> (currently always
+ <literal>YES</literal>)</entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -5761,7 +5768,14 @@ <title><literal>triggers</literal> Columns</title>
<row>
<entry><literal>action_order</literal></entry>
<entry><type>cardinal_number</type></entry>
- <entry>Not yet implemented</entry>
+ <entry>
+ Firing order among triggers on the same table having the same
+ <literal>event_manipulation</literal>,
+ <literal>action_timing</literal>, and
+ <literal>action_orientation</literal>. In
+ <productname>PostgreSQL</productname>, triggers are fired in name
+ order, so this column reflects that.
+ </entry>
</row>
<row>
@@ -5806,13 +5820,13 @@ <title><literal>triggers</literal> Columns</title>
<row>
<entry><literal>action_reference_old_table</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Applies to a feature not available in
<productname>PostgreSQL</productname></entry>
+ <entry>Name of the <quote>old</quote> transition table, or null if
none</entry>
</row>
<row>
<entry><literal>action_reference_new_table</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Applies to a feature not available in
<productname>PostgreSQL</productname></entry>
+ <entry>Name of the <quote>new</quote> transition table, or null if
none</entry>
</row>
<row>
diff --git a/src/backend/catalog/information_schema.sql
b/src/backend/catalog/information_schema.sql
index 6fb1a1bc1c..757a14d447 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1783,7 +1783,8 @@ CREATE VIEW table_constraints AS
CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS
yes_or_no)
AS is_deferrable,
CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
- AS initially_deferred
+ AS initially_deferred,
+ CAST('YES' AS yes_or_no) AS enforced
FROM pg_namespace nc,
pg_namespace nr,
@@ -1812,7 +1813,8 @@ CREATE VIEW table_constraints AS
CAST(r.relname AS sql_identifier) AS table_name,
CAST('CHECK' AS character_data) AS constraint_type,
CAST('NO' AS yes_or_no) AS is_deferrable,
- CAST('NO' AS yes_or_no) AS initially_deferred
+ CAST('NO' AS yes_or_no) AS initially_deferred,
+ CAST('YES' AS yes_or_no) AS enforced
FROM pg_namespace nr,
pg_class r,
@@ -2084,8 +2086,12 @@ CREATE VIEW triggers AS
CAST(current_database() AS sql_identifier) AS event_object_catalog,
CAST(n.nspname AS sql_identifier) AS event_object_schema,
CAST(c.relname AS sql_identifier) AS event_object_table,
- CAST(null AS cardinal_number) AS action_order,
- -- XXX strange hacks follow
+ CAST(
+ -- To determine action order, partition by schema, table,
+ -- event_manipulation (INSERT/DELETE/UPDATE), ROW/STATEMENT (1),
+ -- BEFORE/AFTER (66), then order by trigger name
+ rank() OVER (PARTITION BY n.oid, c.oid, em.num, (t.tgtype & 1 &
66) ORDER BY t.tgname)
+ AS cardinal_number) AS action_order,
CAST(
CASE WHEN pg_has_role(c.relowner, 'USAGE')
THEN (regexp_match(pg_get_triggerdef(t.oid), E'.{35,} WHEN
\\((.+)\\) EXECUTE PROCEDURE'))[1]
@@ -2103,8 +2109,8 @@ CREATE VIEW triggers AS
-- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF'
ELSE 'AFTER' END
AS character_data) AS action_timing,
- CAST(null AS sql_identifier) AS action_reference_old_table,
- CAST(null AS sql_identifier) AS action_reference_new_table,
+ CAST(tgoldtable AS sql_identifier) AS action_reference_old_table,
+ CAST(tgnewtable AS sql_identifier) AS action_reference_new_table,
CAST(null AS sql_identifier) AS action_reference_old_row,
CAST(null AS sql_identifier) AS action_reference_new_row,
CAST(null AS time_stamp) AS created
diff --git a/src/test/regress/expected/triggers.out
b/src/test/regress/expected/triggers.out
index 9a7aafcc96..280ee4c5d3 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -96,6 +96,24 @@ CONTEXT: SQL statement "delete from fkeys2 where fkey21 =
$1 and fkey22 = $2 "
update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
+SELECT trigger_name, event_manipulation, event_object_schema,
event_object_table,
+ action_order, action_condition, action_orientation, action_timing,
+ action_reference_old_table, action_reference_new_table
+ FROM information_schema.triggers ORDER BY 1, 2;
+ trigger_name | event_manipulation | event_object_schema |
event_object_table | action_order | action_condition | action_orientation |
action_timing | action_reference_old_table | action_reference_new_table
+----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
+ check_fkeys2_fkey_restrict | DELETE | public |
fkeys2 | 1 | | ROW |
BEFORE | |
+ check_fkeys2_fkey_restrict | UPDATE | public |
fkeys2 | 1 | | ROW |
BEFORE | |
+ check_fkeys2_pkey_exist | INSERT | public |
fkeys2 | 1 | | ROW |
BEFORE | |
+ check_fkeys2_pkey_exist | UPDATE | public |
fkeys2 | 2 | | ROW |
BEFORE | |
+ check_fkeys_pkey2_exist | INSERT | public | fkeys
| 1 | | ROW | BEFORE
| |
+ check_fkeys_pkey2_exist | UPDATE | public | fkeys
| 1 | | ROW | BEFORE
| |
+ check_fkeys_pkey_exist | INSERT | public | fkeys
| 2 | | ROW | BEFORE
| |
+ check_fkeys_pkey_exist | UPDATE | public | fkeys
| 2 | | ROW | BEFORE
| |
+ check_pkeys_fkey_cascade | DELETE | public | pkeys
| 1 | | ROW | BEFORE
| |
+ check_pkeys_fkey_cascade | UPDATE | public | pkeys
| 1 | | ROW | BEFORE
| |
+(10 rows)
+
DROP TABLE pkeys;
DROP TABLE fkeys;
DROP TABLE fkeys2;
@@ -347,6 +365,24 @@ CREATE TRIGGER insert_when BEFORE INSERT ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
CREATE TRIGGER delete_when AFTER DELETE ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
+SELECT trigger_name, event_manipulation, event_object_schema,
event_object_table,
+ action_order, action_condition, action_orientation, action_timing,
+ action_reference_old_table, action_reference_new_table
+ FROM information_schema.triggers ORDER BY 1, 2;
+ trigger_name | event_manipulation | event_object_schema |
event_object_table | action_order | action_condition |
action_orientation | action_timing | action_reference_old_table |
action_reference_new_table
+----------------------+--------------------+---------------------+--------------------+--------------+--------------------------------+--------------------+---------------+----------------------------+----------------------------
+ after_ins_stmt_trig | INSERT | public | main_table
| 1 | | STATEMENT |
AFTER | |
+ after_upd_row_trig | UPDATE | public | main_table
| 1 | | ROW |
AFTER | |
+ after_upd_stmt_trig | UPDATE | public | main_table
| 2 | | STATEMENT |
AFTER | |
+ before_ins_stmt_trig | INSERT | public | main_table
| 2 | | STATEMENT |
BEFORE | |
+ delete_a | DELETE | public | main_table
| 1 | (old.a = 123) | ROW |
AFTER | |
+ delete_when | DELETE | public | main_table
| 2 | true | STATEMENT |
AFTER | |
+ insert_a | INSERT | public | main_table
| 3 | (new.a = 123) | ROW |
AFTER | |
+ insert_when | INSERT | public | main_table
| 4 | true | STATEMENT |
BEFORE | |
+ modified_a | UPDATE | public | main_table
| 3 | (old.a <> new.a) | ROW |
BEFORE | |
+ modified_any | UPDATE | public | main_table
| 4 | (old.* IS DISTINCT FROM new.*) | ROW |
BEFORE | |
+(10 rows)
+
INSERT INTO main_table (a) VALUES (123), (456);
NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE,
level = STATEMENT
NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE,
level = STATEMENT
@@ -1991,6 +2027,33 @@ create trigger child3_update_trig
create trigger child3_delete_trig
after delete on child3 referencing old table as old_table
for each statement execute procedure dump_delete();
+SELECT trigger_name, event_manipulation, event_object_schema,
event_object_table,
+ action_order, action_condition, action_orientation, action_timing,
+ action_reference_old_table, action_reference_new_table
+ FROM information_schema.triggers ORDER BY 1, 2;
+ trigger_name | event_manipulation | event_object_schema |
event_object_table | action_order | action_condition | action_orientation |
action_timing | action_reference_old_table | action_reference_new_table
+------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
+ after_ins_stmt_trig | INSERT | public |
main_table | 1 | | STATEMENT |
AFTER | |
+ after_upd_a_b_row_trig | UPDATE | public |
main_table | 1 | | ROW |
AFTER | |
+ after_upd_b_row_trig | UPDATE | public |
main_table | 2 | | ROW |
AFTER | |
+ after_upd_b_stmt_trig | UPDATE | public |
main_table | 3 | | STATEMENT |
AFTER | |
+ after_upd_stmt_trig | UPDATE | public |
main_table | 4 | | STATEMENT |
AFTER | |
+ before_ins_stmt_trig | INSERT | public |
main_table | 2 | | STATEMENT |
BEFORE | |
+ before_upd_a_stmt_trig | UPDATE | public |
main_table | 5 | | STATEMENT |
BEFORE | |
+ child1_delete_trig | DELETE | public | child1
| 1 | | STATEMENT | AFTER
| old_table |
+ child1_insert_trig | INSERT | public | child1
| 1 | | STATEMENT | AFTER
| | new_table
+ child1_update_trig | UPDATE | public | child1
| 1 | | STATEMENT | AFTER
| old_table | new_table
+ child2_delete_trig | DELETE | public | child2
| 1 | | STATEMENT | AFTER
| old_table |
+ child2_insert_trig | INSERT | public | child2
| 1 | | STATEMENT | AFTER
| | new_table
+ child2_update_trig | UPDATE | public | child2
| 1 | | STATEMENT | AFTER
| old_table | new_table
+ child3_delete_trig | DELETE | public | child3
| 1 | | STATEMENT | AFTER
| old_table |
+ child3_insert_trig | INSERT | public | child3
| 1 | | STATEMENT | AFTER
| | new_table
+ child3_update_trig | UPDATE | public | child3
| 1 | | STATEMENT | AFTER
| old_table | new_table
+ parent_delete_trig | DELETE | public | parent
| 1 | | STATEMENT | AFTER
| old_table |
+ parent_insert_trig | INSERT | public | parent
| 1 | | STATEMENT | AFTER
| | new_table
+ parent_update_trig | UPDATE | public | parent
| 1 | | STATEMENT | AFTER
| old_table | new_table
+(19 rows)
+
-- insert directly into children sees respective child-format tuples
insert into child1 values ('AAA', 42);
NOTICE: trigger = child1_insert_trig, new table = (AAA,42)
diff --git a/src/test/regress/sql/triggers.sql
b/src/test/regress/sql/triggers.sql
index 47b5bde390..7abebda459 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -92,6 +92,11 @@
update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
+SELECT trigger_name, event_manipulation, event_object_schema,
event_object_table,
+ action_order, action_condition, action_orientation, action_timing,
+ action_reference_old_table, action_reference_new_table
+ FROM information_schema.triggers ORDER BY 1, 2;
+
DROP TABLE pkeys;
DROP TABLE fkeys;
DROP TABLE fkeys2;
@@ -279,6 +284,10 @@ CREATE TRIGGER insert_when BEFORE INSERT ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
CREATE TRIGGER delete_when AFTER DELETE ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
+SELECT trigger_name, event_manipulation, event_object_schema,
event_object_table,
+ action_order, action_condition, action_orientation, action_timing,
+ action_reference_old_table, action_reference_new_table
+ FROM information_schema.triggers ORDER BY 1, 2;
INSERT INTO main_table (a) VALUES (123), (456);
COPY main_table FROM stdin;
123 999
@@ -1472,6 +1481,11 @@ CREATE RULE european_city_delete_rule AS ON DELETE TO
european_city_view
after delete on child3 referencing old table as old_table
for each statement execute procedure dump_delete();
+SELECT trigger_name, event_manipulation, event_object_schema,
event_object_table,
+ action_order, action_condition, action_orientation, action_timing,
+ action_reference_old_table, action_reference_new_table
+ FROM information_schema.triggers ORDER BY 1, 2;
+
-- insert directly into children sees respective child-format tuples
insert into child1 values ('AAA', 42);
insert into child2 values ('BBB', 42);
base-commit: f069c91a5793ff6b7884120de748b2005ee7756f
--
2.16.1
