Обсуждение: Inserts hang in DB and error messages in log

The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you.




************************************************************************************
This footnote confirms that this email message has been scanned by
PineApp Mail-SeCure for the presence of malicious code, vandals & computer viruses.
************************************************************************************

On Wednesday 20 May 2009 11:36:38 pm Moshe Ben-Shoham wrote:
> Hi,
>
>
>
> We're working with version 8.3.5-1.
>
>
>
> Lately we started seeing insert statements hang in the DB. The
> statements come from two different clients.
>
>
>
> When it happens, I see the following messages in the log every second or
> so:
>
>
>
> 2009-05-21 08:56:49 IDT ERROR:  permission denied to set parameter
> "log_statement"
>
> 2009-05-21 08:56:49 IDT STATEMENT:  SET log_statement='none';
>
> 2009-05-21 08:56:50 IDT ERROR:  permission denied to set parameter
> "log_statement"
>
> 2009-05-21 08:56:50 IDT STATEMENT:  SET log_statement='none';
>
> 2009-05-21 08:56:51 IDT ERROR:  permission denied to set parameter
> "log_statement"
>
> 2009-05-21 08:56:51 IDT STATEMENT:  SET log_statement='none';
>
> 2009-05-21 08:56:52 IDT ERROR:  permission denied to set parameter
> "log_statement"
>
> 2009-05-21 08:56:52 IDT STATEMENT:  SET log_statement='none';
>
> 2009-05-21 08:56:53 IDT ERROR:  permission denied to set parameter
> "log_statement"
>
> 2009-05-21 08:56:53 IDT STATEMENT:  SET log_statement='none';
>
> 2009-05-21 08:56:54 IDT ERROR:  permission denied to set parameter
> "log_statement"
>
> 2009-05-21 08:56:54 IDT STATEMENT:  SET log_statement='none';
>
> 2009-05-21 08:56:55 IDT ERROR:  permission denied to set parameter
> "log_statement"
>
> 2009-05-21 08:56:55 IDT STATEMENT:  SET log_statement='none';
>
>
>
> Any help (or guidelines for additional required information) is
> appreciated.
>
>
>
> Thanks,
>
> Moshe.
>
>

Would seem that the insert statements are being done in conjunction with the SET
log_statement and that the client(s) doing that do not have the necessary
permission level to set log_statement.

--
Adrian Klaver
aklaver@comcast.net

In response to "Moshe Ben-Shoham" <mosheb@nexperience.com>:
>
> We're working with version 8.3.5-1.
>
> Lately we started seeing insert statements hang in the DB. The
> statements come from two different clients.
>
> When it happens, I see the following messages in the log every second or
> so:
>
> 2009-05-21 08:56:49 IDT ERROR:  permission denied to set parameter
> "log_statement"
>
> 2009-05-21 08:56:49 IDT STATEMENT:  SET log_statement='none';

[snip the same errors over an over ...]

> Any help (or guidelines for additional required information) is
> appreciated.

It's kind of hard to tell from what you've posted, but I'll take a guess.

First, the inability to turn log_statement off isn't going to cause the
server to hang or pause or anything.  This error is _not_ the problem,
although it's probably related, since it's happening in conjunction
with the problem.

Based on that, my guess is that you're running a commercial application
that is trying to hide its SQL from you, thus it refuses to run any
queries unless it can turn log_statement to "none".  However, log_statement
can only be changed by a superuser, and I'm betting you did the _right_
thing and didn't make the application user a superuser.

If my guesses are right, the following is true:
* The application is probably garbage.  You'll probably have other problems
  with it if you continue to use it.  You should contact the vendor and
  chew them out for their crappy design.  Either that or they're so
  brilliant that they've managed to write SQL statements that are copyrighted
  or something.
* If you give the application user superuser privs, the application will
  probably start working.
* You _may_ be able to get the application to start working without
  granting superuser privs, simply by setting the value of log_statement
  to "none" in the postgresql.conf.  This is assuming the application is
  smart enough to check the value and only change it if it's not already
  "none".

As I said, the advice is all based on guessing, so good luck with it.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/