* Dean Rasheed (dean.a.rash...@gmail.com) wrote: > On 28 September 2015 at 20:15, Stephen Frost <sfr...@snowman.net> wrote: > > I listed out the various alternatives but didn't end up getting any > > responses to it. I'm still of the opinion that the documentation is the > > main thing which needs improving here, but we can also change CREATE > > POLICY, et al, to require an explicit WITH CHECK clause for the commands > > where that makes sense if that's the consensus. > > My vote would be to keep it as-is.
That's my feeling on it as well, particularly as... > It feels perfectly natural to me. USING clauses add to the query's > WHERE clause controlling which existing rows you can SELECT, UPDATE or > DELETE. WITH CHECK clauses control what new data you can add via > INSERT or UPDATE. UPDATE allows both, but most of the time I expect > you'll want them to be the same. exactly this. Many people are going to want them to be the same and not supporting a single-expression syntax is going to frustrate them, to no particularly good end, in my view. The "USING AND WITH CHECK" technically solves that but feels very odd to me. > So having the WITH CHECK clause default to being the same as the USING > clause for UPDATE matches what I expect to be the most common usage. > Users granted permission to update a subset of the table's rows > probably don't want to give those rows away. More advanced use-cases > are still supported, but the simplest/most common case is the default, > which means that you don't have to supply the same expression twice. Agreed. > I agree that the documentation could be improved. > > As things stand, you have to read quite a lot of text on the CREATE > POLICY page before you get to the description of how the USING and > WITH CHECK expressions interact. I'd suggest rewording the 2nd > paragraph where these clauses are first introduced. Perhaps something > like: > > """ > A policy grants the ability to SELECT, INSERT, UPDATE, or DELETE rows > which match the relevant policy expression. For SELECT, UPDATE and > DELETE, the USING expression from the policy is combined with the > query's WHERE clause to control which existing table rows can be > retrieved, updated or deleted. For INSERT and UPDATE, the WITH CHECK > expression is used to constrain what new data can be added to the > table. A policy that applies to UPDATE may have both USING and WITH > CHECK expressions, which may be different from one another, but if > they are the same, the WITH CHECK expression can be omitted and the > USING expression will be used automatically in its place. > > Policy expressions may be any expressions that evaluate to give a > result of type boolean. When a USING expression returns true for a > given row then the query is allowed to act upon that row, while rows > for which the expression returns false or null are skipped. When a > WITH CHECK expression returns true for a new row then the system > allows that row to be added to the table, but if the expression > returns false or null an error is raised. > """ I'm not convinced that this really helps, but I don't have anything dramatically better yet either. I'll try to come up with something though. Thanks! Stephen
signature.asc
Description: Digital signature
