From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Amit Langote <amitlangote09(at)gmail(dot)com>, Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Declarative partitioning - another take |
Date: | 2016-10-26 02:00:15 |
Message-ID: | f6985c0d-6981-8423-f28d-53b15a4a8cb8@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2016/10/25 15:58, Amit Kapila wrote:
> On Thu, Oct 6, 2016 at 12:44 PM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Chapman Flack | 2016-10-26 02:09:32 | Re: 9.6, background worker processes, and PL/Java |
Previous Message | Amit Langote | 2016-10-26 01:06:14 | Re: Declarative partitioning - another take |