Feedback-ID: i76984098:Fastmail
Message-ID: <08c96195-3d5f-4902-b5d7-15916a02e23f@aklaver.com>
Date: Wed, 12 Mar 2025 09:01:10 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Duplicate Key Values
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: <CH3PR22MB4312C23476C0E67F9B4C2E10BACB2@CH3PR22MB4312.namprd22.prod.outlook.com>
 <9d919848-3fe1-46fd-b343-c5f2931864f7@aklaver.com>
 <SJ2PR22MB43282F7C60DD81CA90502FA0BAD52@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <513ddbee-135c-4af2-b6f6-acca667b8d09@aklaver.com>
 <SJ2PR22MB4328C3D2704FB8F6CD079924BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <d9c4c12e-fcf9-44a6-becc-f7c852f62ed4@aklaver.com>
 <SJ2PR22MB4328267BD9F601D90601715BBAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <c6ca9286-a0ea-48bb-adb0-2d4e1835ebee@aklaver.com>
 <SJ2PR22MB4328A3EDC83E495DB9A4BD09BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <84aac7bf-5638-4f1f-9772-ace0ea27e848@aklaver.com>
 <SJ2PR22MB4328CEB1B47FC1AC4A996CB3BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <9203d0df-0ddd-4564-a985-59bcfcf1d294@aklaver.com>
 <SJ2PR22MB432802BD55A9AEFCF212F4A7BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <d51484c7-6659-4552-b999-cc220a492f8e@aklaver.com>
 <d21319fa-169d-46d9-81c1-7e22bafe2a2b@aklaver.com>
Content-Language: en-US
In-Reply-To: <d21319fa-169d-46d9-81c1-7e22bafe2a2b@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/08c96195-3d5f-4902-b5d7-15916a02e23f%40aklaver.com>
Precedence: bulk

On 3/12/25 08:46, Adrian Klaver wrote:
> On 3/11/25 13:24, Adrian Klaver wrote:
>> On 3/11/25 12:55, mark bradley wrote:
>>> It happened again.  Now there are no sequences (although there once 
>>> was).
>>
>> Read my previous post and provide the information requested.
>>
> 
> Mark sent me the below, which answers some of the questions, namely 
> there is inheritance going on:

Mark, to illustrate:

create table node (node_id integer primary key, fld1 varchar);
create table node_1 (node_id integer primary key, node_1_fld boolean) 
inherits ( node);
NOTICE:  merging column "node_id" with inherited definition

insert into node values (1, 'dog');
insert into node_1 values (1, 'cat', 'f');

select * from node;
  node_id | fld1
---------+------
        1 | dog
        1 | cat


This is explained here:

https://www.postgresql.org/docs/current/sql-createtable.html

INHERITS ( parent_table [, ... ] )

"... , and by default the data of the child table is included in scans 
of the parent(s)."

This explains why you see duplicates of node_id.

Though if you try to enter a duplicate value in to a particular table 
you get:

insert into node_1 values (1, 'test', 't');
ERROR:  duplicate key value violates unique constraint "node_1_pkey"
DETAIL:  Key (node_id)=(1) already exists.

This still does not explain why REINDEX TABLE node; caused data to 
disappear?

>  > Did you ever run VALIDATE CONSTRAINT against them?
> Here is the run

As error notes VALIDATE CONSTRAINT only works on FK and check 
constraints. You would need to run against the FK constraints that where 
marked NOT VALID e.g "dataset" on the dataset table.


Honestly, I think you need rework your data model. Not sure what the 
inheritance is getting you. Seems simpler to just have the node table 
not be inherited and just use FK relationships back to it.

> 
> Universal Metadata Schema=# ALTER TABLE node VALID
> ATE CONSTRAINT node_id;
> ERROR: constraint "node_id" of relation "node" is
> not a foreign key or check constraint
> Universal Metadata Schema=#
> 
> Universal Metadata Schema=# ALTER TABLE dataset VA
> LIDATE CONSTRAINT node_id;
> ALTER TABLE
> Universal Metadata Schema=#
> 
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com