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.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

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

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

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs