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 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(at)cri(dot)ensmp(dot)fr

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)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 where it should be in the documentation...
- I'm not sure about "-q" for this purpose, mostly because
I would not use it by 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(at)cri(dot)ensmp(dot)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 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 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(at)cri(dot)ensmp(dot)fr
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Jun 16, 2012 at 9:00 PM, <nik9000(at)gmail(dot)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(at)cri(dot)ensmp(dot)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(at)gmail(dot)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(at)svana(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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(at)gmail(dot)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(at)gmx(dot)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("there is 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 cascades to %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(at)gmail(dot)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(at)momjian(dot)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(at)momjian(dot)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

On Mon, Jul 2, 2012 at 3:47 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Jun 29, 2012 at 3:07 AM, Bruce Momjian <bruce(at)momjian(dot)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(at)gmail(dot)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 be690e291d59e8d0c9f4df59abe09f1ff6cc0da9
Author: Robert Haas <rhaas(at)postgresql(dot)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 an error 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(at)cri(dot)ensmp(dot)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 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 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(at)cri(dot)ensmp(dot)fr
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +