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
Attachments:
000_alter_role_multiple_set_v1.patchapplication/octet-stream; name=000_alter_role_multiple_set_v1.patchDownload+163-95
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 seems a 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 seems 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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 seems 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.
Regards,
--
Masahiko Sawada
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 seems 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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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? This seems 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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? This seems 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers