Обсуждение: [ADMIN] Unusual Postgres 9.4 error message

Поиск
Список
Период
Сортировка

[ADMIN] Unusual Postgres 9.4 error message

От
"Keefer, Brad (ITS)"
Дата:

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"

< 2017-06-12 18:38:05.289 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:12.496 UTC >ERROR:  could not obtain lock on row in relation "main_instance"

< 2017-06-12 18:38:12.496 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:59.267 UTC >ERROR:  could not obtain lock on row in relation "main_instance"

< 2017-06-12 18:38:59.267 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

 

Our setup is a little different, as we are running the 2ndQuadrant BDR-enabled Postgres 9.4 package on the server:

[14:39:01 root@dch282ml5pgsql> yum list installed |grep postgres

Repository epel is listed more than once in the configuration

Repository epel-source is listed more than once in the configuration

Repository jlaska-rabbitmq is listed more than once in the configuration

Repository pgdg94 is listed more than once in the configuration

Repository pgdg94 is listed more than once in the configuration

Repository pgdg94-source is listed more than once in the configuration

postgresql-bdr94-2ndquadrant-redhat.noarch

 

and the OS is RHEL 7.3.

 

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?

 

Just trying to figure out what is trying to access the database that could be generating statements like that.

 

Any information you could provide would be greatly appreciated.

 

Thanks.

 

Brad Keefer

Red Hat Consultant – RHCSA

 

Office of Information Technology Services

Tax & Finance Building 8, Floor 7, Harriman Campus

C: 919-210-7045 | brad.keefer@its.ny.gov

www.its.ny.gov

 

Re: [ADMIN] Unusual Postgres 9.4 error message

От
"David G. Johnston"
Дата:
On Mon, Jun 12, 2017 at 12:20 PM, Keefer, Brad (ITS) <Brad.Keefer@its.ny.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.