Thread: Pg default's verbosity?
Hello pgdev, (Second attempt) I've conducted a statistical study about PostgreSQL use in OSS. One of the result is that quite a few projects have errors in their SQL setup scripts which lead to some statements to be ignored, typically somme ADD CONSTRAINTS which do not change the database schema from a functional point of view, or syntactic errors (typically a mysql syntax...) that result in missing tables, but which are not found if the application is not fully tested. I think that there are two reasons why these errors are not caught by application developers: (1) the default verbosity is set to "notice", which is much to high. The users just get used to seeing a lot of messages on loading an sql script, and to ignore them, so that errors are just hidden in the flow of notices. I think that a better default setting would be "warnings", that is messages that require some attention from the developer. (2) the default behavior of psql on errors is to keep going. Developers of SQL script that are expected to work shoud be advised to: - encourage application devs to set ON_ERROR_STOP and/or use aglobal transaction in their script. - provide a simple/short option to do that from the command line basically thatcould be an enhanced "-1", NOT restricted to "-f" but that would work on standard input as well. sh> psql -1 -f setup.sql # -1 does work here sh> psql -1 < setup.sql # -1 does not apply to stdin stuff... So I would suggest the following todos: 1 - change the default verbosity to "warning". 2 - change -1 to work on stdin as well instead of being ignored, or provide another option that would do that. -- Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> [ errors in SQL scripts fed to psql are easily missed ]
> So I would suggest the following todos:
> 1 - change the default verbosity to "warning".
The argument for defaulting to NOTICE is the same as it's always been:
that those messages are really intended for novices, and a pretty good
definition of a novice is somebody who doesn't know how to (or that he
should) change the verbosity setting. So if we don't show notices by
default, they will be unavailable to exactly the people who need them.
Your proposal does not overcome this argument.
Besides, I'm not convinced that changing client_min_messages in
isolation would do much for the problem, because psql is still pretty
chatty by itself; you really need -q to have any hope that important
messages didn't scroll off your screen. Perhaps it would be sensible to
have the -q switch also execute "set client_min_messages = warning", and
recommend that people use that when running allegedly-debugged scripts?
> 2 - change -1 to work on stdin as well instead of being ignored,
> or provide another option that would do that.
Yeah, if that doesn't work already, it would be sane to make it do so,
at least for non-tty stdin. It seems like a fairly bad idea for
interactive stdin, though.
regards, tom lane
Hello Tom, thanks for your answer. >> So I would suggest the following todos: >> 1 - change the default verbosity to "warning". > > The argument for defaulting to NOTICE is the same as it's always been: > that those messages are really intended for novices, and a pretty good > definition of a novice is somebody who doesn't know how to (or that he > should) change the verbosity setting. So if we don't show notices by > default, they will be unavailable to exactly the people who need them. > Your proposal does not overcome this argument. I'm sceptical about what a real "novice" is expected to do about the incredible flow of useless information displayed when loading a significant script. For a start, s?he should be an incredibly fast reader:-) For a non-novice it just hides what is important and should be seen. However maybe it make senses in interactive mode, as you suggest, so possibly this should be the real trigger to change the level of messages. > Besides, I'm not convinced that changing client_min_messages in > isolation would do much for the problem, I agree with you, but it is a necessary step somewhere... > because psql is still pretty chatty by itself; you really need -q to > have any hope that important messages didn't scroll off your screen. Hmmm, yes and no, in my opinion. "CREATE XXX" is a very short output, quite distinct from the output of a warning/error, which can be seen when messages are scrolled, even if the message cannot be read on the fly. I would know that something is not right. > Perhaps it would be sensible to have the -q switch also execute "set > client_min_messages = warning", and recommend that people use that when > running allegedly-debugged scripts? That could be useful. However I'm not sure that I would select -q when loading a big script, I'm happy to know that things are going on and I would like to know if the script is stuck somewhere. >> 2 - change -1 to work on stdin as well instead of being ignored, >> or provide another option that would do that. > > Yeah, if that doesn't work already, I did checked that it does not work with 9.1.3. > it would be sane to make it do so, at least for non-tty stdin. It seems > like a fairly bad idea for interactive stdin, though. I agree that distinguishing interactive & non interactive stdin is reasonable. So maybe the suggestion would be to distinguish 2 default settings - client_min_tty_messages = notice; # or some other name...- client_min_messages = warning; # or some other name... Moreover: - "-1" should work on stdin *when not interactive* - -1 should be clearly advised when loading scripts... not sure whereit should be in the documentation... - I'm not sure about "-q" for this purpose, mostly because I would not use itby default -- Fabien.
I've always used -1-f - < file.sql. It is confusing that -1 doesn't warn you when it wont work though. Sent from my iPhone On Jun 16, 2012, at 3:42 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote: > > Hello pgdev, > > (Second attempt) > > I've conducted a statistical study about PostgreSQL use in OSS. One of the result is that quite a few projects have errorsin their SQL setup scripts which lead to some statements to be ignored, typically somme ADD CONSTRAINTS which do notchange the database schema from a functional point of view, or syntactic errors (typically a mysql syntax...) that > result in missing tables, but which are not found if the application is not fully tested. > > I think that there are two reasons why these errors are not caught by application developers: > > (1) the default verbosity is set to "notice", which is much to high. The users just get used to seeing a lot of messageson loading an sql script, and to ignore them, so that errors are just hidden in the flow of notices. I think thata better default setting would be "warnings", that is messages that require some attention from the developer. > > (2) the default behavior of psql on errors is to keep going. Developers of SQL script that are expected to work shoud beadvised to: > - encourage application devs to set ON_ERROR_STOP and/or use a global > transaction in their script. > - provide a simple/short option to do that from the command line > basically that could be an enhanced "-1", NOT restricted > to "-f" but that would work on standard input as well. > > sh> psql -1 -f setup.sql # -1 does work here > sh> psql -1 < setup.sql # -1 does not apply to stdin stuff... > > > So I would suggest the following todos: > > 1 - change the default verbosity to "warning". > > 2 - change -1 to work on stdin as well instead of being ignored, > or provide another option that would do that. > > -- > Fabien Coelho - coelho@cri.ensmp.fr > > -- > 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 16, 2012 at 9:00 PM, <nik9000@gmail.com> wrote: > I've always used -1-f - < file.sql. It is confusing that -1 doesn't warn you when it wont work though. Yeah, I just got bitten by that one. Definitely violates the POLA. Cheers, Jeff
On Sat, Jun 16, 2012 at 2:56 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote: >> The argument for defaulting to NOTICE is the same as it's always been: >> that those messages are really intended for novices, and a pretty good >> definition of a novice is somebody who doesn't know how to (or that he >> should) change the verbosity setting. So if we don't show notices by >> default, they will be unavailable to exactly the people who need them. >> Your proposal does not overcome this argument. > > I'm sceptical about what a real "novice" is expected to do about the > incredible flow of useless information displayed when loading a significant > script. For a start, s?he should be an incredibly fast reader:-) > > For a non-novice it just hides what is important and should be seen. There might be something to the idea of demoting a few of the things we've traditionally had as NOTICEs, though. IME, the following two messages account for a huge percentage of the chatter: NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for serial column "foo.a" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" I'm not going to claim that nobody in the history of the world has ever benefited from those notices ... but I would be willing to bet that a large majority of the people, in a large majority of the cases, do not care. And getting rid of them would surely make warnings and notices that might actually be of interest to the user a lot more visible. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> There might be something to the idea of demoting a few of the things
> we've traditionally had as NOTICEs, though. IME, the following two
> messages account for a huge percentage of the chatter:
> NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for
> serial column "foo.a"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "foo_pkey" for table "foo"
Personally, I'd have no problem with flat-out dropping (not demoting)
both of those two specific messages. I seem to recall that Bruce has
lobbied for them heavily in the past, though.
regards, tom lane
On Mon, Jun 18, 2012 at 09:30:14PM -0400, Robert Haas wrote: > There might be something to the idea of demoting a few of the things > we've traditionally had as NOTICEs, though. IME, the following two > messages account for a huge percentage of the chatter: > > NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for > serial column "foo.a" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "foo_pkey" for table "foo" +1 Absolutely. And if you also suppress the output of the setval's produced by pg_dump that would make a successful restore of a dump produce barely any output at all with -q. That would make errors significantly more visible. Not sure how to go about that though. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
> There might be something to the idea of demoting a few of the things > we've traditionally had as NOTICEs, though. IME, the following two > messages account for a huge percentage of the chatter: > > NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for > serial column "foo.a" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "foo_pkey" for table "foo" You can also add: NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_name_key" for table "foo" I agree that these amount for most of the noise. As create table does create other objects, I could understand that someone wants to hear about that. Maybe move them as "info" ? Otherwise, changing the default message level seems reasonable to me. What we really case when loading an SQL script is WARNING & ERROR, so that should be what is activated. > I'm not going to claim that nobody in the history of the world has > ever benefited from those notices ... but I would be willing to bet > that a large majority of the people, in a large majority of the cases, > do not care. And getting rid of them would surely make warnings and > notices that might actually be of interest to the user a lot more > visible. -- Fabien.
On Tue, Jun 19, 2012 at 2:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> There might be something to the idea of demoting a few of the things >> we've traditionally had as NOTICEs, though. IME, the following two >> messages account for a huge percentage of the chatter: > >> NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for >> serial column "foo.a" >> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index >> "foo_pkey" for table "foo" > > Personally, I'd have no problem with flat-out dropping (not demoting) > both of those two specific messages. I seem to recall that Bruce has > lobbied for them heavily in the past, though. My vote would be to make 'em DEBUG1, and similarly with the UNIQUE message that Fabian mentions downthread. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On tis, 2012-06-19 at 02:15 -0400, Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > There might be something to the idea of demoting a few of the things > > we've traditionally had as NOTICEs, though. IME, the following two > > messages account for a huge percentage of the chatter: > > > NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for > > serial column "foo.a" > > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > > "foo_pkey" for table "foo" > > Personally, I'd have no problem with flat-out dropping (not demoting) > both of those two specific messages. I seem to recall that Bruce has > lobbied for them heavily in the past, though. I don't like these messages any more than the next guy, but why drop only those, and not any of the other NOTICE-level messages? The meaning of NOTICE is pretty much, if this is the first time you're using PostgreSQL, let me tell you a little bit about how we're doing things here. If you've run your SQL script more than 3 times, you won't need them anymore. So set your client_min_messages to WARNING then. That should be pretty much standard for running SQL scripts, in addition to all the other stuff listed here: http://petereisentraut.blogspot.fi/2010/03/running-sql-scripts-with-psql.html
On Wed, Jun 20, 2012 at 11:25 AM, Peter Eisentraut <peter_e@gmx.net> wrote: > I don't like these messages any more than the next guy, but why drop > only those, and not any of the other NOTICE-level messages? The meaning > of NOTICE is pretty much, if this is the first time you're using > PostgreSQL, let me tell you a little bit about how we're doing things > here. If you've run your SQL script more than 3 times, you won't need > them anymore. So set your client_min_messages to WARNING then. That > should be pretty much standard for running SQL scripts, in addition to > all the other stuff listed here: > http://petereisentraut.blogspot.fi/2010/03/running-sql-scripts-with-psql.html Well, let's look at some of the other places where we use NOTICE: ereport(NOTICE, (errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION), errmsg("thereis no transaction in progress"))); ereport(NOTICE, (errmsg("pg_stop_backup cleanup done, waiting for required WAL segments to be archived"))); ereport(msglevel, /* translator: %d always has a value larger than 1 */ (errmsg_plural("drop cascadesto %d other object", "drop cascades to %d other objects", numReportedClient + numNotReportedClient, numReportedClient + numNotReportedClient), errdetail("%s", clientdetail.data), errdetail_log("%s", logdetail.data))); ereport(NOTICE, (errmsg("merging constraint \"%s\" with inherited definition", ccname))); ereport(NOTICE, (errmsg("database \"%s\" does not exist, skipping", dbname))); ereport(NOTICE, (errmsg("version \"%s\" of extension \"%s\" is already installed", versionName,stmt->extname))); ereport(NOTICE, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("argument type %s is only a shell", TypeNameToString(t)))); It seems to me that at least some of those have quite a bit more value than the messages under discussion, and they're not all just tips for novices. Maybe you'd want to suppress those when running a script and maybe you wouldn't, but I think that most people don't want to see the messages under discussion even in interactive mode, unless perhaps they are debugging some unexpected behavior. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Jun 19, 2012 at 02:15:43AM -0400, Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > There might be something to the idea of demoting a few of the things > > we've traditionally had as NOTICEs, though. IME, the following two > > messages account for a huge percentage of the chatter: > > > NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for > > serial column "foo.a" > > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > > "foo_pkey" for table "foo" > > Personally, I'd have no problem with flat-out dropping (not demoting) > both of those two specific messages. I seem to recall that Bruce has > lobbied for them heavily in the past, though. I would like to see them gone or reduced as well. I think I wanted them when we changed the fact that SERIAL doesn't create unique indexes, but that was long ago --- I think everyone knows what happens now. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Fri, Jun 29, 2012 at 3:07 AM, Bruce Momjian <bruce@momjian.us> wrote: >> Personally, I'd have no problem with flat-out dropping (not demoting) >> both of those two specific messages. I seem to recall that Bruce has >> lobbied for them heavily in the past, though. > > I would like to see them gone or reduced as well. I think I wanted them > when we changed the fact that SERIAL doesn't create unique indexes, but > that was long ago --- I think everyone knows what happens now. Patch attached. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Attachment
On Mon, Jul 2, 2012 at 3:47 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Fri, Jun 29, 2012 at 3:07 AM, Bruce Momjian <bruce@momjian.us> wrote: >>> Personally, I'd have no problem with flat-out dropping (not demoting) >>> both of those two specific messages. I seem to recall that Bruce has >>> lobbied for them heavily in the past, though. >> >> I would like to see them gone or reduced as well. I think I wanted them >> when we changed the fact that SERIAL doesn't create unique indexes, but >> that was long ago --- I think everyone knows what happens now. > > Patch attached. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sun, Jun 17, 2012 at 12:00:20AM -0400, nik9000@gmail.com wrote: > I've always used -1-f - < file.sql. It is confusing that -1 doesn't warn you when it wont work though. This will be fixed in 9.3 with this commit: commit be690e291d59e8d0c9f4df59abe09f1ff6cc0da9Author: Robert Haas <rhaas@postgresql.org>Date: Thu Aug 9 09:59:45 2012-0400 Make psql -1 < file behave as expected. Previously, the -1 option was silently ignored. Also, emit anerror if -1 is used in a context where it won't be respected, to avoid user confusion. Original patch by Fabien COELHO,but this version is quite different from the original submission. --------------------------------------------------------------------------- > > Sent from my iPhone > > On Jun 16, 2012, at 3:42 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote: > > > > > Hello pgdev, > > > > (Second attempt) > > > > I've conducted a statistical study about PostgreSQL use in OSS. One of the result is that quite a few projects have errorsin their SQL setup scripts which lead to some statements to be ignored, typically somme ADD CONSTRAINTS which do notchange the database schema from a functional point of view, or syntactic errors (typically a mysql syntax...) that > > result in missing tables, but which are not found if the application is not fully tested. > > > > I think that there are two reasons why these errors are not caught by application developers: > > > > (1) the default verbosity is set to "notice", which is much to high. The users just get used to seeing a lot of messageson loading an sql script, and to ignore them, so that errors are just hidden in the flow of notices. I think thata better default setting would be "warnings", that is messages that require some attention from the developer. > > > > (2) the default behavior of psql on errors is to keep going. Developers of SQL script that are expected to work shoudbe advised to: > > - encourage application devs to set ON_ERROR_STOP and/or use a global > > transaction in their script. > > - provide a simple/short option to do that from the command line > > basically that could be an enhanced "-1", NOT restricted > > to "-f" but that would work on standard input as well. > > > > sh> psql -1 -f setup.sql # -1 does work here > > sh> psql -1 < setup.sql # -1 does not apply to stdin stuff... > > > > > > So I would suggest the following todos: > > > > 1 - change the default verbosity to "warning". > > > > 2 - change -1 to work on stdin as well instead of being ignored, > > or provide another option that would do that. > > > > -- > > Fabien Coelho - coelho@cri.ensmp.fr > > > > -- > > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +