Thread: BUG #11350: ALTER SYSTEM is not DDL?
The following bug has been logged on the website: Bug reference: 11350 Logged by: Tomonari Katsumata Email address: katsumata.tomonari@po.ntts.co.jp PostgreSQL version: 9.4beta2 Operating system: RHEL6.4 x86_64 Description: Hi, I found an odd thing about logging for alter system. When I set 'log_statement=ddl' and execute ALTER SYSTEM, any log messages are not outputted. ----------------------------------------------- [log_statement = ddl] postgres=# alter system set autovacuum to off; ALTER SYSTEM ----------------------------------------------- Whereas, when I set 'log_statemnt = all' then log messege is outputted. ----------------------------------------------- [log_statement = all] postgres=# alter system set autovacuum to off; LOG: statement: alter system set autovacuum to off; ALTER SYSTEM ----------------------------------------------- The document says about log_statement: >ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements. So I think log message should be outputted when executing ALTER SYSTEM with 'log_statement = ddl'. Am I missing something?
On Thu, Sep 4, 2014 at 10:43 AM, <katsumata.tomonari@po.ntts.co.jp> wrote: > > The following bug has been logged on the website: > > Bug reference: 11350 > Logged by: Tomonari Katsumata > Email address: katsumata.tomonari@po.ntts.co.jp > PostgreSQL version: 9.4beta2 > Operating system: RHEL6.4 x86_64 > Description: > > The document says about log_statement: > >ddl logs all data definition statements, such as CREATE, ALTER, and DROP > statements. > > So I think log message should be outputted when executing ALTER SYSTEM with > 'log_statement = ddl'. > > Am I missing something? Currently it is considered similar to SET statement as it performs similar operation except that the value for configuration gets set in configuration file. Do you see any reason for it to be considered differently except that the command has ALTER keyword? Note- Initially we have thought it to be a variant of SET (SET PERSISTENT), however later it turns out that it will be better to use ALTER SYSTEM for this command. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
* Amit Kapila (amit.kapila16@gmail.com) wrote:
> On Thu, Sep 4, 2014 at 10:43 AM, <katsumata.tomonari@po.ntts.co.jp> wrote:
> > So I think log message should be outputted when executing ALTER SYSTEM
> with
> > 'log_statement =3D ddl'.
> >
> > Am I missing something?
>=20
> Currently it is considered similar to SET statement as it performs
> similar operation except that the value for configuration gets set
> in configuration file. Do you see any reason for it to be considered
> differently except that the command has ALTER keyword?
Uh, it's nothing like SET as SET only affects the current session.
It's much, much closer to ALTER ROLE, which correctly logs at the DDL
level. This should be fixed, and before 9.4 goes out...
Thanks,
Stephen
On 2014-09-04 15:14:56 -0400, Stephen Frost wrote: > * Amit Kapila (amit.kapila16@gmail.com) wrote: > > On Thu, Sep 4, 2014 at 10:43 AM, <katsumata.tomonari@po.ntts.co.jp> wrote: > > > So I think log message should be outputted when executing ALTER SYSTEM > > with > > > 'log_statement = ddl'. > > > > > > Am I missing something? > > > > Currently it is considered similar to SET statement as it performs > > similar operation except that the value for configuration gets set > > in configuration file. Do you see any reason for it to be considered > > differently except that the command has ALTER keyword? > > Uh, it's nothing like SET as SET only affects the current session. > > It's much, much closer to ALTER ROLE, which correctly logs at the DDL > level. This should be fixed, and before 9.4 goes out... +1. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Hi, Thank you for checking this. Originally this feature might have been close to SET, but currently has an 'ALTER' keyword. Many users don't know this and would be confusing with reading the manual. I made a quick patch for fixing this. Could you check it? regards, ------------- Tomonari Katsumata (2014/09/05 5:12), Andres Freund wrote: > On 2014-09-04 15:14:56 -0400, Stephen Frost wrote: >> * Amit Kapila (amit.kapila16@gmail.com) wrote: >>> On Thu, Sep 4, 2014 at 10:43 AM, <katsumata.tomonari@po.ntts.co.jp> wrote: >>>> So I think log message should be outputted when executing ALTER SYSTEM >>> with >>>> 'log_statement = ddl'. >>>> >>>> Am I missing something? >>> >>> Currently it is considered similar to SET statement as it performs >>> similar operation except that the value for configuration gets set >>> in configuration file. Do you see any reason for it to be considered >>> differently except that the command has ALTER keyword? >> >> Uh, it's nothing like SET as SET only affects the current session. >> >> It's much, much closer to ALTER ROLE, which correctly logs at the DDL >> level. This should be fixed, and before 9.4 goes out... > > +1. > > Greetings, > > Andres Freund >
Attachment
On Fri, Sep 5, 2014 at 12:44 AM, Stephen Frost <sfrost@snowman.net> wrote: > > * Amit Kapila (amit.kapila16@gmail.com) wrote: > > On Thu, Sep 4, 2014 at 10:43 AM, <katsumata.tomonari@po.ntts.co.jp> wrote: > > > So I think log message should be outputted when executing ALTER SYSTEM > > with > > > 'log_statement = ddl'. > > > > > > Am I missing something? > > > > Currently it is considered similar to SET statement as it performs > > similar operation except that the value for configuration gets set > > in configuration file. Do you see any reason for it to be considered > > differently except that the command has ALTER keyword? > > Uh, it's nothing like SET as SET only affects the current session. and Alter System affects at system level, both of these can't be considered as DDL, may be a separate category. If we consider below types of statements, SET will fall into 4th category and Alter System will fall into 5th category, but none should be considered as DDL. 1. DDL 2. DML 3. Transaction Control 4. Session Control 5. System Control > It's much, much closer to ALTER ROLE, which correctly logs at the DDL > level. This should be fixed, and before 9.4 goes out... I think fix should be in explanation of log_statement, something like below: ddl logs all data definition statements, such as CREATE, ALTER (except ALTER SYSTEM), and DROP statements With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Amit,
* Amit Kapila (amit.kapila16@gmail.com) wrote:
> On Fri, Sep 5, 2014 at 12:44 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > > Currently it is considered similar to SET statement as it performs
> > > similar operation except that the value for configuration gets set
> > > in configuration file. Do you see any reason for it to be considered
> > > differently except that the command has ALTER keyword?
> >
> > Uh, it's nothing like SET as SET only affects the current session.
>=20
> and Alter System affects at system level, both of these can't
> be considered as DDL, may be a separate category.
Perhaps something went wrong with your quoting, but the above doesn't
make sense to me.
> If we consider below types of statements, SET will fall into
> 4th category and Alter System will fall into 5th category, but
> none should be considered as DDL.
Based on the existing logging structure that we have and the existing
precedent, DDL is the right level for these kinds of statements.
> > It's much, much closer to ALTER ROLE, which correctly logs at the DDL
> > level. This should be fixed, and before 9.4 goes out...
>=20
> I think fix should be in explanation of log_statement, something like bel=
ow:
> ddl logs all data definition statements, such as CREATE, ALTER
> (except ALTER SYSTEM), and DROP statements
No. ALTER SYSTEM should be logged at the 'ddl' level and not the 'all'.
I'm fine with improving the documentation, but saying we shouldn't log
ALTER SYSTEM changes at 'ddl' is wrong.
Thanks,
Stephen
All,
* Stephen Frost (sfrost@snowman.net) wrote:
> No. ALTER SYSTEM should be logged at the 'ddl' level and not the 'all'.
> I'm fine with improving the documentation, but saying we shouldn't log
> ALTER SYSTEM changes at 'ddl' is wrong.
Are there any further objections to this..? Does anyone else want to
weigh in on it? If there's nothing further on it, then I'll go ahead
and make the change.
Thanks,
Stephen
On 09/05/2014 08:32 PM, Amit Kapila wrote: > > and Alter System affects at system level, both of these can't > be considered as DDL, may be a separate category. Like CREATE USER / CREATE ROLE / CREATE DATABASE ? All those are logged as DDL. Sure, you could separate them, but what real world benefit would that offer? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Sep 19, 2014 at 9:49 AM, Craig Ringer <craig@2ndquadrant.com> wrote: > > On 09/05/2014 08:32 PM, Amit Kapila wrote: > > > > and Alter System affects at system level, both of these can't > > be considered as DDL, may be a separate category. > > Like CREATE USER / CREATE ROLE / CREATE DATABASE ? > > All those are logged as DDL. They are different from ALTER SYSTEM in terms that they create/modify the object (here object can be any database or cluster object) property. Can you tell me that if we want to make ALTER SYSTEM as DDL, then why SET or CHECKPOINT commands can't be DDL? > Sure, you could separate them, but what real world benefit would that offer? At some level consistency with other databases (Oracle doesn't consider ALTER SYSTEM as DDL, it defines statements similar to what I had mentioned upthread). Another point is tomorrow if some one says that I want a command like Alter System Kill Session or some thing else on similar lines, then telling that it is DDL might not be the right definition. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
On 2014-09-19 12:50:24 +0530, Amit Kapila wrote: > On Fri, Sep 19, 2014 at 9:49 AM, Craig Ringer <craig@2ndquadrant.com> wrote: > > > > On 09/05/2014 08:32 PM, Amit Kapila wrote: > > > > > > and Alter System affects at system level, both of these can't > > > be considered as DDL, may be a separate category. > > > > Like CREATE USER / CREATE ROLE / CREATE DATABASE ? > > > > All those are logged as DDL. > > They are different from ALTER SYSTEM in terms that they create/modify > the object (here object can be any database or cluster object) property. > > Can you tell me that if we want to make ALTER SYSTEM as DDL, then > why SET or CHECKPOINT commands can't be DDL? Really? Because neither has persistent effects? I'd be perfectly fine if we started logging CHECKPOINT; as DDL - SET on the hand doesn't make much sense. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Sep 19, 2014 at 1:05 PM, Andres Freund <andres@2ndquadrant.com> wrote: > > On 2014-09-19 12:50:24 +0530, Amit Kapila wrote: > > On Fri, Sep 19, 2014 at 9:49 AM, Craig Ringer <craig@2ndquadrant.com> wrote: > > > > > > On 09/05/2014 08:32 PM, Amit Kapila wrote: > > > > > > > > and Alter System affects at system level, both of these can't > > > > be considered as DDL, may be a separate category. > > > > > > Like CREATE USER / CREATE ROLE / CREATE DATABASE ? > > > > > > All those are logged as DDL. > > > > They are different from ALTER SYSTEM in terms that they create/modify > > the object (here object can be any database or cluster object) property. > > > > Can you tell me that if we want to make ALTER SYSTEM as DDL, then > > why SET or CHECKPOINT commands can't be DDL? > > Really? Because neither has persistent effects? I am not sure if persistent effects should be considered while defining type of statement. Statements that define database structure or schema in some way should be considered as DDL statements. I am not able to convince myself that ALTER SYSTEM should be a DDL command, however if you and Stephen feels strongly about it then lets do it that way. I think there is already a patch in this thread which defines it as DDL (though I haven't checked it yet). Do you expect anything more? With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
All,
* Stephen Frost (sfrost@snowman.net) wrote:
> Are there any further objections to this..? Does anyone else want to
> weigh in on it? If there's nothing further on it, then I'll go ahead
> and make the change.
This has been done, and back-patched to 9.4, thanks for all the
discussion!
Thanks again,
Stephen