Hi guys, I’m seeing some non-intuitive behavior with the new updateable security barrier views in 9.4. Below is the
behaviorof 9.4b3:<div class=""><br class="" /></div><div class=""><div class="" style="margin: 0px; font-size: 11px;
font-family:Menlo;"></div><blockquote class="" type="cite"><div class="" style="margin: 0px; font-size: 11px;
font-family:Menlo;">=# create table widget ( id integer);</div><div class="" style="margin: 0px; font-size: 11px;
font-family:Menlo;">CREATE TABLE</div><div class="" style="margin: 0px; font-size: 11px; font-family: Menlo;">=# create
viewwidget_sb WITH (security_barrier=true) AS SELECT * from widget where id = 22;</div><div class="" style="margin:
0px;font-size: 11px; font-family: Menlo;">CREATE VIEW</div><div class="" style="margin: 0px; font-size: 11px;
font-family:Menlo;">=# insert into widget_sb (id) values (23);</div><div class="" style="margin: 0px; font-size: 11px;
font-family:Menlo;">INSERT 0 1</div><div class="" style="margin: 0px; font-size: 11px; font-family: Menlo;">=# select *
fromwidget;</div><div class="" style="margin: 0px; font-size: 11px; font-family: Menlo;"> id </div><div class=""
style="margin:0px; font-size: 11px; font-family: Menlo;">----</div><div class="" style="margin: 0px; font-size: 11px;
font-family:Menlo;"> 23</div><div class="" style="margin: 0px; font-size: 11px; font-family: Menlo;">(1
row)</div></blockquote></div><divclass=""><br class="" /></div><div class="">I think the insert should fail, since the
viewcan only contain widgets with id = 22, and the widget being inserted has id 23. In reality, the insert to the
behindtable succeeds as shown above, although it still remains absent from the view:</div><div class=""><br class=""
/></div><divclass=""><blockquote class="" type="cite">=# select * from widget_sb;<br class="" /> id <br class=""
/>----<brclass="" />(0 rows)</blockquote></div><div class=""><br class="" /></div><div class="">IMHO this is
nonintuitive,the intuitive behavior of a security_barrier view should be to forbid inserting rows that can’t appear in
theview.</div><div class=""><br class="" /></div><div class="">Have I missed something fundamental here?</div><div
class=""><brclass="" /></div><div class="">Drew</div>