On 2 July 2013 08:44, Rushabh Lathia <rushabh.lat...@gmail.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.lat...@gmail.com> wrote: >> >> The following bug has been logged on the website: >> >> Bug reference: 8275 >> Logged by: Rushabh Lathia >> Email address: rushabh.lat...@gmail.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
updatable-views-inh.patch
Description: Binary data
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
