On 2016/10/25 15:58, Amit Kapila wrote: > On Thu, Oct 6, 2016 at 12:44 PM, Amit Langote > <langote_amit...@lab.ntt.co.jp> wrote: >> On 2016/10/05 2:12, Robert Haas wrote: >>> Hmm, do we ever fire triggers on the parent for operations on a child >>> table? Note this thread, which seems possibly relevant: >>> >>> https://www.postgresql.org/message-id/flat/cd282adde5b70b20c57f53bb9ab75e27%40biglumber.com >> >> The answer to your question is no. >> >> The thread you quoted discusses statement-level triggers and the >> conclusion is that they don't work as desired for UPDATE and DELETE on >> inheritance tables. As things stand, only UPDATE or DELETE on the parent >> affects the child tables and it's proposed there that the statement-level >> triggers on the parent and also on any child tables affected should be >> fired in that case. >> > > Doesn't that imply that the statement level triggers should be fired > for all the tables that get changed for statement? If so, then in > your case it should never fire for parent table, which means we could > disallow statement level triggers as well on parent tables?
I may have misunderstood statement-level triggers, but don't they apply to tables *specified* as the target table in the statement, instead of those *changed* by resulting actions? Now in case of inheritance, unless ONLY is specified, all tables in the hierarchy including the parent are *implicitly* specified to be affected by an UPDATE or DELETE operation. So, if some or all of those tables have any statement-level triggers defined, they should get fired. That was the conclusion of that thread, but that TODO item still remains [1]. I am not (or no longer) sure how that argument affects INSERT on partitioned tables with tuple-routing though. Are partitions at all levels *implicitly specified to be affected* when we say INSERT INTO root_partitioned_table? > Some of the other things that we might want to consider disallowing on > parent table could be: > a. Policy on table_name Perhaps. Since there are no rows in the parent table(s) itself of a partition hierarchy, it might not make sense to continue to allow creating row-level security policies on them. > b. Alter table has many clauses, are all of those allowed and will it > make sense to allow them? Currently, we only disallow the following with partitioned parent tables as far as alter table is concerned. - cannot change inheritance by ALTER TABLE partitioned_table INHERIT ... - cannot let them be regular inheritance parents either - that is, the following is disallowed: ALTER TABLE some_able INHERIT partitioned_table - cannot create UNIQUE, PRIMARY KEY, FOREIGN KEY, EXCLUDE constraints - cannot drop column involved in the partitioning key Most other forms that affect attributes and constraints follow the regular inheritance behavior (recursion) with certain exceptions such as: - cannot add/drop an attribute or check constraint to *only* to/from the parent - cannot add/drop NOT NULL constraint to/from *only* the parent Thoughts? Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
