From: | mark bradley <markbradyju(at)outlook(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Duplicate Key Values |
Date: | 2025-03-11 19:55:59 |
Message-ID: | SJ2PR22MB432802BD55A9AEFCF212F4A7BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com>
Sent: Tuesday, March 11, 2025 3:37 PM
To: mark bradley <markbradyju(at)outlook(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2025-03-11 20:24:37 | Re: Duplicate Key Values |
Previous Message | Adrian Klaver | 2025-03-11 19:37:41 | Re: Duplicate Key Values |