CODEWITHSUNDEEP
databasepostgresqlpostgresql-9.6
Tapxxor Stanijkis
Tapxxor Stanijkis

Reputation: 81

How to add informations on WARNING message in postgres

I get the following warning

WARNING: there is already a transaction in progress

in my database and i want to investigate the reason this happens. However due to the database is accessible through many microservices i cannot find which service is trying to start a new/parallel connection.

How can i increase the level of information of this message? Like a timestamp, who tried to start the connection like client_addr field or any other information that will reveal the root of the fault.

Thanks in advance

Upvotes: 1

Views: 507

Answers (2)

Dan
Dan

Reputation: 1881

There's plenty you could do to find out what's going on. First, you could check PostgreSQL logs to see what's going on. If you do not have access to the logs. You can check which queries are active, idle, or idle in transaction by running the next query:

SELECT
  pid,
  query,
  state
FROM pg_stat_activity

There you can see which transaction is currently running by adding to the query WHERE state='active'

IMPORTANT NOTE:

If your using services to access the database (specialy c# services (by experience)) you have to check your connection to the database. Because if it is not correctly configured you'll end up with services that can accept only one user per transaction and that's really dangerous.

The problem might be that you are sending your calls to the database through one connection and the 'service' never opens new connections. Therefore, PostgreSQL will reject any incoming queries and set the message:

WARNING: there is already a transaction in progress

Because the connection channel is being used by a transaccion.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51559

the source - starting transaction twice, example:

t=# begin;
BEGIN
Time: 22.594 ms
t=# begin;
WARNING:  there is already a transaction in progress
BEGIN
Time: 1.269 ms

to see who, when, set log_min_messages to at least warning, log_line_prefix to have %h for IP and %m for time, %u for username - https://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT, (logging_collector of course on) and check logs

Upvotes: 1

Related Questions