Re: [HACKERS] WITH CHECK OPTION for auto-updatable views
On 18 July 2013 22:27, Stephen Frost sfr...@snowman.net wrote: Dean, * Stephen Frost (sfr...@snowman.net) wrote: Thanks! This is really looking quite good, but it's a bit late and I'm going on vacation tomorrow, so I didn't quite want to commit it yet. :) Apologies on this taking a bit longer than I expected, but it's been committed and pushed now. Please take a look and let me know of any issues you see with the changes that I made. Excellent. Thank you! The changes look good to me. Cheers, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WITH CHECK OPTION for auto-updatable views
Dean, * Stephen Frost (sfr...@snowman.net) wrote: Thanks! This is really looking quite good, but it's a bit late and I'm going on vacation tomorrow, so I didn't quite want to commit it yet. :) Apologies on this taking a bit longer than I expected, but it's been committed and pushed now. Please take a look and let me know of any issues you see with the changes that I made. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] WITH CHECK OPTION for auto-updatable views
Hello just some notes: * autocomplete for INSERT, UPDATE, DELETE should to show updatable views too * can you explain better in doc differences between WITH CASCADED or WITH LOCAL OPTION - assign some simple example to doc, please * is possible to better identify (describe) failed constraints? postgres=# create view v1 as select * from bubu where a 0; CREATE VIEW postgres=# create view v2 as select * from v1 where a 10 with check option; CREATE VIEW postgres=# insert into v1 values(-10); INSERT 0 1 postgres=# insert into v2 values(-10); ERROR: new row violates WITH CHECK OPTION for view v2 --- but this constraint is related to v1 DETAIL: Failing row contains (-10). * I found a difference against MySQL - LOCAL option ignore all other constraints postgres=# CREATE TABLE t1 (a INT); CREATE TABLE postgres=# CREATE VIEW v1 AS SELECT * FROM t1 WHERE a 2 WITH CHECK OPTION; CREATE VIEW postgres=# CREATE VIEW v2 AS SELECT * FROM v1 WHERE a 0 WITH LOCAL CHECK OPTION; CREATE VIEW postgres=# INSERT INTO v2 VALUES (2); ERROR: new row violates WITH CHECK OPTION for view v1 -- it will be ok on MySQL DETAIL: Failing row contains (2). Probably MySQL is wrong (due differet behave than in DB2) -- but who know http://bugs.mysql.com/bug.php?id=6404 What is a correct behave? Regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WITH CHECK OPTION for auto-updatable views
On 5 July 2013 07:02, Pavel Stehule pavel.steh...@gmail.com wrote: Hello I try to check this patch I have a problem with initdb after patching error initializing dependencies ... ok creating system views ... FATAL: WITH CHECK OPTION is supported only on auto-updatable views STATEMENT: /* I found missing initialization (strange, gcc doesn't raise warnings :( ) + boolcheck_option; + boolsecurity_barrier; Ah, good catch. I was being careless there. It turns out that although I compile with -Wall which implies -Wuninitialized and -Wmaybe-uninitialized, those warnings are only supported in optimised builds, which is why I didn't see it. So I've learned something new today: always do an optimised build as well during development. Will fix. Thanks. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WITH CHECK OPTION for auto-updatable views
Dean, * Dean Rasheed (dean.a.rash...@gmail.com) wrote: Here's an updated version --- I missed the necessary update to the check_option column of information_schema.views. Thanks! This is really looking quite good, but it's a bit late and I'm going on vacation tomorrow, so I didn't quite want to commit it yet. :) Instead, here are a few things that I'd like to see fixed up: I could word-smith the docs all day, most likely, but at least the following would be nice to have cleaned up: - 'This is parameter may be either' - I don't like This allows an existing view's The option can be used on CREATE VIEW as well as ALTER VIEW. I'd say something like: This parameter may be either literallocal/ or literalcascaded/, and is equivalent to specifying literalWITH [ CASCADED | LOCAL ] CHECK OPTION/ (see below). This option can be changed on existing views using xref linkend=sql-alterview. - wrt what shows up in '\h create view' and '\h alter view', I think we should go ahead and add in with the options are, ala EXPLAIN. That avoids having to guess at it (I was trying 'with_check_option' initially :). - Supposedly, this option isn't available for RECURSIVE views, but it's happily accepted: =*# create recursive view qq (a) with (check_option = local) as select z from q; CREATE VIEW (same is true of ALTER VIEW on a RECURSIVE view) - pg_dump support is there, but it outputs the definition using the PG syntax instead of the SQL syntax; is there any particular reason for this..? imv, we should be dumping SQL spec where we can trivially do so. - Why check_option_offset instead of simply check_option..? We don't have security_barrier_offset and it seems like we should be consistent there. The rest looks pretty good to me. If you can fix the above, I'll review again and would be happy to commit it. :) Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] WITH CHECK OPTION for auto-updatable views
On 9 June 2013 11:14, Dean Rasheed dean.a.rash...@gmail.com wrote: Here's a rebased version of the patch implementing WITH CHECK OPTION for auto-updatable views. It now includes documentation, and a clearer description of the patch's limitations --- WITH CHECK OPTION is only supported on auto-updatable views, not trigger-updatable or rule-updatable views. I believe that's compatible with the following features from the SQL standard: F311-04 Schema definition statement CREATE VIEW: WITH CHECK OPTION F751View CHECK enhancements Here's an updated version --- I missed the necessary update to the check_option column of information_schema.views. Regards, Dean with-check-option.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
