public inbox for [email protected]  

help / color / mirror / Atom feed
From: mark bradley <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
Date: Tue, 11 Mar 2025 19:55:59 +0000
Message-ID: <SJ2PR22MB432802BD55A9AEFCF212F4A7BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <CH3PR22MB4312C23476C0E67F9B4C2E10BACB2@CH3PR22MB4312.namprd22.prod.outlook.com>
	<SJ2PR22MB4328931B7CB951A48F1434ABBACA2@SJ2PR22MB4328.namprd22.prod.outlook.com>
	<[email protected]>
	<SJ2PR22MB43286857EAC74D1EE332B08CBAD52@SJ2PR22MB4328.namprd22.prod.outlook.com>
	<[email protected]>
	<SJ2PR22MB43282F7C60DD81CA90502FA0BAD52@SJ2PR22MB4328.namprd22.prod.outlook.com>
	<[email protected]>
	<SJ2PR22MB4328C3D2704FB8F6CD079924BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
	<[email protected]>
	<SJ2PR22MB4328267BD9F601D90601715BBAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
	<[email protected]>
	<SJ2PR22MB4328A3EDC83E495DB9A4BD09BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
	<[email protected]>
	<SJ2PR22MB4328CEB1B47FC1AC4A996CB3BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
	<[email protected]>

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 <[email protected]>
Sent: Tuesday, March 11, 2025 3:37 PM
To: mark bradley <[email protected]>
Cc: pgsql-general <[email protected]>
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
[email protected]



Attachments:

  [image/png] image.png (41.5K, 3-image.png)
  download | view image

  [image/png] image.png (41.4K, 4-image.png)
  download | view image

view thread (25+ messages)  latest in thread

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Duplicate Key Values
  In-Reply-To: <SJ2PR22MB432802BD55A9AEFCF212F4A7BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox