public inbox for [email protected]
help / color / mirror / Atom feedDuplicate key value violates unique constraint "pg_type_typname_nsp_index" error
3+ messages / 3 participants
[nested] [flat]
* Duplicate key value violates unique constraint "pg_type_typname_nsp_index" error
@ 2026-02-19 01:47 Ed Sabol <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Ed Sabol @ 2026-02-19 01:47 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Using PostgreSQL 15.16, I have some SQL for creating a materialized view that's inside a larger transaction which randomly emits the following error:
ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(largetable, 20488) already exists.
The error doesn't happen 100% of the times I try to execute this transaction. Maybe 30% of the time? Also, I've had it happen on multiple separate PostgreSQL servers.
I'm not entirely sure how to interpret these ERROR and DETAIL messages. Can someone explain it?
I've tried the following queries to try to figure out what typnamespace = 20488 refers to:
SELECT nspname FROM pg_namespace WHERE oid = 20488;
SELECT count(*) FROM pg_catalog.pg_type WHERE typname = 'targetable' AND typnamespace = 20488;
Neither query provides any useful information. The latter always returns zero immediately immediately prior to the SQL error.
Here's the (slightly sanitized) SQL which causes the error:
CREATE MATERIALIZED VIEW someschema.largetable AS
(
SELECT
row_number() OVER () AS __row,
sub.*,
public.somefunc1(afield) AS s_result1,
public.somefunc2(anotherfield) AS s_result2,
public.somefunc3(somefield1, somefield2) AS s_result3
FROM (
SELECT * FROM someschema.largetable_upd_1
UNION ALL SELECT * FROM someschema.largetable_upd_2
UNION ALL SELECT * FROM someschema.largetable_upd_3
UNION ALL SELECT * FROM someschema.largetable_upd_4
UNION ALL SELECT * FROM someschema.largetable_upd_5
UNION ALL SELECT * FROM someschema.largetable_upd_6
UNION ALL SELECT * FROM someschema.largetable_upd_8
UNION ALL SELECT * FROM someschema.largetable_upd_9
UNION ALL SELECT * FROM someschema.largetable_upd_10
) sub
);
public.somefunc1, public.somefunc2, and public.somefunc3 return three types defined in PgSphere. I have the latest PgSphere installed.
Any ideas as to how to eliminate the error?
My first thought was to change the asterisks to explicit lists of columns.
Any assistance would be appreciated!
Thanks,
Ed
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Duplicate key value violates unique constraint "pg_type_typname_nsp_index" error
@ 2026-02-19 04:51 Tom Lane <[email protected]>
parent: Ed Sabol <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2026-02-19 04:51 UTC (permalink / raw)
To: Ed Sabol <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Ed Sabol <[email protected]> writes:
> Using PostgreSQL 15.16, I have some SQL for creating a materialized view that's inside a larger transaction which randomly emits the following error:
> ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
> DETAIL: Key (typname, typnamespace)=(largetable, 20488) already exists.
Huh. Does it always mention 20488?
> I've tried the following queries to try to figure out what typnamespace = 20488 refers to:
> SELECT nspname FROM pg_namespace WHERE oid = 20488;
> SELECT count(*) FROM pg_catalog.pg_type WHERE typname = 'targetable' AND typnamespace = 20488;
> Neither query provides any useful information.
What do you mean by "any useful information", exactly? Do these show
that there is no pg_namespace row with oid = 20488?
If that is the case, I'd bet on index corruption in the indexes of
pg_type and/or pg_namespace. REINDEX might help.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Duplicate key value violates unique constraint "pg_type_typname_nsp_index" error
@ 2026-02-19 13:02 Edward J. Sabol <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Edward J. Sabol @ 2026-02-19 13:02 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Feb 18, 2026, at 11:51 PM, Tom Lane wrote:
> Ed Sabol <[email protected]> writes:
>> Using PostgreSQL 15.16, I have some SQL for creating a materialized view that's inside a larger transaction which randomly emits the following error:
>> ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
>> DETAIL: Key (typname, typnamespace)=(largetable, 20488) already exists.
>
> Huh. Does it always mention 20488?
Yes, it's always 20488.
>> I've tried the following queries to try to figure out what typnamespace = 20488 refers to:
>
>> SELECT nspname FROM pg_namespace WHERE oid = 20488;
>> SELECT count(*) FROM pg_catalog.pg_type WHERE typname = 'targetable' AND typnamespace = 20488;
>
>> Neither query provides any useful information.
>
> What do you mean by "any useful information", exactly? Do these show
> that there is no pg_namespace row with oid = 20488?
Well, the first query returns a schema that is completely unrelated to anything the SQL is doing. I wondered if the typnamespace = 20488 DETAIL was referring to something else.
> If that is the case, I'd bet on index corruption in the indexes of
> pg_type and/or pg_namespace. REINDEX might help.
I'll try that. But why would it only happen sometimes?
All of the mentions of this error message I could find on the Internet suggest this happens when you have multiple processes trying to execute the same DDL simultaneously, but I'm sure there's only one process executing this transaction.
Thanks,
Ed
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-02-19 13:02 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-19 01:47 Duplicate key value violates unique constraint "pg_type_typname_nsp_index" error Ed Sabol <[email protected]>
2026-02-19 04:51 ` Tom Lane <[email protected]>
2026-02-19 13:02 ` Edward J. Sabol <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox