powa_take_snapshot fails following powa upgrade to 4.1.3

[arnobnq]

Hello,

On a local powa installation, we have upgraded powa from 4.0.0 to 4.1.3, powa-web from 4.0.0 to 4.1.2. Ever since, the snapshots fail to execute, the webui shows an invalid input syntax for type integer: "" error, and data doesn't get collected. Here is the full error from postgresql.conf:

 [8827]: [156-1] user=,db=,client= WARNING:  powa_take_snapshot(0): function "powa_statements_snapshot" failed:
                  state  : 22P02
                  message: invalid input syntax for type integer: ""
                  detail : 
                  hint   : 
                  context: PL/pgSQL function powa_statements_src(integer) line 6 at SQL statement
    SQL statement "WITH capture AS(
            SELECT *
            FROM powa_statements_src(_srvid)
        ),
        mru as (UPDATE powa_statements set last_present_ts = now()
                FROM capture
                WHERE powa_statements.queryid = capture.queryid
                  AND powa_statements.dbid = capture.dbid
                  AND powa_statements.userid = capture.userid
                  AND powa_statements.srvid = _srvid
        ),
        missing_statements AS(
            INSERT INTO public.powa_statements (srvid, queryid, dbid, userid, query)
                SELECT _srvid, queryid, dbid, userid, min(query)
                FROM capture c
                WHERE NOT EXISTS (SELECT 1
                                  FROM powa_statements ps
                                  WHERE ps.queryid = c.queryid
                                  AND ps.dbid = c.dbid
                                  AND ps.userid = c.userid
                                  AND ps.srvid = _srvid
                )
                GROUP BY queryid, dbid, userid
        ),

        by_query AS (
            INSERT INTO public.powa_statements_history_current
                SELECT _srvid, queryid, dbid, userid,
                ROW(
                    ts, calls, total_exec_time, rows,
                    shared_blks_hit, shared_blks_read, shared_blks_dirtied,
                    shared_blks_written, local_blks_hit, local_blks_read,
                    local_blks_dirtied, local_blks_written, temp_blks_read,
                    temp_blks_written, blk_read_time, blk_write_time,
                    plans, total_plan_time,
                    wal_records, wal_fpi, wal_bytes
                )::powa_statements_history_record AS record
                FROM capture
        ),

        by_database AS (
            INSERT INTO public.powa_statements_history_current_db
                SELECT _srvid, dbid,
                ROW(
                    ts, sum(calls),
                    sum(total_exec_time), sum(rows), sum(shared_blks_hit),
                    sum(shared_blks_read), sum(shared_blks_dirtied),
                    sum(shared_blks_written), sum(local_blks_hit),
                    sum(local_blks_read), sum(local_blks_dirtied),
                    sum(local_blks_written), sum(temp_blks_read),
                    sum(temp_blks_written), sum(blk_read_time), sum(blk_write_time),
                    sum(plans), sum(total_plan_time),
                    sum(wal_records), sum(wal_fpi), sum(wal_bytes)
                )::powa_statements_history_record AS record
                FROM capture
                GROUP BY dbid, ts
        )

        SELECT count(*)                     FROM capture"
    PL/pgSQL function powa_statements_snapshot(integer) line 15 at SQL statement
    SQL statement "SELECT powa_statements_snapshot(0)"
    PL/pgSQL function powa_take_snapshot(integer) line 63 at EXECUTE
    SQL statement "SELECT public.powa_take_snapshot()"

Here are the extensions versions :

        Name         | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 btree_gist         | 1.5     | public     | support for indexing common datatypes in GiST
 hypopg             | 1.3.0   | public     | Hypothetical indexes for PostgreSQL
 pg_qualstats       | 2.0.3   | public     | An extension collecting statistics about quals
 pg_stat_statements | 1.7     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 powa               | 4.1.3   | public     | PostgreSQL Workload Analyser-core

Any idea as to why this is occurring?

Thank you!

[rjuju]

Hi,

Are you using powa in remote mode or local mode?

If remote, did you update the extension on all the remote servers too? I'm mentioning that because:

WARNING:  powa_take_snapshot(0)

means that this is either reported by the local server in local mode or a remote server in remote mode.

