ALTER SYSTEM RESET?

On Wed, Jun 25, 2014 at 6:20 PM, Christoph Berg <cb@df7cb.de> wrote:

Hi,

is there a reason there's no ALTER SYSTEM RESET?

The natural idiom to reset SET statements is "RESET guc;", I don't
think "SET guc = default;" is in use much, so "ALTER SYSTEM RESET guc;"
would be the natural way to try.

Currently you can achieve that by
"ALTER SYSTEM RESET guc = Default;".
However it will be good to have support for RESET as well. I think it
should not be too complicated to implement that syntax, I personally
don't have bandwidth to it immediately, but I would like to take care
of it unless you or someone wants to do it by the time I get some
bandwidth.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Wed, Jun 25, 2014 at 10:04 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

On Wed, Jun 25, 2014 at 6:20 PM, Christoph Berg <cb@df7cb.de> wrote:

Hi,

is there a reason there's no ALTER SYSTEM RESET?

The natural idiom to reset SET statements is "RESET guc;", I don't
think "SET guc = default;" is in use much, so "ALTER SYSTEM RESET guc;"
would be the natural way to try.

Currently you can achieve that by
"ALTER SYSTEM RESET guc = Default;".
However it will be good to have support for RESET as well. I think it
should not be too complicated to implement that syntax, I personally
don't have bandwidth to it immediately, but I would like to take care
of it unless you or someone wants to do it by the time I get some
bandwidth.

I have some time then I can provide a patch in a few days... Is ok for you
guys?

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On 06/25/2014 03:04 PM, Amit Kapila wrote:

On Wed, Jun 25, 2014 at 6:20 PM, Christoph Berg <cb@df7cb.de
<mailto:cb@df7cb.de>> wrote:

Hi,

is there a reason there's no ALTER SYSTEM RESET?

The natural idiom to reset SET statements is "RESET guc;", I don't
think "SET guc = default;" is in use much, so "ALTER SYSTEM RESET guc;"
would be the natural way to try.

Currently you can achieve that by
"ALTER SYSTEM RESET guc = Default;".
However it will be good to have support for RESET as well. I think it
should not be too complicated to implement that syntax, I personally
don't have bandwidth to it immediately, but I would like to take care
of it unless you or someone wants to do it by the time I get some
bandwidth.

Would something like this suffice?
--
Vik

On Wed, Jun 25, 2014 at 1:26 PM, Vik Fearing <vik.fearing@dalibo.com> wrote:

On 06/25/2014 03:04 PM, Amit Kapila wrote:

On Wed, Jun 25, 2014 at 6:20 PM, Christoph Berg <cb@df7cb.de
<mailto:cb@df7cb.de>> wrote:

Hi,

is there a reason there's no ALTER SYSTEM RESET?

The natural idiom to reset SET statements is "RESET guc;", I don't
think "SET guc = default;" is in use much, so "ALTER SYSTEM RESET guc;"
would be the natural way to try.

Currently you can achieve that by
"ALTER SYSTEM RESET guc = Default;".
However it will be good to have support for RESET as well. I think it
should not be too complicated to implement that syntax, I personally
don't have bandwidth to it immediately, but I would like to take care
of it unless you or someone wants to do it by the time I get some
bandwidth.

Would something like this suffice?

Is fine to me...

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On Wed, Jun 25, 2014 at 9:56 PM, Vik Fearing <vik.fearing@dalibo.com> wrote:

On 06/25/2014 03:04 PM, Amit Kapila wrote:

Currently you can achieve that by
"ALTER SYSTEM RESET guc = Default;".
However it will be good to have support for RESET as well. I think it
should not be too complicated to implement that syntax, I personally
don't have bandwidth to it immediately, but I would like to take care
of it unless you or someone wants to do it by the time I get some
bandwidth.

Would something like this suffice?

I think it will make sense if we support RESET ALL as well similar
to Alter Database .. RESET ALL syntax. Do you see any reason
why we shouldn't support RESET ALL syntax for Alter System?

About patch:

+ | ALTER SYSTEM_P RESET var_name
+ {
+ AlterSystemStmt *n = makeNode(AlterSystemStmt);
+ n->setstmt = makeNode(VariableSetStmt);
+ n->setstmt->kind = VAR_RESET;
+ n->setstmt->name = $4;
+ $ = (Node *)n;
+ }

I think it would be better to have ALTER SYSTEM_P as generic and
then SET | RESET as different versions, something like below:

| SET reloptions
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_SetRelOptions;
n->def = (Node *)$2;
$$ = (Node *)n;
}
/* ALTER TABLE <name> RESET (...) */
| RESET reloptions
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_ResetRelOptions;
n->def = (Node *)$2;
$$ = (Node *)n;
}

Another point is that if we decide to support RESET ALL syntax, then
we might want reuse VariableResetStmt, may be by breaking into
generic and specific like we have done for generic_set.

Thanks for working on patch.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: Amit Kapila 2014-06-26 <CAA4eK1+Zt_Uo-z7WjP2yznv7AYTxGBqgp23wCMPuB9SdMgyGcQ@mail.gmail.com>

On Wed, Jun 25, 2014 at 9:56 PM, Vik Fearing <vik.fearing@dalibo.com> wrote:

On 06/25/2014 03:04 PM, Amit Kapila wrote:

Currently you can achieve that by
"ALTER SYSTEM RESET guc = Default;".
However it will be good to have support for RESET as well. I think it
should not be too complicated to implement that syntax, I personally
don't have bandwidth to it immediately, but I would like to take care
of it unless you or someone wants to do it by the time I get some
bandwidth.

Would something like this suffice?

I think it will make sense if we support RESET ALL as well similar
to Alter Database .. RESET ALL syntax. Do you see any reason
why we shouldn't support RESET ALL syntax for Alter System?

RESET ALL would definitely be useful to have as well.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 06/26/2014 05:07 AM, Amit Kapila wrote:

On Wed, Jun 25, 2014 at 9:56 PM, Vik Fearing <vik.fearing@dalibo.com
<mailto:vik.fearing@dalibo.com>> wrote:

On 06/25/2014 03:04 PM, Amit Kapila wrote:

Currently you can achieve that by
"ALTER SYSTEM RESET guc = Default;".
However it will be good to have support for RESET as well. I think it
should not be too complicated to implement that syntax, I personally
don't have bandwidth to it immediately, but I would like to take care
of it unless you or someone wants to do it by the time I get some
bandwidth.

Would something like this suffice?

I think it will make sense if we support RESET ALL as well similar
to Alter Database .. RESET ALL syntax. Do you see any reason
why we shouldn't support RESET ALL syntax for Alter System?

Yes, that makes sense. I've added that in the attached version 2 of the
patch.

About patch:

+ | ALTER SYSTEM_P RESET var_name
+ {
+ AlterSystemStmt *n = makeNode(AlterSystemStmt);
+ n->setstmt = makeNode(VariableSetStmt);
+ n->setstmt->kind = VAR_RESET;
+ n->setstmt->name = $4;
+ $ = (Node *)n;
+ }

I think it would be better to have ALTER SYSTEM_P as generic and
then SET | RESET as different versions, something like below:

| SET reloptions
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_SetRelOptions;
n->def = (Node *)$2;
$$ = (Node *)n;
}
/* ALTER TABLE <name> RESET (...) */
| RESET reloptions
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_ResetRelOptions;
n->def = (Node *)$2;
$$ = (Node *)n;
}

Another point is that if we decide to support RESET ALL syntax, then
we might want reuse VariableResetStmt, may be by breaking into
generic and specific like we have done for generic_set.

I didn't quite follow your ALTER TABLE example because I don't think
it's necessary, but I did split out VariableResetStmt like you suggested
because I think that is indeed cleaner.

Thanks for working on patch.

You're welcome.
--
Vik

On Thu, Jun 26, 2014 at 8:17 PM, Vik Fearing <vik.fearing@dalibo.com> wrote:

On 06/26/2014 05:07 AM, Amit Kapila wrote:

I think it will make sense if we support RESET ALL as well similar
to Alter Database .. RESET ALL syntax. Do you see any reason
why we shouldn't support RESET ALL syntax for Alter System?

Yes, that makes sense. I've added that in the attached version 2 of the
patch.

I think the idea used in patch to implement RESET ALL is sane. In passing
by, I noticed that modified lines in .sgml have crossed 80 char
boundary which
is generally preferred to be maintained..

Refer below modification.

! values to the <filename>postgresql.auto.conf</filename> file. Setting
the parameter to
! <literal>DEFAULT</literal>, or using the <command>RESET</command>
variant, removes the configuration entry from

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On 06/27/2014 06:22 AM, Amit Kapila wrote:

