Re: Rules on table partitions

On 2017/06/20 17:51, Dean Rasheed wrote:
> On 20 June 2017 at 03:01, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> On 2017/06/19 20:19, Dean Rasheed wrote:
>>> Perhaps we
>>> should explicitly forbid this for now -- i.e., raise a "not supported"
>>> error when attempting to add a rule to a partition, or attach a table
>>> with rules to a partitioned table.
>>
>> We could do that, but an oft-raised question is how different we should
>> make new partitions from the old-style inheritance child tables?
>>
>> Although a slightly different territory, you will also notice that
>> statement triggers of partitions won't be fired unless they are explicitly
>> named in the query, which is what happens for inheritance in general and
>> hence also for partitions.
>>
>>> Personally, I wouldn't regard adding proper support for rules on
>>> partitions as a high priority, so I'd be OK with it remaining
>>> unsupported unless someone cares enough to implement it, but that
>>> seems preferable to leaving it partially working in this way.
>>
>> Sure, if consensus turns out to be that we prohibit rules, statement
>> triggers, etc. that depend on the relation being explicitly named in the
>> query to be defined on partitions, I could draft up a patch for v10.
>>
>
> Hmm, perhaps it's OK as-is. The user can always define the
> rules/triggers on both the parent and the children, if that's what
> they want.

I feel that the documentation in this area could clarify some of these
things a bit more (at least any differences that exist between the
old-style inheritance and new partitioning). I will try to see if I can
come up with a sensible patch for that.

>>> Also, as things stand, it is possible to do the following:
>>>
>>> CREATE TABLE t2(a int, b int) PARTITION BY RANGE(a);
>>> CREATE TABLE t2_p PARTITION OF t2 FOR VALUES FROM (1) TO (10);
>>> CREATE RULE "_RETURN" AS ON SELECT TO t2_p DO INSTEAD SELECT * FROM t2;
>>>
>>> which results in the partition becoming a view that selects from the
>>> parent, which surely ought to be forbidden.
>>
>> Hmm, yes. The following exercise convinced me.
>>
>> create table r (a int) partition by range (a);
>> create table r1 partition of r for values from (1) to (10);
>> create rule "_RETURN" as on select to r1 do instead select * from r;
>>
>> insert into r values (1);
>> ERROR: cannot insert into view "r1"
>> HINT: To enable inserting into the view, provide an INSTEAD OF INSERT
>> trigger or an unconditional ON INSERT DO INSTEAD rule.
>>
>> The error is emitted by CheckValidResultRel() that is called on individual
>> leaf partitions when setting up tuple-routing in ExecInitModifyTable.
>>
>> I agree that we should forbid this case,
>
> Yeah. Also it would be possible to define the rule to select from a
> non-empty table, leading to rows appearing in the partition, but not
> the parent. Since we normally explicitly forbid the use of a view as a
> table partition, it seems worth closing the loophole in this case.

Oh, that's even worse. :-(

>> so please find attached a patch.
>
> Thanks, I'll take a look at it later today.

Thanks.

Regards,
Amit