ERROR: duplicate key value violates unique constraint "powa_extension_config_pkey"

[jw1u1]

powa-collector 1.3.0-1.pgdg110+1

The hypopg version remains null, this should be an update or an insert on conflict update:

ERROR: duplicate key value violates unique constraint "powa_extension_config_pkey"
app=PoWA collector - repo_conn for worker commhistoryservice-pg-pro DETAIL: Key (srvid, extname)=(2, hypopg) already exists.
app=PoWA collector - repo_conn for worker commhistoryservice-pg-pro STATEMENT:
INSERT INTO public.powa_extension_config
(srvid, extname, version)
VALUES (2, 'hypopg', ('1.4.1'))

[rjuju]

Hi,

the code is already suppose to handle INSERT vs UPDATE. It looks like something specific happened in your case since this code has been there for years and this is the first report about an issue. Can you share details on how you configure things and what lead to that error?

[jw1u1]

The log shows an insert, not an insert or update.

[rjuju]

Yes, that's because powa-collector supports postgres down to 9.4 which isn't compatible with the ON CONFLICT clause.

What I meant is that it does check and issue either an insert or an update: https://github.com/powa-team/powa-collector/blob/master/powa_collector/powa_worker.py#L233-L247 so something is broken in that logic, and I'm trying to understand the scenario that leads to it.

[jw1u1]

This happend after registering 110 Servers at the same time.
We had a massive load peak due to missing indexes in the powa db and had to restart the collector.
We had to terminate some powa Backends in the surveiled DBs due to idle in transaction for > 1h.
No more details, sorry.

[rjuju]

I see. at least that confirms the source of your slow down

I will try to reproduce and otherwise distinguish the "null version" from the "no matching row for hypopg" that is probably the root issue here.

[jw1u1]

I guess this is from powa_worker.py line 237.

[jw1u1]

Duplicate Key Errors in powa_catalog_generic_snapshot at line 64:
WARNING: public.powa_take_snapshot(40): function public.powa_catalog_generic_snapshot for catalog pg_type failed:
state : 23505
message: duplicate key value violates unique constraint "powa_catalog_type_pkey"

[rjuju]

This one looks really suspicious, the function powa_catalog_generic_snapshot() removes any target row before inserting the new ones so by definition there can't be any duplicate.

[jw1u1]

Yeah, sorry, edited the comment just now.

[jw1u1]

The row doesn't exist:
delete from powa_catalog_type where srvid=40 and dbid=13881 and oid=16;
DELETE 0

[jw1u1]

Same for
public.powa_take_snapshot(75): function public.powa_catalog_generic_snapshot for catalog pg_namespace failed: duplicate key value violates unique constraint "powa_catalog_namespace_pkey"

[rjuju]

then it means it was present twice in the associated src table. can you check if that's the case?

if yes there are two possible explanations for that:

• a bug in the collector when retrieving the data. while possible it ses unlikely to have a bug leading to sometimes duplicating rows. or just put the wrong database id or something
• a corruption on the source server. it also seems unlikely but that should be easy to check

[jw1u1]

Sorry,
i have deleted and re-created the server and the errors went away.
Source Tables are currently empty.

[rjuju]

no worries. you could still check if the problematic oids are present twice on the remote database(s). you may need to check forcing a sequential scan.