[HACKERS] Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs
On Tue, Jul 30, 2013 at 09:23:19AM +0100, Dean Rasheed wrote: create table some_data (id int4 primary key, payload text); create view first as select * from some_data where 0 = id % 2 with local check option; create view second as select * from first where 0 = id with local check option; [...] the check is 0 = id % 3 - i.e. id has to be multiply of 3. Sorry if my way of writing conditionals is confusing. Yes it definitely looks like a typo in the test --- the definition of first has id % 2, so it is checking for even numbers, not for numbers divisible by 3. Sorry, my bad - must have screwed copy/paste. the second view is: select * from first where 0 = id % 3 with local check option; As for the point about which of the checks should be failing, I believe that the current behaviour is correct. In such case, can you show me what is the difference of local check and cascaded check? Because I assumed, after reading the commit log, that local checks just the view definition of the view I'm inserting to, and the cascaded check, checks all the views upstream. Given the assumption that current code works correctly - both checks check also the upstream view. Best regards, depesz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs
On Tue, Jul 30, 2013 at 11:45:47AM +0100, Dean Rasheed wrote: Quoting the manual: LOCAL: New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION). CASCADED: New rows are checked against the conditions of the view and all underlying base views. If the CHECK OPTION is specified, and neither LOCAL nor CASCADED is specified, then CASCADED is assumed. In particular, note the part about unless they also specify the CHECK OPTION. Ah. All clear now. Sorry for misreading. Best regards, depesz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs
depesz, moved to -hackers.. * hubert depesz lubaczewski (dep...@depesz.com) wrote: create table some_data (id int4 primary key, payload text); create view first as select * from some_data where 0 = id % 2 with local check option; create view second as select * from first where 0 = id with local check option; insert into second (id, payload) values (15, '15 is divisible by 3, but not by 2'); ERROR: new row violates WITH CHECK OPTION for view first DETAIL: Failing row contains (15, 15 is divisible by 3, but not by 2). Interesting. If I read it correctly, insert to second with id = 15 should work, because the where on second matches, and local check shouldn't be checking on first? I think you're half-right, the check should be against 'second', which should fail because of the '0 = id' check in that view, but we're reporting the failure as being associated with 'first', which I don't believe is correct. Dean, thoughts..? Thanks! Stephen signature.asc Description: Digital signature
[HACKERS] Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs
On Mon, Jul 29, 2013 at 07:43:53PM -0400, Stephen Frost wrote: depesz, moved to -hackers.. * hubert depesz lubaczewski (dep...@depesz.com) wrote: create table some_data (id int4 primary key, payload text); create view first as select * from some_data where 0 = id % 2 with local check option; create view second as select * from first where 0 = id with local check option; insert into second (id, payload) values (15, '15 is divisible by 3, but not by 2'); ERROR: new row violates WITH CHECK OPTION for view first DETAIL: Failing row contains (15, 15 is divisible by 3, but not by 2). Interesting. If I read it correctly, insert to second with id = 15 should work, because the where on second matches, and local check shouldn't be checking on first? I think you're half-right, the check should be against 'second', which should fail because of the '0 = id' check in that view, but we're reporting the failure as being associated with 'first', which I don't believe is correct. Dean, thoughts..? the check is 0 = id % 3 - i.e. id has to be multiply of 3. Sorry if my way of writing conditionals is confusing. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs
depesz, * hubert depesz lubaczewski (dep...@depesz.com) wrote: On Mon, Jul 29, 2013 at 07:43:53PM -0400, Stephen Frost wrote: * hubert depesz lubaczewski (dep...@depesz.com) wrote: create table some_data (id int4 primary key, payload text); create view first as select * from some_data where 0 = id % 2 with local check option; create view second as select * from first where 0 = id with local check option; [...] the check is 0 = id % 3 - i.e. id has to be multiply of 3. Sorry if my way of writing conditionals is confusing. Neither client that I use to read email with saw a '% 3' on the view definition for 'second' in your original email (or as quoted above). Still, I do see what you're talking about and will take a look. Thanks, Stephen signature.asc Description: Digital signature
