Re: [HACKERS] Pg default's verbosity?

2012-08-30 Thread Bruce Momjian
On Sun, Jun 17, 2012 at 12:00:20AM -0400, nik9...@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 be690e291d59e8d0c9f4df59abe09f1ff6cc0da9
Author: Robert Haas rh...@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 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 coe...@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 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 - coe...@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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

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


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


Re: [HACKERS] Pg default's verbosity?

2012-07-04 Thread Robert Haas
On Mon, Jul 2, 2012 at 3:47 PM, Robert Haas robertmh...@gmail.com wrote:
 On Fri, Jun 29, 2012 at 3:07 AM, Bruce Momjian br...@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

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


Re: [HACKERS] Pg default's verbosity?

2012-06-29 Thread Bruce Momjian
On Tue, Jun 19, 2012 at 02:15:43AM -0400, Tom Lane wrote:
 Robert Haas robertmh...@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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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


Re: [HACKERS] Pg default's verbosity?

2012-06-20 Thread Peter Eisentraut
On tis, 2012-06-19 at 02:15 -0400, Tom Lane wrote:
 Robert Haas robertmh...@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


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


Re: [HACKERS] Pg default's verbosity?

2012-06-20 Thread Robert Haas
On Wed, Jun 20, 2012 at 11:25 AM, Peter Eisentraut pete...@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(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

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


Re: [HACKERS] Pg default's verbosity?

2012-06-19 Thread Tom Lane
Robert Haas robertmh...@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

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


Re: [HACKERS] Pg default's verbosity?

2012-06-19 Thread Martijn van Oosterhout
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   klep...@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


signature.asc
Description: Digital signature


Re: [HACKERS] Pg default's verbosity?

2012-06-19 Thread Fabien COELHO



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.

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


Re: [HACKERS] Pg default's verbosity?

2012-06-19 Thread Robert Haas
On Tue, Jun 19, 2012 at 2:15 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@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

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


Re: [HACKERS] Pg default's verbosity?

2012-06-18 Thread Robert Haas
On Sat, Jun 16, 2012 at 2:56 PM, Fabien COELHO coe...@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

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


Re: [HACKERS] Pg default's verbosity?

2012-06-17 Thread Jeff Janes
On Sat, Jun 16, 2012 at 9:00 PM,  nik9...@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

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


[HACKERS] Pg default's verbosity?

2012-06-16 Thread Fabien COELHO


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 - coe...@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


Re: [HACKERS] Pg default's verbosity?

2012-06-16 Thread Tom Lane
Fabien COELHO coe...@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

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


Re: [HACKERS] Pg default's verbosity?

2012-06-16 Thread Fabien COELHO


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.

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


Re: [HACKERS] Pg default's verbosity?

2012-06-16 Thread nik9000
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 coe...@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 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 - coe...@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