On Thu, Jun 26, 2014 at 8:17 PM, Vik Fearing <vik.fearing@dalibo.com
<mailto:vik.fearing@dalibo.com>> wrote:

On 06/26/2014 05:07 AM, Amit Kapila wrote:

I think it will make sense if we support RESET ALL as well similar
to Alter Database .. RESET ALL syntax. Do you see any reason
why we shouldn't support RESET ALL syntax for Alter System?

Yes, that makes sense. I've added that in the attached version 2 of the
patch.

I think the idea used in patch to implement RESET ALL is sane. In passing
by, I noticed that modified lines in .sgml have crossed 80 char
boundary which
is generally preferred to be maintained..

Refer below modification.

! values to the <filename>postgresql.auto.conf</filename> file.
Setting the parameter to
! <literal>DEFAULT</literal>, or using the <command>RESET</command>
variant, removes the configuration entry from

I did that on purpose so that it's easy for a reviewer/committer to see
what changed.

This third patch reformats the documentation in the way I expected it to
be committed.
--
Vik

On 06/27/2014 08:49 AM, Vik Fearing wrote:

This third patch reformats the documentation in the way I expected it to
be committed.

Amit,

I added this to the next commitfest with your name as reviewer.

https://commitfest.postgresql.org/action/patch_view?id=1495

Please update the status as you see fit.
--
Vik

--
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, Jun 27, 2014 at 12:27 PM, Vik Fearing <vik.fearing@dalibo.com>
wrote:

On 06/27/2014 08:49 AM, Vik Fearing wrote:

This third patch reformats the documentation in the way I expected it to
be committed.

Amit,

I added this to the next commitfest with your name as reviewer.

No issues, I will review it in next CF.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: Vik Fearing 2014-06-27 <53AD15F7.2060506@dalibo.com>

On 06/27/2014 08:49 AM, Vik Fearing wrote:

This third patch reformats the documentation in the way I expected it to
be committed.

Amit,

I added this to the next commitfest with your name as reviewer.

https://commitfest.postgresql.org/action/patch_view?id=1495

Please update the status as you see fit.

Isn't this 9.4 material? It seems like an oversight in the ALTER
SYSTEM implementation, and people will be expecting "ALTER SYTEM
(RE)SET guc" to just work like "(RE)SET guc".

It was mentioned by Alvaro there:
/messages/by-id/20130802173458.GO5669@eldon.alvh.no-ip.org

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

--
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, Jun 28, 2014 at 10:38 PM, Christoph Berg <cb@df7cb.de> wrote:

Re: Vik Fearing 2014-06-27 <53AD15F7.2060506@dalibo.com>

Amit,

I added this to the next commitfest with your name as reviewer.

https://commitfest.postgresql.org/action/patch_view?id=1495

Please update the status as you see fit.

Isn't this 9.4 material?

I think it's late for adding new feature/sub-feature/minor enhancement in
9.4, unless without that base feature won't work.

Isn't your use case addressed by alternative suggested by me upthread?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Thu, Jun 26, 2014 at 8:17 PM, Vik Fearing <vik.fearing@dalibo.com> wrote:

I didn't quite follow your ALTER TABLE example because I don't think
it's necessary,

I was asking to split the ALTER SYSTEM command like it's there
for ALTER TABLE (AlterTableStmt:
ALTER TABLE relation_expr alter_table_cmds).
It would have make adding further commands to ALTER SYSTEM bit
simpler and systemetic. However as there is no correctness issue here,
so lets leave it like you have currently done in patch.

I have verified the patch and found that it works well for
all scenario's. Few minor suggestions:

1.
! values to the <filename>postgresql.auto.conf</filename> file.
! Setting the parameter to <literal>DEFAULT</literal>, or using the
! <command>RESET</command> variant, removes the configuration entry from

It would be better if we can write a separate line for RESET ALL
as is written in case of both Alter Database and Alter Role in their
respective documentation.

2.
! %type <vsetstmt> generic_set set_rest set_rest_more generic_reset
reset_rest SetResetClause FunctionSetResetClause

Good to break it into 2 lines.

3. I think we can add some text on top of function
AlterSystemSetConfigFile() to explain functionality w.r.t reset all.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Wed, Jul 30, 2014 at 9:11 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

I have verified the patch and found that it works well for
all scenario's. Few minor suggestions:

1.
! values to the <filename>postgresql.auto.conf</filename> file.
! Setting the parameter to <literal>DEFAULT</literal>, or using the
! <command>RESET</command> variant, removes the configuration entry

from

It would be better if we can write a separate line for RESET ALL
as is written in case of both Alter Database and Alter Role in their
respective documentation.

2.
! %type <vsetstmt> generic_set set_rest set_rest_more generic_reset

reset_rest SetResetClause FunctionSetResetClause

Good to break it into 2 lines.

3. I think we can add some text on top of function
AlterSystemSetConfigFile() to explain functionality w.r.t reset all.

I have updated the patch to address the above points.

I will mark this patch as "Ready For Committer" as most of the
review comments were already addressed by Vik and remaining
I have addressed in attached patch.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Mon, Aug 25, 2014 at 1:34 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Wed, Jul 30, 2014 at 9:11 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

I have verified the patch and found that it works well for
all scenario's. Few minor suggestions:

1.
! values to the <filename>postgresql.auto.conf</filename> file.
! Setting the parameter to <literal>DEFAULT</literal>, or using the
! <command>RESET</command> variant, removes the configuration entry
from

It would be better if we can write a separate line for RESET ALL
as is written in case of both Alter Database and Alter Role in their
respective documentation.

2.
! %type <vsetstmt> generic_set set_rest set_rest_more generic_reset
reset_rest SetResetClause FunctionSetResetClause

Good to break it into 2 lines.

3. I think we can add some text on top of function
AlterSystemSetConfigFile() to explain functionality w.r.t reset all.

I have updated the patch to address the above points.

I will mark this patch as "Ready For Committer" as most of the
review comments were already addressed by Vik and remaining
I have addressed in attached patch.

The patch looks good to me. One minor comment is; probably you need to
update the tab-completion code.

Regards,

--
Fujii Masao

--
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, Aug 27, 2014 at 7:16 PM, Fujii Masao <masao.fujii@gmail.com> wrote:

The patch looks good to me. One minor comment is; probably you need to
update the tab-completion code.

Thanks for the review. I have updated the patch to support
tab-completion.
As this is a relatively minor change, I will mark it as
"Ready For Committer" rather than "Needs Review".

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao <masao.fujii@gmail.com> wrote:

The patch looks good to me. One minor comment is; probably you need to
update the tab-completion code.

Thanks for the review. I have updated the patch to support
tab-completion.
As this is a relatively minor change, I will mark it as
"Ready For Committer" rather than "Needs Review".

Thanks for updating the patch!

One more minor comment is; what about applying the following change
for the tab-completion for RESET ALL? This causes the tab-completion of
even ALTER SYSTEM SET to display "all" and that's strange. But
the tab-completion of "SET" has already had the same problem. So
I think that we can live with that. Attached is the patch that I added
the following change onto your patch. Barring any objection, I will commit
the patch.

@@ -545,7 +545,8 @@ static const SchemaQuery Query_for_list_of_matviews = {
 "SELECT name FROM "\
 " (SELECT pg_catalog.lower(name) AS name FROM pg_catalog.pg_settings "\
 "  WHERE context != 'internal') ss "\
-" WHERE substring(name,1,%d)='%s'"
+" WHERE substring(name,1,%d)='%s'"\
+" UNION ALL SELECT 'all' ss"

Regards,

--
Fujii Masao

On Mon, Sep 1, 2014 at 3:57 PM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila <amit.kapila16@gmail.com>

wrote:

On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao <masao.fujii@gmail.com>

wrote:

The patch looks good to me. One minor comment is; probably you need to
update the tab-completion code.

Thanks for the review. I have updated the patch to support
tab-completion.
As this is a relatively minor change, I will mark it as
"Ready For Committer" rather than "Needs Review".

Thanks for updating the patch!

One more minor comment is; what about applying the following change
for the tab-completion for RESET ALL? This causes the tab-completion of
even ALTER SYSTEM SET to display "all" and that's strange. But
the tab-completion of "SET" has already had the same problem. So
I think that we can live with that.

Right and I have checked that behaviour is same for other similar
statements like Alter Database <database_name> SET <config_var>
or Alter User <user_name> SET <config_var>. So, the change
made by you is on similar lines.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com