Add more information_schema columns
Here is a patch that fills in a few more information schema columns, in
particular those related to the trigger transition tables feature.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
0001-Add-more-information_schema-columns.patchtext/plain; charset=UTF-8; name=0001-Add-more-information_schema-columns.patch; x-mac-creator=0; x-mac-type=0Download
From c8e1585951859b1248f02c070929e9f83534092a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Mon, 5 Feb 2018 20:22:16 -0500
Subject: [PATCH] Add more information_schema columns
- table_constraints.enforced
- triggers.action_order
- triggers.action_reference_old_table
- triggers.action_reference_new_table
---
doc/src/sgml/information_schema.sgml | 20 +++++++++--
src/backend/catalog/information_schema.sql | 12 ++++---
src/test/regress/expected/triggers.out | 54 ++++++++++++++++++++++++++++++
src/test/regress/sql/triggers.sql | 5 +++
4 files changed, 83 insertions(+), 8 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..6066597648 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,8 @@ 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(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 +2105,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..7d60b4164f 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -96,6 +96,21 @@ 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 +362,21 @@ 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 +2021,30 @@ 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..c4df2e3687 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -92,6 +92,8 @@
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 +281,7 @@ 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 +1475,8 @@ 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
On Mon, Feb 05, 2018 at 08:59:31PM -0500, Peter Eisentraut wrote:
Here is a patch that fills in a few more information schema columns, in
particular those related to the trigger transition tables feature.
It is unfortunate that this cannot be backpatched. Here are few
comments, the logic and theh definitions look correct to me.
- 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.
- 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...
--
Michael
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
Attachments:
v2-0001-Add-more-information_schema-columns.patchtext/plain; charset=UTF-8; name=v2-0001-Add-more-information_schema-columns.patch; x-mac-creator=0; x-mac-type=0Download
From 3b8f1ebb8f8a89c802d1fdeeb839a9a26998917e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@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.paquier@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
On Tue, Feb 6, 2018 at 2:15 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
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.
What possible point can there be to such an expression? It's always 0.
rhaas=# select distinct tgtype::smallint & 1 & 66 from
generate_series(-32768,32767) tgtype;
?column?
----------
0
(1 row)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Tue, Feb 06, 2018 at 03:16:59PM -0500, Robert Haas wrote:
What possible point can there be to such an expression? It's always 0.
rhaas=# select distinct tgtype::smallint & 1 & 66 from
generate_series(-32768,32767) tgtype;
?column?
----------
0
(1 row)
Of course you are right here. I just had a look at the patch again
after waking up and the current patch builds action_order based only on
action_timing and action_orientation, but it forgets event_manipulation.
For example row and statement triggers are correctly filtered, but the
ordering number includes both insert, update and delete types. So what
you need to use instead is (t.tgtype & 1), (t.tgtype & 66) as filter.
--
Michael
On 2/6/18 17:15, Michael Paquier wrote:
On Tue, Feb 06, 2018 at 03:16:59PM -0500, Robert Haas wrote:
What possible point can there be to such an expression? It's always 0.
rhaas=# select distinct tgtype::smallint & 1 & 66 from
generate_series(-32768,32767) tgtype;
?column?
----------
0
(1 row)Of course you are right here. I just had a look at the patch again
after waking up and the current patch builds action_order based only on
action_timing and action_orientation, but it forgets event_manipulation.
For example row and statement triggers are correctly filtered, but the
ordering number includes both insert, update and delete types. So what
you need to use instead is (t.tgtype & 1), (t.tgtype & 66) as filter.
I think what I had meant to write was something like
(t.tgtype & (1 | 66))
but maybe it's clearer to write it all out as you did.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, Feb 06, 2018 at 10:45:52PM -0500, Peter Eisentraut wrote:
I think what I had meant to write was something like
(t.tgtype & (1 | 66))
but maybe it's clearer to write it all out as you did.
If you prefer that, that's fine for me as well. I tend to prefer the
formulation where both expressions are separated to make clearer that
ordering needs to be split for all three characteristics.
--
Michael
On 2/7/18 00:14, Michael Paquier wrote:
On Tue, Feb 06, 2018 at 10:45:52PM -0500, Peter Eisentraut wrote:
I think what I had meant to write was something like
(t.tgtype & (1 | 66))
but maybe it's clearer to write it all out as you did.
If you prefer that, that's fine for me as well. I tend to prefer the
formulation where both expressions are separated to make clearer that
ordering needs to be split for all three characteristics.
Committed with the separate entries.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, Feb 07, 2018 at 10:50:12AM -0500, Peter Eisentraut wrote:
Committed with the separate entries.
Thanks. The result looks fine to me.
--
Michael
On 2018-02-07 10:50:12 -0500, Peter Eisentraut wrote:
On 2/7/18 00:14, Michael Paquier wrote:
On Tue, Feb 06, 2018 at 10:45:52PM -0500, Peter Eisentraut wrote:
I think what I had meant to write was something like
(t.tgtype & (1 | 66))
but maybe it's clearer to write it all out as you did.
If you prefer that, that's fine for me as well. I tend to prefer the
formulation where both expressions are separated to make clearer that
ordering needs to be split for all three characteristics.Committed with the separate entries.
Do we have a policy about catversion bumps for information schema
changes? A cluster from before this commit fails the regression tests
after the change, but still mostly works...
Greetings,
Andres Freund
Andres Freund <andres@anarazel.de> writes:
Do we have a policy about catversion bumps for information schema
changes? A cluster from before this commit fails the regression tests
after the change, but still mostly works...
I think historically we've not bumped catversion, on the grounds that
there's no incompatibility with the backend as such. However, it is
kind of annoying that not updating means the regression tests fail.
Informally, I'm sure most developers take "catversion bump" to mean
"you need to initdb". So I'd support saying that an information_schema
change should include a catversion bump if it involves any changes in
regression test results.
regards, tom lane
On 2/13/18 18:39, Tom Lane wrote:
Andres Freund <andres@anarazel.de> writes:
Do we have a policy about catversion bumps for information schema
changes? A cluster from before this commit fails the regression tests
after the change, but still mostly works...I think historically we've not bumped catversion, on the grounds that
there's no incompatibility with the backend as such. However, it is
kind of annoying that not updating means the regression tests fail.
Informally, I'm sure most developers take "catversion bump" to mean
"you need to initdb". So I'd support saying that an information_schema
change should include a catversion bump if it involves any changes in
regression test results.
I will do that in the future if that is the preference.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services