Error "public.powa_take_snapshot()" in PostgreSQL
pablodelflo opened this issue · 7 comments
I am observing this error in the log of my PostgreSQL 12:
2023-10-07 16:03:06 UTC:LOG: POWA connected to database powa
2023-10-07 16:03:06 UTC:ERROR: query returned more than one row
2023-10-07 16:03:06 UTC:HINT: Make sure the query returns a single row, or use LIMIT 1.
2023-10-07 16:03:06 UTC:CONTEXT: PL/pgSQL function powa_take_snapshot(integer) line 31 at SQL statement
SQL statement "SELECT public.powa_take_snapshot()"
2023-10-07 16:03:06 UTC:LOG: background worker "powa" (PID 34049) exited with exit code 1
I have tried to restart the DB but the error appears again. If I launch the SELECT statement "[local]:
[local] postgres@powa=# SELECT public.powa_take_snapshot();
ERROR: query returned more than one row
HINT: Make sure the query returns a single row, or use LIMIT 1.
CONTEXT: PL/pgSQL function powa_take_snapshot(integer) line 31 at SQL statement
Time: 5.702 ms
Hi,
Which version of powa-archivist is installed on that server? Using version 4.2.0 as a reference, I see that line 31 of the function is around this part:
UPDATE powa_snapshot_metas
SET coalesce_seq = coalesce_seq + 1,
errors = NULL,
snapts = now()
WHERE srvid = _srvid
RETURNING coalesce_seq INTO purge_seq;Having multiple rows returned there is quite suspicious, as srvid is the primary key of the powa_snapshot_metas table.
Can you check what those queries are returning on the same database?
- SELECT * FROM powa_snapshot_metas
- SELECT * FROM powa_snapshot_metas WHERE srvid = 0
Hi.
The two queries return the following:
[local] postgres@powa=# SELECT * FROM powa_snapshot_metas;
srvid coalesce_seq snapts aggts purgets errors
----- ------------ ------------------------------- ------------------------------ ------------------------------- -------------------------------------------------------------------------------------------
0 31672 29/04/2022 01:34:41.377167 CEST 27/04/2022 13:52:07.45721 CEST 27/04/2022 14:22:07.399071 CEST {"powa_take_snapshot(0): function \"powa_kcache_snapshot\" failed: column k.reads does not .
exist"}
0 31647 28/04/2022 11:44:07.073224 CEST 27/04/2022 13:52:07.45721 CEST 27/04/2022 14:22:07.399071 CEST #NULL#
(2 rows)
Time: 0.500 ms
[local] postgres@powa=# SELECT * FROM powa_snapshot_metas WHERE srvid = 0;
srvid coalesce_seq snapts aggts purgets errors
----- ------------ ------------------------------- ------------------------------ ------------------------------- -------------------------------------------------------------------------------------------
0 31672 29/04/2022 01:34:41.377167 CEST 27/04/2022 13:52:07.45721 CEST 27/04/2022 14:22:07.399071 CEST {"powa_take_snapshot(0): function \"powa_kcache_snapshot\" failed: column k.reads does not .
exist"}
0 31647 28/04/2022 11:44:07.073224 CEST 27/04/2022 13:52:07.45721 CEST 27/04/2022 14:22:07.399071 CEST #NULL#
(2 rows)
Time: 0.870 ms
How can I see the powa-archivist version?
Oh, it looks like there was some corruption on the server :(
How can I see the powa-archivist version?
You can simply use \dx to display all information about extensions. While at it, could you also show the output of \d powa_snapshot_metas to make sure that the primary key is as expected?
Hi.
[local] postgres@powa=# \dx
List of installed extensions
Name Version Schema Description
------------------ ------- ---------- ---------------------------------------------------------
btree_gist 1.5 public support for indexing common datatypes in GiST
hypopg 1.1.3 public Hypothetical indexes for PostgreSQL
pg_buffercache 1.3 public examine the shared buffer cache
pg_qualstats 2.0.2 public An extension collecting statistics about quals
pg_stat_kcache 2.2.0 public Kernel statistics gathering
pg_stat_statements 1.7 public track execution statistics of all SQL statements executed
pgstattuple 1.5 public show tuple-level statistics
plpgsql 1.0 pg_catalog PL/pgSQL procedural language
powa 4.0.1 public PostgreSQL Workload Analyser-core
(9 rows)
[local] postgres@powa=# \d powa_snapshot_metas
Table "public.powa_snapshot_metas"
Column Type Collation Nullable Default
------------ ------------------------ --------- -------- -------------------------------------
srvid integer not null
coalesce_seq bigint not null 1
snapts timestamp with time zone not null '-infinity'::timestamp with time zone
aggts timestamp with time zone not null '-infinity'::timestamp with time zone
purgets timestamp with time zone not null '-infinity'::timestamp with time zone
errors text[]
Indexes:
"powa_snapshot_metas_pkey" PRIMARY KEY, btree (srvid)
Foreign-key constraints:
"powa_snapshot_metas_srvid_fkey" FOREIGN KEY (srvid) REFERENCES powa_servers(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
Ok, so you do have a primary key declared on powa_snapshot_metas(srvid), and yet have 2 rows for the same srvid.
Did something happen on that server around the time that error started to appear? You should really try to root cause that issue as you might have other tables corrupted, possibly on other databases.
Other than that, the fix should be simple: simply drop the row with srvid = 0 that has the lowest coalesce_seq to make sure you won't have duplicates during the next snapshot(s) and you should be good to go.
Ok, I deleted the line with the lowest coalesce_seq, but I still get the error that the reads column does not exist:
[local] postgres@powa=# SELECT * FROM powa_snapshot_metas;
srvid coalesce_seq snapts aggts purgets errors
----- ------------ ---------------------------- -- ---------------------------- ---------------------- --------- ----------------------------------------- --------------------------------------------------
0 31651 10/09/2023 12:41:56.384099 CEST 04/27/2022 13:52:07.45721 CEST 04/27/2022 14:22:07.399071 CEST {"powa_take_snapshot(0): function \"powa_kcache_snapshot\" failed: column k.reads does not .
exists"}
(1 row)
Time: 1.196 ms
That's because the reads column was split in 2 (one for execution time and one for planning time) with pg_stat_kcache 2.2.0, but you still have an older version of powa-archivist.
There's a compatibility layer in powa-archivist to take care of it, but it can only work if powa-archivist is as least as recent as the most recent stat extension that requires a compatibility layer. Upgrading powa-archivist to the latest version should fix that problem.