Format error on COPY for powa_statements_snapshot

[ua-code-dragon]

Found on one server out of many. Postgres 15.4 collector 1.2.0 Probably a problem with the delimiter

Sep 27 00:17:44 SERVER powa-collector.py[607188]: 2023-09-27 00:17:44,942 SERVER:5432 WARNING: Error while inserting data:
Sep 27 00:17:44 SERVER powa-collector.py[607188]: extra data after last expected column
Sep 27 00:17:44 SERVER powa-collector.py[607188]: CONTEXT:  COPY powa_statements_src_tmp, line 1: "6#0112023-09-27 00:17:35.871034+03#01110#01160630#011t#0113914805734239635900#011select table0_.id as id1, ..."
Sep 27 00:17:44 SERVER powa-collector.py[607188]:
Sep 27 00:17:44 SERVER powa-collector.py[607188]: 2023-09-27 00:17:44,942 SERVER:5432 WARNING: Giving up for powa_statements_snapshot

[rjuju]

Hi,

Is this a problem that suddenly happen or did it start when that server was setup?

I'm a bit surprised of the format of the data:

CONTEXT: COPY powa_statements_srctmp, line 1: "6#0112023-09-27 00:17:35.871034+03#01110#01160630#011t#0113914805734239635900#011select table0.id as id1, ..."

The data should be exported with default COPY option, so tab-separated and not "#" separated.

Which version of powa-archivist is installed on the remote server and repository server?

[ua-code-dragon]

On data source server

> yum list installed |grep powa
powa-archivist_15.x86_64                    4.2.0-1PGDG.rhel8                   @pgdg15
powa_15.x86_64                              4.2.0-1PGDG.rhel9                   @pgdg15

On collector/web server

powa_15.x86_64 4.1.4-1.rhel9 @pgdg15

Another surprise - I see #-delimiter in the collector log but space/tab at the error presentation on the web

[df7cb]

The #011 might be actual tab characters mangled by syslog.

[ua-code-dragon]

So looks like 4.2.0 to 4.1.4 incompatibility. Is it possible?

[df7cb]

Did you run alter extension powa update; ?

[ua-code-dragon]

Extension update script seems not working due to another issue - file:powa--4.1.4--4.2.0.sql line:10 ALTER TABLE ... ADD toplevel boolean NOT NULL; it should not work if table not empty. Fixed it manually.

So I have upgraded the source-side powa to 4.2.0 and got another error

Sep 29 00:21:46  python3[744118]: 2023-09-29 00:21:46,602 server:5432 WARNING: Error while inserting data:
Sep 29 00:21:46  python3[744118]: invalid input syntax for type bigint: "t"
Sep 29 00:21:46  python3[744118]: CONTEXT:  COPY powa_statements_src_tmp, line 1, column queryid: "t"
Sep 29 00:21:46  python3[744118]:
Sep 29 00:21:46  python3[744118]: 2023-09-29 00:21:46,602 server:5432 WARNING: Giving up for powa_statements_snapshot

Reproduced it with the fresh deploy of both source and collector of latest versions. Now trying to downgrade everything to 4.1.4 :(

[rjuju]

Oh right, that's indeed a big problem :( I unfortunately realize that we don't test the upgrade scripts with non-empty tables.

Another probably easier way to go for you would be, if you can, to modify the powa--4.1.4--4.2.0.sql directly and fix the 3 problematic lines:

https://github.com/powa-team/powa-archivist/blob/master/powa--4.1.4--4.2.0.sql#L10-L12

Basically changing them to

ALTER TABLE public.powa_statements_src_tmp ADD toplevel boolean NOT NULL DEFAULT false;
ALTER TABLE public.powa_statements_history ADD toplevel boolean NOT NULL DEFAULT false;
ALTER TABLE public.powa_statements_history_current ADD toplevel boolean NOT NULL DEFAULT false;

That's what the compatibility layer will be generating anyway if you have an older version of pg_stat_statements.

I unfortunately won't have access to my computer for a few days so I won't be able to push a fix before then.

[ua-code-dragon]

Update script is a little issue. Big issue is powa-collector seeems not working with 4.2.0 due to incompatible column list and COPY without exact columns Could you advice something?

[rjuju]

On Fri, 29 Sept 2023, 06:45 Yuriy Vountesmery, @.***> wrote:

Update script is a little issue. Big issue is powa-collector seeems not working with 4.2.0 due to incompatible column list and COPY without exact columns Could you advice something?

No, unfortunately the whole design is that the powa extension on the remote server takes care of any backward compatibility problem, and it relies on both the repository and remote servers having at least the same major powa version (eg 4.2.x).

powa-collector doesn't have any specific knowledge of the datasources, it just uses what the powa-archivist extension provides.

The only last possibility I see to do the upgrade while keeping your data would be to try to pg_dump the powa database, drop powa-archivist extension and recreate it, and then reload the dump. I'm not sure that would work, and as I said I basically won't have computer access before Monday or Tuesday so I won't be able to test before that.

Of course if you don't mind losing your current history, dropping powa-archivist on the repository server and recreating it with the latest version will easily get rid of the problem. You could also rename the current powa database and create a new one and install the new version there, and keep the old database for a few days in case you need to access the older metrics. I'm really sorry that I don't have better options than that.

[rjuju]

It took me a bit of time to go back to this problem, but I now added a check to make sure that (most) tables aren't empty when testing the upgrade scripts for consistency, and also worked on a fix.

If you have a way to try, the commit is https://github.com/powa-team/powa-archivist/commit/73a6605ec1912ff7daaf8a003ae74eac8574052f, otherwise I will just release a new version shortly.

[rjuju]

I just released version 4.2.1 to fix that issue.