[arnobnq]

Powa is installed in local mode, only the webui is running on a different server.

[rjuju]

Ok!

Can you confirm that you reproduce the problem just executing this on the powa database:

SELECT powa_statements_src(0);

Also, I'm moving the issue to the powa-archivist repository, as it's where the problem is.

[arnobnq]

Indeed, executing this command against the powa database fails with the same error :

powa=# SELECT powa_statements_src(0);
WARNING:  nonstandard use of escape in a string literal
HINT : Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING:  nonstandard use of escape in a string literal
LINE 1 : SELECT regexp_split_to_array(extversion, '\.')              ...
                                                   ^
HINT : Use the escape string syntax for escapes, e.g., E'\r\n'.
QUERY : SELECT regexp_split_to_array(extversion, '\.')                            FROM pg_extension
        WHERE extname = 'pg_stat_statements'
ERROR:  invalid input syntax for type integer: ""
CONTEXT : PL/pgSQL function powa_statements_src(integer) line 6 at SQL statement

[rjuju]

Ah, do you have standard_conforming_string set to off on this instance?

If you first do "SET standard_conforming_strings = on,", does the function work? If yes, as a workaround you could maybe change it on the powa database and/or the powa dedicated role (if any), like

ALTER ROLE mypowarole IN DATABASE mypowadatabase SET standard_conforming_strings = on;

and restart powa-collector (or use pg_terminate_backend() to restart the powa bgworker if that's what you're using, or restart the instance), until I release a new version that supports this setting.

[arnobnq]

You are right that standard_conforming_strings is set to off. But please review the following :

powa=# show standard_conforming_strings;
 standard_conforming_strings 
-----------------------------
 off
(1 ligne)

powa=# SET standard_conforming_strings = on;
SET
powa=# SELECT powa_statements_src(0);
ERROR:  invalid input syntax for type integer: ""
CONTEXT : PL/pgSQL function powa_statements_src(integer) line 6 at SQL statement

Setting this parameter to on does fix the warnings, but the main error is still occurring as far as I can see (invalid input syntax for type integer: "")

[rjuju]

did you close and start a new psql session? I'm wondering if the function has been cached since and is still using the wrong code path.

[arnobnq]

The above sql commands were run in a single session, if that's what you mean. Just to make sure I ran the same commands again on a new psql session, with the same results.

SET standard_conforming_strings = on;
SELECT powa_statements_src(0);

[rjuju]

Ok. Can you confirm what postgres major version you're using?

[rjuju]

For the record that's not the behavior I see on postgres 10:

powa=# SELECT version();
                                                            version
-------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.19@cb91cb8aac on x86_64-apple-darwin20.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit
(1 row)

powa=# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description
--------------------+---------+------------+-----------------------------------------------------------
 btree_gist         | 1.5     | public     | support for indexing common datatypes in GiST
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 powa               | 4.1.3   | public     | PostgreSQL Workload Analyser-core
(4 rows)

powa=# show standard_conforming_strings ;
 standard_conforming_strings
-----------------------------
 off
(1 row)

powa=# select powa_statements_src (0);
WARNING:  nonstandard use of escape in a string literal
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING:  nonstandard use of escape in a string literal
LINE 1: SELECT regexp_split_to_array(extversion, '\.')              ...
                                                 ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
QUERY:  SELECT regexp_split_to_array(extversion, '\.')                            FROM pg_extension
        WHERE extname = 'pg_stat_statements'
ERROR:  invalid input syntax for integer: ""
CONTEXT:  PL/pgSQL function powa_statements_src(integer) line 6 at SQL statement

powa=# set standard_conforming_strings = on;
SET
powa=# select powa_statements_src (0);
ERROR:  invalid input syntax for integer: ""
CONTEXT:  PL/pgSQL function powa_statements_src(integer) line 6 at SQL statement

powa=# \c
You are now connected to database "powa" as user "jrouhaud".

