WARNING: there is already a transaction in progress. After updating to version 4.6

[AlexCh1991]

Hi!

We have installed pg_profile version 4.3 on a centralized sample collection server with PostgreSQL 15.7. Today we updated to version 4.6, as described in the documentation (sequentially updated to 4.4, 4.5, 4.6). After the update, already on version 4.6 samples are created

profile_reports=# select take_sample('server_name'); take_sample

(OK,00:00:00.74) (1 row)

But Warning messages appear in the PostgreSQL log:

2024-06-10 10:43:38.139 MSK [3853688] admin_profile@profile_reports LOG: properties: { "timings": { }, "settings": [ { "name": "server_version_num", "unit": null, "reset_val": "160002", "pending_restart": false } ], "extensions": [ { "extname": "pg_wait_sampling", "extversion": "1.1", "extnamespace": "public" }, { "extname": "pg_stat_kcache", "extversion": "2.2.3", "extnamespace": "public" }, { "extname": "pg_stat_statements", "extversion": "1.10", "extnamespace": "public" } ], "properties": { "topn": 20, "pgpro": false, "last_sample_id": 6, "server_connstr": "host=ip_address dbname=postgres port=5432", "subsample_enabled": true }, "collect_timings": false } 2024-06-10 10:43:38.139 MSK [3853688] admin_profile@profile_reports CONTEXT: PL/pgSQL function init_sample(integer) line 192 at RAISE PL/pgSQL function take_sample(integer,boolean) line 32 at assignment PL/pgSQL function take_sample(name,boolean) line 16 at assignment 2024-06-10 10:43:38.139 MSK [3853688] admin_profile@profile_reports STATEMENT: select take_sample('server_name'); WARNING: there is already a transaction in progress WARNING: there is no transaction in progress

Also on the Postgresql instance from which the sample is taken there is a message in the logs: postgres@postgres WARNING: there is already a transaction in progress postgres@postgres WARNING: there is no transaction in progress

[zubkov-andrei]

Hi! There is a debug message in tar package. I've fixed it. Warnings about transaction processing are fixed too. You should reinstall extension. If you can't loose your data, take a backup and create a profile dump using export_data() function. Then load the data using import_data() function. You'll need enable imported server to continue data collection.

[AlexCh1991]

Hi! Thanks for the quick response and for the new version. When importing data I get the following error

profile_reports=# SELECT * FROM import_data('import'); NOTICE: Started processing table servers NOTICE: Finished processing table servers (0 rows) NOTICE: Started processing table samples NOTICE: Table samples processed: 1000 rows NOTICE: Table samples processed: 2000 rows NOTICE: Table samples processed: 3000 rows NOTICE: Table samples processed: 4000 rows NOTICE: Table samples processed: 5000 rows NOTICE: Table samples processed: 6000 rows NOTICE: Table samples processed: 7000 rows NOTICE: Table samples processed: 8000 rows NOTICE: Table samples processed: 9000 rows NOTICE: Table samples processed: 10000 rows NOTICE: Table samples processed: 11000 rows NOTICE: Table samples processed: 12000 rows NOTICE: Table samples processed: 13000 rows NOTICE: Table samples processed: 14000 rows NOTICE: Table samples processed: 15000 rows NOTICE: Table samples processed: 16000 rows NOTICE: Finished processing table samples (16311 rows) NOTICE: Started processing table baselines NOTICE: Finished processing table baselines (0 rows) NOTICE: Started processing table sample_settings NOTICE: Table sample_settings processed: 1000 rows NOTICE: Table sample_settings processed: 2000 rows NOTICE: Table sample_settings processed: 3000 rows NOTICE: Table sample_settings processed: 4000 rows NOTICE: Table sample_settings processed: 5000 rows NOTICE: Table sample_settings processed: 6000 rows NOTICE: Finished processing table sample_settings (6365 rows) NOTICE: Started processing table server_subsample ERROR: null value in column "server_id" of relation "server_subsample" violates not-null constraint DETAIL: Failing row contains (null, t, null, null, null, null). CONTEXT: SQL statement "INSERT INTO server_subsample(server_id, subsample_enabled, min_query_dur, min_xact_dur, min_xact_age, min_idle_xact_dur) SELECT (srv_map ->> dr.server_id::text)::integer, dr.subsample_enabled, dr.min_query_dur, dr.min_xact_dur, dr.min_xact_age, dr.min_idle_xact_dur FROM json_to_record(datarow.row_data) AS dr( server_id integer, subsample_enabled boolean, min_query_dur interval hour to second, min_xact_dur interval hour to second, min_xact_age bigint, min_idle_xact_dur interval hour to second ) ON CONFLICT ON CONSTRAINT pk_server_subsample DO UPDATE SET (subsample_enabled, min_query_dur, min_xact_dur, min_xact_age, min_idle_xact_dur) = (EXCLUDED.subsample_enabled, EXCLUDED.min_query_dur, EXCLUDED.min_xact_dur, EXCLUDED.min_xact_age, EXCLUDED.min_idle_xact_dur) WHERE (server_subsample.subsample_enabled, server_subsample.min_query_dur, server_subsample.min_xact_dur, server_subsample.min_xact_age, server_subsample.min_idle_xact_dur) IS DISTINCT FROM (EXCLUDED.subsample_enabled, EXCLUDED.min_query_dur, EXCLUDED.min_xact_dur, EXCLUDED.min_xact_age, EXCLUDED.min_idle_xact_dur)" PL/pgSQL function import_section_data_profile(refcursor,name,jsonb,jsonb,text[]) line 2460 at SQL statement SQL statement "SELECT rows_processed, COALESCE(new_import_meta, import_meta) FROM import_section_data_profile( c_datarows, r_result.relname, tmp_srv_map, import_meta, dump_versions)" PL/pgSQL function import_data(regclass,text) line 251 at SQL statement

Can you help me?

[vut12]

Hi! Thanks for the quick response and for the new version. When importing data I get the following error

ERROR: null value in column "server_id" of relation "server_subsample" violates not-null constraint

Can you help me?

This error also occurs when export contains Server without samples (sample_settings contains no rows for server_id)