Обсуждение: Why is LOG level below WARNING and ERROR for log_min_error_statement?
Hello, I'm trying to debug a problem that is occurring rather rarely, which causes multiple clients connected to the same postgresql 9.4 server to crash at the same time. These clients send *lots* of SQL statements. I want to turn on statement logging, but I am baffled by the fact that the LOG level is *below* WARNING and ERROR levels, for the `log_min_error_statement` configuration option. This results in my log being flooded with useless messages about statements that have succeeded. Why is it designed like that? How do real DBAs deal with that (I'm not a real DBA, just a Python/C# developer)? Best Regards, Kal
Kal Sze <swordangel@gmail.com> writes:
> I'm trying to debug a problem that is occurring rather rarely, which
> causes multiple clients connected to the same postgresql 9.4 server to
> crash at the same time.
> These clients send *lots* of SQL statements. I want to turn on
> statement logging, but I am baffled by the fact that the LOG level is
> *below* WARNING and ERROR levels, for the `log_min_error_statement`
> configuration option. This results in my log being flooded with
> useless messages about statements that have succeeded.
> Why is it designed like that?
LOG level is meant for non-client-facing log messages, which should get
sent to the postmaster log even if log_min_messages has been set high
enough to suppress logging of everyday SQL-statement errors. On the other
hand, they're not meant for client consumption so they don't get sent to
the client at the default client_min_messages setting.
You didn't say exactly where the problematic messages are coming from,
but it sounds a bit like somebody on the client side misunderstood what
they should use LOG for.
regards, tom lane
On 5 June 2015 at 13:27, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Kal Sze <swordangel@gmail.com> writes: >> I'm trying to debug a problem that is occurring rather rarely, which >> causes multiple clients connected to the same postgresql 9.4 server to >> crash at the same time. > >> These clients send *lots* of SQL statements. I want to turn on >> statement logging, but I am baffled by the fact that the LOG level is >> *below* WARNING and ERROR levels, for the `log_min_error_statement` >> configuration option. This results in my log being flooded with >> useless messages about statements that have succeeded. > >> Why is it designed like that? > > LOG level is meant for non-client-facing log messages, which should get > sent to the postmaster log even if log_min_messages has been set high > enough to suppress logging of everyday SQL-statement errors. On the other > hand, they're not meant for client consumption so they don't get sent to > the client at the default client_min_messages setting. > > You didn't say exactly where the problematic messages are coming from, > but it sounds a bit like somebody on the client side misunderstood what > they should use LOG for. > > regards, tom lane I want to turn on statement logging on the server side, not sending the messages to the clients. Right now, I don't even know where the problematic messages come from, which is why I want to turn on statement logging on the server side. All I know is that both clients crash at the same time while performing some COPYs in a transaction. And I suspect it's a problem with this particular server instance because I have another, physically separate setup of server and clients, and that setup is not crashing.
On 5 June 2015 at 13:58, Kal Sze <swordangel@gmail.com> wrote: > On 5 June 2015 at 13:27, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Kal Sze <swordangel@gmail.com> writes: >>> I'm trying to debug a problem that is occurring rather rarely, which >>> causes multiple clients connected to the same postgresql 9.4 server to >>> crash at the same time. >> >>> These clients send *lots* of SQL statements. I want to turn on >>> statement logging, but I am baffled by the fact that the LOG level is >>> *below* WARNING and ERROR levels, for the `log_min_error_statement` >>> configuration option. This results in my log being flooded with >>> useless messages about statements that have succeeded. >> >>> Why is it designed like that? >> >> LOG level is meant for non-client-facing log messages, which should get >> sent to the postmaster log even if log_min_messages has been set high >> enough to suppress logging of everyday SQL-statement errors. On the other >> hand, they're not meant for client consumption so they don't get sent to >> the client at the default client_min_messages setting. >> >> You didn't say exactly where the problematic messages are coming from, >> but it sounds a bit like somebody on the client side misunderstood what >> they should use LOG for. >> >> regards, tom lane > > I want to turn on statement logging on the server side, not sending > the messages to the clients. > > Right now, I don't even know where the problematic messages come from, > which is why I want to turn on statement logging on the server side. > All I know is that both clients crash at the same time while > performing some COPYs in a transaction. And I suspect it's a problem > with this particular server instance because I have another, > physically separate setup of server and clients, and that setup is not > crashing. Put another way, what kinds of messages are included in the ERROR level? When the wrong data type is supplied for a table column? When a value in an COPY statement would violate a unique constraint? If so, why should LOG be even below ERROR? If not, then what kinds of messages fall into the ERROR level? I know there are also the FATAL and PANIC levels, but FATAL is supposed to be for really bad things that cause a *session* to abort, so I don't expect stuff like unique constraint violation to fall into that level, unless I'm misunderstanding what is meant by a *session*.
What is client_min_messages setting ? is it on default? Regards, Amul -- View this message in context: http://postgresql.nabble.com/Why-is-LOG-level-below-WARNING-and-ERROR-for-log-min-error-statement-tp5852572p5852580.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
On 5 June 2015 at 14:29, amulsul <sul_amul@yahoo.co.in> wrote: > What is client_min_messages setting ? is it on default? > > Regards, > Amul > > > > -- > View this message in context: http://postgresql.nabble.com/Why-is-LOG-level-below-WARNING-and-ERROR-for-log-min-error-statement-tp5852572p5852580.html > Sent from the PostgreSQL - novice mailing list archive at Nabble.com. > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice client_min_messages is on default - i.e. notice.
>Put another way, what kinds of messages are included in the ERROR >level? When the wrong data type is supplied for a table column? When a >value in an COPY statement would violate a unique constraint? If so, >why should LOG be even below ERROR? Check log_min_error_statement(enum) description given in postgres document[1] as : Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. *The defaultis ERROR, which means statements causing errors, log messages, fatal errors, or panics will be logged*. Regards, Amul 1. postgres document : http://www.postgresql.org/docs/9.4/static/runtime-config-logging.html.