Feedback-ID: i76984098:Fastmail
Message-ID: <d51484c7-6659-4552-b999-cc220a492f8e@aklaver.com>
Date: Tue, 11 Mar 2025 13:24:37 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Duplicate Key Values
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: <CH3PR22MB4312C23476C0E67F9B4C2E10BACB2@CH3PR22MB4312.namprd22.prod.outlook.com>
 <SJ2PR22MB43286857EAC74D1EE332B08CBAD52@SJ2PR22MB4328.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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <SJ2PR22MB432802BD55A9AEFCF212F4A7BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/d51484c7-6659-4552-b999-cc220a492f8e%40aklaver.com>
Precedence: bulk

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.

Also:

1) Postgres version.

2) Where did you get Postgres from?

> 
> Sequence of data reentry:
> 
>  1.
>     I reentered the /node_id/s in table node as a primary key.
>  2.
>     I reentered the datasets, in table /dataset/.
>  3.
>     /Node_id/ was already a PK in dataset.
>  4.
>     _I set _/_node_id_/_ to also be a foreign key in _/_dataset_/_._
>  5.
>     I went back to look at /node/ and see that duplicate key values
>     appeared.
> 
> 
> 
> 
> Processing nodes are unaffected because I didn't add any data to the 
> /processing_node/ table.
> 
> Next, I'm going to create a simple database from scratch and see if I 
> can duplicate this behavior.
> 
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Tuesday, March 11, 2025 3:37 PM
> *To:* mark bradley <markbradyju@outlook.com>
> *Cc:* pgsql-general <pgsql-general@postgresql.org>
> *Subject:* Re: Duplicate Key Values
> On 3/11/25 11:52, mark bradley wrote:
>> 
>> 
>>     there is an index on node_id as it is the Primary Key.
>> 
>>     Why do you think there is not?
>> 
>> My mistake, I misread the output from \d dataset
>> 
>> 
>> 
>> Can you elaborate more on point 3.
>> 
>> Are you calling the Foreign Key relationships subclassing?
>> 
>> 
>> Although I did not explicitly use Postgres to declare inheritance, 
>> logically speaking table /dataset/ and /processing _node/ inherit or are 
>> subclasses of /node/ because they are subclasses of /node/ in a dataflow 
>> diagram.
>> 
>> 
>> In terms of keys, this is accomplished by having the /node_id/ key in 
>> the /node/ table appear as a foreign key and as a primary key in both 
>> the /dataset/ and /processing_node/ tables.
> 
> You will need to show the schema definitions for:
> 
>     node
>     dataset
>     processing_node
> 
> Best to do using psql \d <table_name>
> 
> Also in from previous \d dataset there where NOT VALID FK definitions.
> 
> Did you ever run VALIDATE CONSTRAINT against them?
> 
>> 
>> 
>> 
>>     Is there anything in Postgres log at the time you did the above that
>>     showed it did more then a REINDEX?
>> 
>> 
>> Not that I can tell.
>> 
>> 
>> 
>> 
>> Best regards,
>> Mark Brady
>> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady 
> <https://amazon.com/author/markjbrady>>_
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com