Thread: BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
From
rushabh.lathia@gmail.com
Date:
The following bug has been logged on the website:
Bug reference: 8275
Logged by: Rushabh Lathia
Email address: rushabh.lathia@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)
Re: BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
From
Rushabh Lathia
Date:
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.lathia@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 8275 > Logged by: Rushabh Lathia > Email address: rushabh.lathia@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) > > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Rushabh Lathia
Re: BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
From
Dean Rasheed
Date:
On 2 July 2013 08:44, Rushabh Lathia <rushabh.lathia@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.lathia@gmail.com> wrote: >> >> The following bug has been logged on the website: >> >> Bug reference: 8275 >> Logged by: Rushabh Lathia >> Email address: rushabh.lathia@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
Attachment
Re: BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
From
Dean Rasheed
Date:
On 2 July 2013 09:30, Dean Rasheed <dean.a.rasheed@gmail.com> wrote: > 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. On second thoughts, I think this should only apply to INSERT. UPDATE and DELETE should continue work the same as SELECT, respecting the inheritance flag from the view query regardless of the inheritance flag in the outer query. Attached is an updated patch for HEAD, with regression tests. This should also be applied to the 9.3beta branch. Regards, Dean
Attachment
Re: BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
From
Tom Lane
Date:
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> On 2 July 2013 09:30, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>> 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.
> On second thoughts, I think this should only apply to INSERT.
> UPDATE and DELETE should continue work the same as SELECT, respecting
> the inheritance flag from the view query regardless of the inheritance
> flag in the outer query.
[ thinks for a bit... ] Yeah, I agree.
> Attached is an updated patch for HEAD, with regression tests. This
> should also be applied to the 9.3beta branch.
Applied with minor adjustments --- mainly, I took out the inFromCl
twiddling, which AFAICS is neither necessary (nothing downstream of this
looks at inFromCl) nor clearly correct.
regards, tom lane