Re: Declarative partitioning - another take

Hi Rajkumar,

On 2017/04/28 17:11, Rajkumar Raghuwanshi wrote:
> On Fri, Apr 28, 2017 at 11:43 AM, Amit Langote <
>> Updated patch attached.
>
> I have applied given patch, could see below behaviour with statement
> trigger.
>
> When trying to delete value within partition range, triggers got fired
> (with zero row affected) as expected, but if trying to delete value which
> is outside of partition range (with zero row affected), No trigger fired.
> is this expected??

Good catch.

I'm afraid though that this is not a defect of this patch, but some
unrelated (maybe) bug, which affects not only the partitioned tables but
inheritance in general.

Problem is that the plan created is such that the executor has no
opportunity to fire the trigger in question, because the plan contains no
information about which table is affected by the statement. You can see
that with inheritance. See below:

create table foo ();
create table bar () inherits (foo);

create or replace function trig_notice() returns trigger as $$
begin raise notice 'trigger fired'; return null; end;
$$ language plpgsql;

create trigger foo_del_before before delete on foo
for each statement execute procedure trig_notice();

explain delete from foo where false;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

-- no trigger fired
delete from foo where false;
DELETE 0

Trigger *is* fired though, if inheritance is not used.

explain delete from only foo where false;
QUERY PLAN
-------------------------------------------------
Delete on foo (cost=0.00..0.00 rows=0 width=0)
-> Result (cost=0.00..0.00 rows=0 width=6)
One-Time Filter: false
(3 rows)

delete from only foo where false;
NOTICE: trigger fired
DELETE 0

I'm not sure how to go about fixing this, if at all. Or to fix it at
least for partitioned tables. Would like to hear what others think.

Thanks,
Amit