powa=# set standard_conforming_strings = on;
SET
powa=# select powa_statements_src (0);
                                                                                                    powa_statements_src
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ("2022-03-25 12:06:27.472022+01",10,36934,1153186663,"select powa_statements_src ($1)",1,27.676785,1,644,43,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
 ("2022-03-25 12:06:27.472022+01",10,36934,1605857175,"set standard_conforming_strings = on",4,0.13406,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
 ("2022-03-25 12:06:27.472022+01",10,36934,4115798515,"SELECT version()",1,0.023532,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
 ("2022-03-25 12:06:27.472022+01",10,36934,901623699,"show standard_conforming_strings",1,0.02,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
 ("2022-03-25 12:06:27.472022+01",10,36934,2125976981,"SELECT e.extname AS ""Name"", e.extversion AS ""Version"", n.nspname AS ""Schema"", c.description AS ""Description""                                                +
 FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = $1::pg_catalog.regclass                           +
 ORDER BY 1",1,2.027415,4,14,4,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
 ("2022-03-25 12:06:27.472022+01",10,36934,1400654985,"SELECT name FROM  (SELECT pg_catalog.lower(name) AS name FROM pg_catalog.pg_settings   UNION ALL SELECT $1   UNION ALL SELECT $2) ss  WHERE substring(name,$3,$4)=$5+
 LIMIT $6",3,6.078681,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
(6 rows)

[arnobnq]

Sorry I should have mentioned, we are running PostgreSQL 12.10.

[rjuju]

I have a 12.9 available locally, and I reproduced the exact same behavior as showed in https://github.com/powa-team/powa-archivist/issues/51#issuecomment-1078912175 with the same extension versions, ie. on a fresh psql session it works if I change the standard_conforming_strings first. I'm not sure what else is different on your instance.

[rjuju]

Can you try to edit the powa_statements_src function changing

        SELECT regexp_split_to_array(extversion, '\.') INTO STRICT v_pgss

with

        SELECT regexp_split_to_array(extversion, E'\\.') INTO STRICT v_pgss

This will work with both settings and may fix your problem. If not, can you list the non default parameters you're using, and anything that may be different from some vanilla instance?

[arnobnq]

Here is an overview of current non default parameters (usernames are obfuscated) :

                 name                  |                            current_setting                             
---------------------------------------+------------------------------------------------------------------------
 application_name                      | psql                                                                   
 archive_command                       | /usr/bin/timeout 1800 /usr/bin/archive_wal %p                          
 archive_mode                          | on                                                                     
 archive_timeout                       | 5min                                                                   
 autovacuum_analyze_scale_factor       | 0.05                                                                   
 autovacuum_analyze_threshold          | 200                                                                    
 autovacuum_vacuum_threshold           | 500                                                                    
 bytea_output                          | escape                                                                 
 checkpoint_completion_target          | 0.9                                                                    
 client_encoding                       | UTF8                                                                   
 data_checksums                        | on                                                                     
 DateStyle                             | ISO, DMY                                                               
 default_text_search_config            | pg_catalog.french                                                      
 dynamic_shared_memory_type            | posix                                                                  
 effective_cache_size                  | 40GB                                                                   
 jit_above_cost                        | 2e+06                                                                  
 jit_inline_above_cost                 | 2.5e+06                                                                
 jit_optimize_above_cost               | 2.5e+06                                                                
 lc_collate                            | fr_FR.UTF-8                                                            
 lc_ctype                              | fr_FR.UTF-8                                                            
 lc_messages                           | en_US.UTF-8                                                            
 lc_monetary                           | fr_FR.UTF-8                                                            
 lc_numeric                            | fr_FR.UTF-8                                                            
 lc_time                               | fr_FR.UTF-8                                                            
 listen_addresses                      | *                                                                      
 log_autovacuum_min_duration           | 0                                                                      
 log_checkpoints                       | on                                                                     
 log_destination                       | stderr                                                                 
 log_directory                         | /logs/postgresql                                                       
 log_file_mode                         | 0640                                                                   
 log_filename                          | postgresql-%Y-%m-%d_%H%M%S.log                                         
 log_line_prefix                       | %t [%p]: [%l-1] user=%u,db=%d,client=%h                                
 log_lock_waits                        | on                                                                     
 log_min_duration_statement            | -1                                                                     
 log_rotation_age                      | 1d                                                                     
 log_rotation_size                     | 0                                                                      
 log_temp_files                        | 0                                                                      
 log_timezone                          | Europe/Paris                                                           
 log_truncate_on_rotation              | on                                                                     
 logging_collector                     | on                                                                     
 maintenance_work_mem                  | 1GB                                                                    
 max_connections                       | 300                                                                    
 max_parallel_workers                  | 4                                                                      
 max_prepared_transactions             | 360                                                                    
 max_stack_depth                       | 7MB                                                                    
 max_wal_size                          | 15GB                                                                   
 min_wal_size                          | 80MB                                                                   
 pg_log_userqueries.log_db             | postgres|#REDACTED|#REDACTED                                        
 pg_log_userqueries.log_label          | USERQUERIES                                                            
 pg_log_userqueries.log_level          | log                                                                    
 pg_log_userqueries.log_superusers     | on                                                                     
 pg_log_userqueries.log_user_blacklist | #REDACTED|#REDACTED|#REDACTED|#REDACTED|#REDACTED                
 pg_log_userqueries.match_all          | on                                                                     
 port                                  | 10864                                                                  
 powa.frequency                        | 5min                                                                   
 powa.retention                        | 30d                                                                    
 server_encoding                       | UTF8                                                                   
 server_version                        | 12.10                                                                  
 shared_buffers                        | 8GB                                                                    
 shared_preload_libraries              | pg_log_userqueries,pg_stat_statements,powa,pg_stat_kcache,pg_qualstats 
 standard_conforming_strings           | off                                                                    
 TimeZone                              | Europe/Paris                                                           
 transaction_deferrable                | off                                                                    
 transaction_isolation                 | read committed                                                         
 transaction_read_only                 | off                                                                    
 wal_buffers                           | 16MB                                                                   
 wal_level                             | replica                                                                
 wal_segment_size                      | 16MB                                                                   
 work_mem                              | 64MB 

I tried the suggested fix on the powa_statements_src function, it does seem to work ! Querying the function is now returning 2166 rows, and I don't see the snapshots failling every 5 minutes in the PostgreSQL log file since the function has been recreated. I'll check with the people who have access to the webui if this solves the issue on their side of things and report back.

[rjuju]

Le ven. 25 mars 2022 à 19:58, arnobnq @.***> a écrit :

I tried the suggested fix on the powa_statements_src function, it does seem to work ! Querying the function is now returning 2166 rows, and I don't see the snapshots failling every 5 minutes in the PostgreSQL log file since the function has been recreated. I'll check with the people who have access to the webui is this solves the issue on their side of things and report back.

ah great news! somehow, something was preventing the standard_conforming_string change for fixing it interactively. let me know if that also fixes the UI. FTR this is the change I will apply soon for a next powa version, but since standard_conforming_string isn't something changed very often I probably won't release the new version immediately.

[arnobnq]

I didn't get an answer about the webui yet, but was wondering about what you said :

something was preventing the standard_conforming_string change for fixing it interactively

Does that mean the original powa_statements_src function could work if we set standard_conforming_string to on on the role/database level with the previously suggested SQL ?

ALTER ROLE mypowarole IN DATABASE mypowadatabase SET standard_conforming_strings = on;

I've only set it at the session level in my previous tests, and didn't actually tried to set it permanently, as it didn't seam to solve the issue.

[arnobnq]

It has been reported to me that the webUI is also working fine with the new function, so it seams as an appropriate workaround.

[rjuju]

Does that mean the original powa_statements_src function could work if we set standard_conforming_string to on on the role/database level with the previously suggested SQL ?

In theory yes, this should be able to fix the issue without any modification to the function code.

It has been reported to me that the webUI is also working fine with the new function, so it seams as an appropriate workaround.

Perfect!

[arnobnq]

I don't think we will revert to the previous code to test this out, as it will break the snapshots again and this is an acceptable solution, but it's good to know !

You help has been greatly appreciated, thank you!

[rjuju]

Thanks you :)

For the record I just pushed a fix for that function and also powa_kcache_src which had the same problem: https://github.com/powa-team/powa-archivist/commit/d9c4ccf3f035d053aad4be15ffc31ca4590c6190.

This will be available when version 4.1.4 will be out.