ROLLBACK statements found on postgresql.log

Help
, , , ,
1

Hello!

We have found several “ROLLBACK” statements in the postgresql.log on all nodes in the cluster.

Example:

2024-09-27 00:22:01 UTC pid=[21967][5]  db=[DataBaseName] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK
2024-09-27 00:22:01 UTC pid=[21967][11]  db=[DataBaseName] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK
2024-09-27 00:22:01 UTC pid=[21973][5]  db=[DataBaseNameX] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK
2024-09-27 00:22:01 UTC pid=[21973][11]  db=[DataBaseNameX] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK
2024-09-27 00:22:01 UTC pid=[21974][5]  db=[postgres] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK
2024-09-27 00:22:01 UTC pid=[21974][11]  db=[postgres] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK
2024-09-27 00:22:01 UTC pid=[21974][15]  db=[postgres] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK
2024-09-27 00:22:01 UTC pid=[21967][15]  db=[DataBaseName] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK
2024-09-27 00:22:01 UTC pid=[22007][5]  db=[DataBaseName] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK
2024-09-27 00:22:01 UTC pid=[22007][11]  db=[DataBaseName] usr=[netdata] client=[localhost] app=[[unknown]] LOG:  statement: ROLLBACK

We have checked permissions and the overall configuration, and everything seems to be in order.

Do you know if this is expected behavior, or could it be a misconfiguration?

Thanks for your help,
Richard Barrantes

2 Likes
2

This ROLLBACK statements were caused while running a query for a chart using pandas. Rollback statement occurs as the query is not closed before the next read for the chart.

This was the previous configuration:

- name: "DataBase.QueueTable"
        title: "Queue_Tables_Monitoring"
        family: "Queue_Tables_Monitoring"
        context: "Custom Alerts.Queue_Tables_Monitoring"
        type: "line"
        units: "row_count"
        df_steps: >
          pd.read_sql(""" SELECT count(*) as "DataBase.QueueTable" from "QueueTable" """, con=create_engine('postgresql://netdata@localhost/DataBase'));

The issue was solved by adding “isolation_level=“AUTOCOMMIT”” variable like this:

- name: "DataBase.QueueTable"
        title: "Queue_Tables_Monitoring"
        family: "Queue_Tables_Monitoring"
        context: "Custom Alerts.Queue_Tables_Monitoring"
        type: "line"
        units: "row_count"
        df_steps: >
          pd.read_sql(""" SELECT count(*) as "DataBase.QueueTable" from "QueueTable" """, con=create_engine('postgresql://netdata@localhost/DataBase', isolation_level="AUTOCOMMIT"));

Related articles:

https://pandas.pydata.org/docs/reference/io.html
https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html#pandas.read_sql
https://docs.sqlalchemy.org/en/20/core/connections.html
https://docs.sqlalchemy.org/en/20/dialects/postgresql.html

1 Like