Re: BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement

On 2 July 2013 08:44, Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> wrote:
> Looking further I just found that, if we don't want query to scan through
> child table then we should use ONLY during CREATE VIEW.
>
> So if I replaced my create view query with:
>
> CREATE view view_tp_sales as SELECT * FROM ONLY tp_sales;
>
> Then INSERT stmt working find.
>
> So when you create VIEW on top of inheritance (partition) table you need to
> create it using ONLY keyword, right ?
> anyone please correct me if I am wrong.
>
> Regards,
> Rushabh
>
>
> On Tue, Jul 2, 2013 at 10:29 AM, <rushabh(dot)lathia(at)gmail(dot)com> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 8275
>> Logged by: Rushabh Lathia
>> Email address: rushabh(dot)lathia(at)gmail(dot)com
>> PostgreSQL version: 9.2.4
>> Operating system: All
>> Description:
>>
>> View based on inheritance throws error on insert statement.
>>
>>
>> Testcase:
>>
>>
>> DROP TABLE tp_sales cascade;
>>
>>
>> CREATE TABLE tp_sales
>> (
>> salesman_id INT4,
>> salesman_name VARCHAR,
>> sales_region VARCHAR,
>> sales_amount INT4
>> );
>>
>>
>> create table tp_sales_p_india ( check (sales_region = 'INDIA')) inherits
>> (tp_sales);
>> create table tp_sales_p_rest (check (sales_region != 'INDIA')) inherits
>> (tp_sales);
>>
>>
>> CREATE OR REPLACE FUNCTION tp_sales_insert_trigger()
>> RETURNS TRIGGER AS $$
>> BEGIN
>> IF NEW.sales_region = 'INDIA' THEN
>> INSERT INTO tp_sales_p_india VALUES (NEW.*);
>> ELSE
>> INSERT INTO tp_sales_p_rest VALUES (NEW.*);
>> END IF;
>> RETURN NULL;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>
>>
>>
>>
>> CREATE TRIGGER insert_tp_sales_trigger
>> BEFORE INSERT ON tp_sales
>> FOR EACH ROW EXECUTE PROCEDURE tp_sales_insert_trigger();
>>
>>
>> INSERT INTO tp_sales VALUES (100,'Foo','INDIA',15000);
>> INSERT INTO tp_sales VALUES (110,'Bar','UK',24000);
>>
>>
>> CREATE view view_tp_sales as SELECT * FROM tp_sales;
>>
>>
>> -- run insert on view
>> postgres=# INSERT INTO view_tp_sales VALUES (120,'XYZ','INDIA',11000);
>> ERROR: new row for relation "tp_sales_p_rest" violates check constraint
>> "tp_sales_p_rest_sales_region_check"
>> DETAIL: Failing row contains (120, XYZ, INDIA, 11000).
>> postgres=# INSERT INTO view_tp_sales VALUES (120,'ABC','HELLO',11000);
>> ERROR: new row for relation "tp_sales_p_india" violates check constraint
>> "tp_sales_p_india_sales_region_check"
>> DETAIL: Failing row contains (120, ABC, HELLO, 11000).
>> postgres=# select version();
>> version
>>
>>
>> -----------------------------------------------------------------------------------------------------------------
>> PostgreSQL 9.4devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
>> 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
>> (1 row)
>>

This is a bug. Thanks for the report!

I think the rewritten query should only use inheritance if inheritance
was requested in the original query, *and* if inheritance was enabled
in the view's query, per attached patch against HEAD.
We should probably also include some additional regression tests for
these kinds of query.

Regards,
Dean