Обсуждение: ALTER ROLE SET/RESET for multiple options
Hi all,
ALTER ROLE SET/RESET can set/reset only one GUC parameter per one SQL today.
So when we need to set/reset multiple GUC parameter to user, it would
be burdensome work.
I'd like propose feature makes ALTER ROLE SET/RESET can set/reset
multiple options like ALTER TABLE.
ALTER USER is as well.
For example,
postgres(1)=# CREATE USER hoge_user ;
CREATE ROLE
postgres(1)=# ALTER USER hoge_user SET (log_statement = ddl,
log_min_messages = notice, application_name = 'HOGE');
ALTER ROLE
postgres(1)=# SELECT * FROM pg_db_role_setting ;
setdatabase | setrole | setconfig
-------------+---------+-------------------------------------------------------------------
0 | 16384 |
{log_statement=ddl,log_min_messages=notice,application_name=HOGE}
(1 row)
postgres(1)=# ALTER USER hoge_user RESET (log_statement, log_min_messages);
ALTER ROLE
postgres(1)=# SELECT * FROM pg_db_role_setting ;
setdatabase | setrole | setconfig
-------------+---------+-------------------------
0 | 16384 | {application_name=HOGE}
(1 row)
Attached draft v1 patch.
Please give me feedback.
Regards,
--
Masahiko Sawada
Вложения
The following review has been posted through the commitfest application: make installcheck-world: tested, failed Implements feature: tested, passed Spec compliant: tested, failed Documentation: not tested When running gmake installcheck for regression tests, 2 tests are failing: [vagrant@localhost regress]$ cat /home/vagrant/postgresql/src/test/regress/regression.diffs *** /home/vagrant/postgresql/src/test/regress/expected/int8.out 2016-02-11 22:41:33.983260509 -0500 --- /home/vagrant/postgresql/src/test/regress/results/int8.out 2016-02-11 22:51:58.631238323 -0500 *************** *** 583,593 **** SELECT AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL; to_char_13 | to_char ------------+------------------------ ! | 456.000 ! | 4567890123456789.000 ! | 123.000 ! | 4567890123456789.000 ! | -4567890123456789.000 (5 rows) SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL; --- 583,593 ---- SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL; to_char_13 | to_char ------------+------------------------ ! | $ 456.000 ! | $ 4567890123456789.000 ! | $ 123.000 ! | $ 4567890123456789.000 ! | $-4567890123456789.000 (5 rows) SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL; ====================================================================== *** /home/vagrant/postgresql/src/test/regress/expected/numeric.out 2016-02-11 22:41:33.993260509 -0500 --- /home/vagrant/postgresql/src/test/regress/results/numeric.out 2016-02-11 22:51:58.865238315 -0500 *************** *** 1061,1076 **** SELECT '' AS to_char_16, to_char(val, 'L9999999999999999.099999999999999') FROM num_data; to_char_16| to_char ------------+------------------------------------ ! | .000000000000000 ! | .000000000000000 ! | -34338492.215397047000000 ! | 4.310000000000000 ! | 7799461.411900000000000 ! | 16397.038491000000000 ! | 93901.577630260000000 ! | -83028485.000000000000000 ! | 74881.000000000000000 ! | -24926804.045047420000000 (10 rows) SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999') FROM num_data; --- 1061,1076 ---- SELECT '' AS to_char_16, to_char(val, 'L9999999999999999.099999999999999') FROM num_data; to_char_16| to_char ------------+------------------------------------ ! | $ .000000000000000 ! | $ .000000000000000 ! | $ -34338492.215397047000000 ! | $ 4.310000000000000 ! | $ 7799461.411900000000000 ! | $ 16397.038491000000000 ! | $ 93901.577630260000000 ! | $ -83028485.000000000000000 ! | $ 74881.000000000000000 ! | $ -24926804.045047420000000 (10 rows) SELECT AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999') FROM num_data; ====================================================================== The feature seems to work as described, but is it necessary to enclose multiple GUC settings in a parenthesis? This seemsa deviation from the usual syntax of altering multiple settings separated with comma. Will test out more once I receive a response from the author. The new status of this patch is: Waiting on Author
On Fri, Feb 12, 2016 at 1:35 PM, Payal Singh <payal@omniti.com> wrote: > The feature seems to work as described, but is it necessary to enclose multiple GUC settings in a parenthesis? This seemsa deviation from the usual syntax of altering multiple settings separated with comma. Well, note that you can say: ALTER USER bob SET search_path = a, b, c; I'm not sure how the parentheses help exactly; it seems like there is an inherit ambiguity either way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sat, Feb 13, 2016 at 2:45 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Fri, Feb 12, 2016 at 1:35 PM, Payal Singh <payal@omniti.com> wrote: >> The feature seems to work as described, but is it necessary to enclose multiple GUC settings in a parenthesis? This seemsa deviation from the usual syntax of altering multiple settings separated with comma. > > Well, note that you can say: > > ALTER USER bob SET search_path = a, b, c; > > I'm not sure how the parentheses help exactly; it seems like there is > an inherit ambiguity either way. > I thought it would be useful for user who wants to set several GUC parameter for each user. Especially the case where changing logging parameter for each user. But it might not bring us fantastic usability. Regards, -- Masahiko Sawada
On Wed, Feb 17, 2016 at 3:22 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: > On Sat, Feb 13, 2016 at 2:45 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Fri, Feb 12, 2016 at 1:35 PM, Payal Singh <payal@omniti.com> wrote: >>> The feature seems to work as described, but is it necessary to enclose multiple GUC settings in a parenthesis? This seemsa deviation from the usual syntax of altering multiple settings separated with comma. >> >> Well, note that you can say: >> >> ALTER USER bob SET search_path = a, b, c; >> >> I'm not sure how the parentheses help exactly; it seems like there is >> an inherit ambiguity either way. >> > > I thought it would be useful for user who wants to set several GUC > parameter for each user. Especially the case where changing logging > parameter for each user. > But it might not bring us fantastic usability. Yeah, it doesn't really seem like it's worth trying to figure out a syntax for this that can work. It just doesn't buy us very much vs. issuing one ALTER COMMAND per setting. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Feb 17, 2016 at 7:14 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, Feb 17, 2016 at 3:22 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: >> On Sat, Feb 13, 2016 at 2:45 PM, Robert Haas <robertmhaas@gmail.com> wrote: >>> On Fri, Feb 12, 2016 at 1:35 PM, Payal Singh <payal@omniti.com> wrote: >>>> The feature seems to work as described, but is it necessary to enclose multiple GUC settings in a parenthesis? Thisseems a deviation from the usual syntax of altering multiple settings separated with comma. >>> >>> Well, note that you can say: >>> >>> ALTER USER bob SET search_path = a, b, c; >>> >>> I'm not sure how the parentheses help exactly; it seems like there is >>> an inherit ambiguity either way. >>> >> >> I thought it would be useful for user who wants to set several GUC >> parameter for each user. Especially the case where changing logging >> parameter for each user. >> But it might not bring us fantastic usability. > > Yeah, it doesn't really seem like it's worth trying to figure out a > syntax for this that can work. It just doesn't buy us very much vs. > issuing one ALTER COMMAND per setting. > Yeah, please mark this patch as 'rejected'. If I can come up with another good idea, will post. Regards, -- Masahiko Sawada
On Wed, Feb 17, 2016 at 7:23 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: > On Wed, Feb 17, 2016 at 7:14 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Wed, Feb 17, 2016 at 3:22 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: >>> On Sat, Feb 13, 2016 at 2:45 PM, Robert Haas <robertmhaas@gmail.com> wrote: >>>> On Fri, Feb 12, 2016 at 1:35 PM, Payal Singh <payal@omniti.com> wrote: >>>>> The feature seems to work as described, but is it necessary to enclose multiple GUC settings in a parenthesis? Thisseems a deviation from the usual syntax of altering multiple settings separated with comma. >>>> >>>> Well, note that you can say: >>>> >>>> ALTER USER bob SET search_path = a, b, c; >>>> >>>> I'm not sure how the parentheses help exactly; it seems like there is >>>> an inherit ambiguity either way. >>>> >>> >>> I thought it would be useful for user who wants to set several GUC >>> parameter for each user. Especially the case where changing logging >>> parameter for each user. >>> But it might not bring us fantastic usability. >> >> Yeah, it doesn't really seem like it's worth trying to figure out a >> syntax for this that can work. It just doesn't buy us very much vs. >> issuing one ALTER COMMAND per setting. >> > > Yeah, please mark this patch as 'rejected'. > If I can come up with another good idea, will post. Done so. -- Michael