Thread: Silent insert lack

Luca Saccarola (l.saccarola@bigfoot.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Silent insert lack

Long Description
'insert' statement using a view seems to be accepted with no error message:

  MyDB=> insert into test_u (col1, col2) values ('1', '1');
  INSERT 50371 1

but any following 'select' statements reports an empty table:

  MyDB=> select * from test_pt;
   col1 | col2
  ------+------
  {0 rows)

My environment is PostgreSQL v7.0.3 on Linux 2.2.16 (Red Hat 7.0).


Sample Code
*** using psql:

create table test_pt (col1 varchar(20), col2 varchar(20));
create view test_u as select t1.col1, t1.col2 from test_pt t1;
insert into test_u (col1, col2) values ('1', '1');
select * from test_pt;

No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> 'insert' statement using a view seems to be accepted with no error message:

Yup, many people have been confused by this before you.  The data does
actually get inserted into the physical table that underlies the view
--- whereupon you can never see it again, because anytime you try to
select from that table, the ON SELECT view rule fires.

7.1 will disallow insert into a view unless you provide a suitable ON
rule to rewrite the insert into something else.

            regards, tom lane