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

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)
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
Rushabh Lathia