Re: Unusual Postgres 9.4 error message

On Mon, Jun 12, 2017 at 12:20 PM, Keefer, Brad (ITS) <Brad(dot)Keefer(at)its(dot)ny(dot)gov
> wrote:

> So, I was looking in the Postgres log file for today on our DB server, and
> it’s nothing but lines like these:
>
> < 2017-06-12 18:37:59.249 UTC >STATEMENT: SELECT "main_instance"."id",
> "main_instance"."uuid", "main_instance"."hostname",
> "main_instance"."created", "main_instance"."modified",
> "main_instance"."version", "main_instance"."capacity" FROM "main_instance"
> LIMIT 1 FOR UPDATE NOWAIT
>
> < 2017-06-12 18:38:05.289 UTC >ERROR: could not obtain lock on row in
> relation "main_instance"
>
>
> As far as I can tell, the DB is up, accepting connections, and basically
> working the way it should. So those error messages are confusing me. Are
> they something that can be ignored? If so, is it possible to turn them off?
>

​The error comes from the use of FOR UPDATE NOWAIT as documented here:

https://www.postgresql.org/docs/9.4/static/sql-select.html

It affects nothing aside from the query executing the SELECT statement (and
anything that depends on it). That statement has a LIMIT 1 without an
ORDER BY which could be intentional or a mis-understanding of how NOWAIT
works (which is, I believe, that one random row is picked and then a lock
is attempted; not pick the first unlocked row).

In any case you would need to figure out what application is issuing that
statement and inquire of its creator.

Its hard to say whether ignoring the error is the correct response without
knowing how about the usage of the statement in question.

I do not believe they can just be turned off - the client code would need
to be modified.

David J.