Comments (20)
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.
from powa-archivist.
Powa is installed in local mode, only the webui is running on a different server.
from powa-archivist.
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.
from powa-archivist.
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
from powa-archivist.
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.
from powa-archivist.
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: ""
)
from powa-archivist.
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.
from powa-archivist.
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);
from powa-archivist.
Ok. Can you confirm what postgres major version you're using?
from powa-archivist.
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)
from powa-archivist.
Sorry I should have mentioned, we are running PostgreSQL 12.10.
from powa-archivist.
I have a 12.9 available locally, and I reproduced the exact same behavior as showed in #51 (comment) 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.
from powa-archivist.
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?
from powa-archivist.
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.
from powa-archivist.
from powa-archivist.
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.
from powa-archivist.
It has been reported to me that the webUI is also working fine with the new function, so it seams as an appropriate workaround.
from powa-archivist.
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!
from powa-archivist.
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!
from powa-archivist.
Thanks you :)
For the record I just pushed a fix for that function and also powa_kcache_src which had the same problem: d9c4ccf.
This will be available when version 4.1.4 will be out.
from powa-archivist.
Related Issues (20)
- PG13: powa_take_snapshot(0): function "powa_statements_snapshot" failed: column pgss.total_time does not exist HOT 7
- Snapshot pg_stat_progres_* views? HOT 3
- Work without create extension HOT 1
- Bugs in powa_qualstats_aggregate_constvalues_current, suggested rewrite HOT 2
- powa_kcache_metrics_current grows enormously HOT 12
- powa_wait_sampling_snapshot(0) query is running by active state for a long time HOT 5
- does powa support PG14? HOT 6
- hard disk full HOT 2
- memory leak HOT 11
- duplicate key value violates unique constraint "powa_statements_pkey" HOT 12
- Extension pg_profile influences extension powa HOT 3
- Enhancement proposition HOT 3
- Track number of concurrent connections HOT 1
- "powa_statements_snapshot" failed HOT 4
- DROP queries are hanged if powa is enabled HOT 5
- we need to take toplevel into account in pg_stat_statements HOT 2
- Pg 17 support HOT 3
- Problem with remote collection since toplevel was added HOT 15
- Unparameterized sql query in powa_statements table HOT 16
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from powa-archivist.