Catalogue warning: "there is no transaction in progress"

Development
1

Hello CTA developers team,

We have a question about the Catalogue. We are currently running postgresql version 11-11.8-1 and the Catalogue schema is version 11.0.
We have noticed that the database keeps producing thousands of these logs repeatedly at pg_log, and therefore consuming too much space.

[cta@192.168.XX.XX(43528):cta_catalogue] 2022-09-05 11:12:35 CEST: WARNING: there is no transaction in progress

Our solution (it’s more a patch than a solution) has been to change the log level of the DB for now (the log_min_messages parameter of the postgresql.conf file) so that the warnings stop flooding our log files. However, it does not seem like a right long-term solution.
We wanted to ask if this is a known problem by your team CERN or at other centers using postgresql as the Catalogue and if there is a solution, or if we should just keep it this way for now which, again, doesn’t seem the proper thing to do.

Thank you very much,

Jordi

2

Hi all,
we have the same and we just rotate the logs without any hacks for the moment.

  • cta 4.7.3-1
  • schema 10.0
  • postgres (docker centos/postgresql-13-centos7)

I assume that it could be that there is a periodic (30min) SAVE commit (flush cache) somewhere in CTA RDBMS interface which produce such warning.

Cheers
Victor

3

Hi,

I confirm that this is a general issue when using the postgres cta catalogue; I don’t have any immediate suggestion for you apart from changing the log level or finding another way to filter your log. Functionally this is not indicating any particular fault and it is not causing any direct problem - but possibly indirect problems like larger than necessary logs, as you noted. You could create an issue ticket on the cta tracker, if you have access to that. Otherwise I can open a bug/feature issue, to get this on the list of things to address in a future cta release.

David

4

Hi @jcasals,

Just as a remark, we had the same worry as we started testing w/ CTA but it’s actually a non-issue. Normally the autocommit in postgres is on by default so when a COMMIT is issued in the backend, that warning is generated but it’s not sth you should be worried about. As per the docs, issuing COMMIT when not inside a transaction does no harm, but it will provoke a warning message. As we only log error statements such warnings are suppressed. If it’s CTA issuing the COMMIT then it explicitly has to do it as I’m not certain autocommit is on by default when embedding SQL.

e.g

photon61=# \echo :AUTOCOMMIT
on
photon61=# delete from t_t_dept_p10;
DELETE 56
photon61=# commit;
WARNING:  there is no transaction in progress
COMMIT

cheers,
mwai