It happened again. Now there are no sequences (although there once was). Sequence of data reentry:
1. I reentered the node_ids 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. [cid:c13bcb32-fda3-41d6-9b43-688f6e1cdad8] [cid:2382fdb0-9b71-44f6-848f-69aae7ab8444] 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.kla...@aklaver.com> Sent: Tuesday, March 11, 2025 3:37 PM To: mark bradley <markbrad...@outlook.com> Cc: pgsql-general <pgsql-gene...@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>_ -- Adrian Klaver adrian.kla...@aklaver.com
