public inbox for [email protected]
help / color / mirror / Atom feedRe: Duplicate Key Values
25+ messages / 4 participants
[nested] [flat]
* Re: Duplicate Key Values
@ 2025-03-11 14:28 mark bradley <[email protected]>
2025-03-11 14:33 ` Re: Duplicate Key Values Greg Sabino Mullane <[email protected]>
2025-03-11 14:40 ` Re: Duplicate Key Values Ron Johnson <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
0 siblings, 3 replies; 25+ messages in thread
From: mark bradley @ 2025-03-11 14:28 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general
An "interesting" effect of reindexing is that all the records that were dups in the nodes table were deleted, both copies.
Also, all rows having node_id as a foreign key in other tables were deleted, which means all rows in these tables were deleted.
Fortunately these are not huge tables. I will reenter the data, make a backup, and then try your further extended suggestions.
Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady;
________________________________
From: Adrian Klaver <[email protected]>
Sent: Friday, March 7, 2025 3:25 PM
To: mark bradley <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
On 3/7/25 11:47, mark bradley wrote:
> Wouldn't that be nice 🙂
No, because you would end up with a mess.
My AI rant:
AI is neither artificial or intelligent. It is human code that pattern
matches and then throws the matches against the wall and hope something
sticks. It is left to the human to clean up.
At any rate, in this post:
https://www.postgresql.org/message-id/75b33741-ee99-4524-b63a-edad21c1266d%40aklaver.com
You where provided an answer, which is further extended here:
https://www.postgresql.org/message-id/CAKAnmm%2BBBBaXGN2xPHhXywkwb72UWzinWu2wQ5WadcMw3_57rQ%40mail.g...
>
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <[email protected]>
> *Sent:* Friday, March 7, 2025 10:55 AM
> *To:* mark bradley <[email protected]>
> *Cc:* pgsql-general <[email protected]>
> *Subject:* Re: Duplicate Key Values
> On 3/7/25 06:34, mark bradley wrote:
>> This is what MS Copilot has to say about this apparent bug where
>> Postgres inserts extra rows violating a primary keys uniqueness constraint:
>
>
>
>> What to do? I hesitate to just delete my tables and start over because
>> this error will reoccur.
>
> Let the AI solve it.
>
>>
>> Best regards,
>> Mark Brady
>
>
> --
> Adrian Klaver
> [email protected]
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-11 14:33 ` Greg Sabino Mullane <[email protected]>
2 siblings, 0 replies; 25+ messages in thread
From: Greg Sabino Mullane @ 2025-03-11 14:33 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On Tue, Mar 11, 2025 at 10:29 AM mark bradley <[email protected]>
wrote:
> An "interesting" effect of reindexing is that all the records that were
> dups in the nodes table were deleted, both copies.
>
Er...that's not just interesting, but alarming - if true. Can you show the
steps you took?
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-11 14:40 ` Ron Johnson <[email protected]>
2 siblings, 0 replies; 25+ messages in thread
From: Ron Johnson @ 2025-03-11 14:40 UTC (permalink / raw)
To: pgsql-general
Do you *currently* take regular backups?
We'd be glad to show you how to take regular logical backups.
On Tue, Mar 11, 2025 at 10:29 AM mark bradley <[email protected]>
wrote:
> An "interesting" effect of reindexing is that all the records that were
> dups in the nodes table were deleted, both copies.
>
> Also, all rows having node_id as a foreign key in other tables were
> deleted, which means all rows in these tables were deleted.
>
> Fortunately these are not huge tables. I will reenter the data, make a
> backup, and then try your further extended suggestions.
>
> Best regards,
> Mark Brady
> *amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>*
> ------------------------------
> *From:* Adrian Klaver <[email protected]>
> *Sent:* Friday, March 7, 2025 3:25 PM
> *To:* mark bradley <[email protected]>
> *Cc:* pgsql-general <[email protected]>
> *Subject:* Re: Duplicate Key Values
>
> On 3/7/25 11:47, mark bradley wrote:
> > Wouldn't that be nice 🙂
>
> No, because you would end up with a mess.
>
> My AI rant:
>
> AI is neither artificial or intelligent. It is human code that pattern
> matches and then throws the matches against the wall and hope something
> sticks. It is left to the human to clean up.
>
> At any rate, in this post:
>
>
> https://www.postgresql.org/message-id/75b33741-ee99-4524-b63a-edad21c1266d%40aklaver.com
>
> You where provided an answer, which is further extended here:
>
>
> https://www.postgresql.org/message-id/CAKAnmm%2BBBBaXGN2xPHhXywkwb72UWzinWu2wQ5WadcMw3_57rQ%40mail.g...
>
> >
> >
> > Best regards,
> > Mark Brady
> > _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> > ------------------------------------------------------------------------
> > *From:* Adrian Klaver <[email protected]>
> > *Sent:* Friday, March 7, 2025 10:55 AM
> > *To:* mark bradley <[email protected]>
> > *Cc:* pgsql-general <[email protected]>
> > *Subject:* Re: Duplicate Key Values
> > On 3/7/25 06:34, mark bradley wrote:
> >> This is what MS Copilot has to say about this apparent bug where
> >> Postgres inserts extra rows violating a primary keys uniqueness
> constraint:
> >
> >
> >
> >> What to do? I hesitate to just delete my tables and start over because
> >> this error will reoccur.
> >
> > Let the AI solve it.
> >
> >>
> >> Best regards,
> >> Mark Brady
> >
> >
> > --
> > Adrian Klaver
> > [email protected]
> >
>
> --
> Adrian Klaver
> [email protected]
>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-11 14:56 ` Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2 siblings, 1 reply; 25+ messages in thread
From: Adrian Klaver @ 2025-03-11 14:56 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: pgsql-general
On 3/11/25 07:28, mark bradley wrote:
> An "interesting" effect of reindexing is that all the records that were
> dups in the nodes table were deleted, both copies.
I am trying to understand above.
Was there at least one row of each node_id left?
>
> Also, all rows having node_id as a foreign key in other tables were
> deleted, which means all rows in these tables were deleted.
>
> Fortunately these are not huge tables. I will reenter the data, make a
> backup, and then try your further extended suggestions.
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-11 15:05 ` mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: mark bradley @ 2025-03-11 15:05 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general
The rows that were preserved in the nodes table were the ones that were not dups originally.
Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady;
________________________________
From: Adrian Klaver <[email protected]>
Sent: Tuesday, March 11, 2025 10:56 AM
To: mark bradley <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
On 3/11/25 07:28, mark bradley wrote:
> An "interesting" effect of reindexing is that all the records that were
> dups in the nodes table were deleted, both copies.
I am trying to understand above.
Was there at least one row of each node_id left?
>
> Also, all rows having node_id as a foreign key in other tables were
> deleted, which means all rows in these tables were deleted.
>
> Fortunately these are not huge tables. I will reenter the data, make a
> backup, and then try your further extended suggestions.
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-11 15:12 ` Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Adrian Klaver @ 2025-03-11 15:12 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: pgsql-general
On 3/11/25 08:05, mark bradley wrote:
> The rows that were preserved in the nodes table were the ones that were
> not dups originally.
1) To be specific:
a) If there where two or more rows with a node_id, after the reindexing
was there only one left?
b) Or for any node_ids that where duplicated did reindexing eliminate
all rows with that node_id.
2) Per post from Greg Sabino Mullane, you need to show us the steps you
took to reindex the table.
>
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <[email protected]>
> *Sent:* Tuesday, March 11, 2025 10:56 AM
> *To:* mark bradley <[email protected]>
> *Cc:* pgsql-general <[email protected]>
> *Subject:* Re: Duplicate Key Values
> On 3/11/25 07:28, mark bradley wrote:
>> An "interesting" effect of reindexing is that all the records that were
>> dups in the nodes table were deleted, both copies.
>
> I am trying to understand above.
>
> Was there at least one row of each node_id left?
>
>>
>> Also, all rows having node_id as a foreign key in other tables were
>> deleted, which means all rows in these tables were deleted.
>>
>> Fortunately these are not huge tables. I will reenter the data, make a
>> backup, and then try your further extended suggestions.
>>
>> Best regards,
>> Mark Brady
>> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady
> <https://amazon.com/author/markjbrady>>_
>
>
> --
> Adrian Klaver
> [email protected]
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-11 15:16 ` mark bradley <[email protected]>
2025-03-11 15:52 ` Re: Duplicate Key Values Greg Sabino Mullane <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
0 siblings, 2 replies; 25+ messages in thread
From: mark bradley @ 2025-03-11 15:16 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general
b
REINDEX TABLE node;
Also reindexed table with node_id as a foreign key in the same way.
Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady;
________________________________
From: Adrian Klaver <[email protected]>
Sent: Tuesday, March 11, 2025 11:12 AM
To: mark bradley <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
On 3/11/25 08:05, mark bradley wrote:
> The rows that were preserved in the nodes table were the ones that were
> not dups originally.
1) To be specific:
a) If there where two or more rows with a node_id, after the reindexing
was there only one left?
b) Or for any node_ids that where duplicated did reindexing eliminate
all rows with that node_id.
2) Per post from Greg Sabino Mullane, you need to show us the steps you
took to reindex the table.
>
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <[email protected]>
> *Sent:* Tuesday, March 11, 2025 10:56 AM
> *To:* mark bradley <[email protected]>
> *Cc:* pgsql-general <[email protected]>
> *Subject:* Re: Duplicate Key Values
> On 3/11/25 07:28, mark bradley wrote:
>> An "interesting" effect of reindexing is that all the records that were
>> dups in the nodes table were deleted, both copies.
>
> I am trying to understand above.
>
> Was there at least one row of each node_id left?
>
>>
>> Also, all rows having node_id as a foreign key in other tables were
>> deleted, which means all rows in these tables were deleted.
>>
>> Fortunately these are not huge tables. I will reenter the data, make a
>> backup, and then try your further extended suggestions.
>>
>> Best regards,
>> Mark Brady
>> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady
> <https://amazon.com/author/markjbrady>>_
>
>
> --
> Adrian Klaver
> [email protected]
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-11 15:52 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 25+ messages in thread
From: Greg Sabino Mullane @ 2025-03-11 15:52 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
A reindex is not going to remove rows from the table, so we need to see how
you came to the conclusion that it did.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-11 16:00 ` Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
1 sibling, 1 reply; 25+ messages in thread
From: Adrian Klaver @ 2025-03-11 16:00 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: pgsql-general
On 3/11/25 08:16, mark bradley wrote:
A)
1) Please do not top post. Either inline or bottom post. It makes
things like the below easier to follow. I wrote the post you responded
to and it took me a bit to catch the b as a reference to:
> b
>
b) Or for any node_ids that where duplicated did reindexing eliminate
all rows with that node_id.
B)
From a previous post of yours:
"
1.
Originally, the key in the node table was a sequence, but I changed it
to a non-sequence.
2.
There is no index on the primary key node_id, and I understand there
should be one.
3.
I didn't explicitly use Postgres inheritance but there are two tables
that are subclasses of node. There are dataset nodes and
processing_node [s] tables. Each is a type of node and have primary
keys that are foreign keys from the node table. This key is node_id.
"
As to point 2, from this message:
https://www.postgresql.org/message-id/75b33741-ee99-4524-b63a-edad21c1266d%40aklaver.com
there is an index on node_id as it is the Primary Key.
Why do you think there is not?
Can you elaborate more on point 3.
Are you calling the Foreign Key relationships subclassing?
C)
> REINDEX TABLE node;
>
> Also reindexed table with node_id as a foreign key in the same way.
Is there anything in Postgres log at the time you did the above that
showed it did more then a REINDEX?
>
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <[email protected]>
> *Sent:* Tuesday, March 11, 2025 11:12 AM
> *To:* mark bradley <[email protected]>
> *Cc:* pgsql-general <[email protected]>
> *Subject:* Re: Duplicate Key Values
> On 3/11/25 08:05, mark bradley wrote:
>> The rows that were preserved in the nodes table were the ones that were
>> not dups originally.
>
> 1) To be specific:
>
> a) If there where two or more rows with a node_id, after the reindexing
> was there only one left?
>
> b) Or for any node_ids that where duplicated did reindexing eliminate
> all rows with that node_id.
>
>
> 2) Per post from Greg Sabino Mullane, you need to show us the steps you
> took to reindex the table.
>
>>
>>
>> Best regards,
>> Mark Brady
>> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady
> <https://amazon.com/author/markjbrady>>_
>> ------------------------------------------------------------------------
>> *From:* Adrian Klaver <[email protected]>
>> *Sent:* Tuesday, March 11, 2025 10:56 AM
>> *To:* mark bradley <[email protected]>
>> *Cc:* pgsql-general <[email protected]>
>> *Subject:* Re: Duplicate Key Values
>> On 3/11/25 07:28, mark bradley wrote:
>>> An "interesting" effect of reindexing is that all the records that were
>>> dups in the nodes table were deleted, both copies.
>>
>> I am trying to understand above.
>>
>> Was there at least one row of each node_id left?
>>
>>>
>>> Also, all rows having node_id as a foreign key in other tables were
>>> deleted, which means all rows in these tables were deleted.
>>>
>>> Fortunately these are not huge tables. I will reenter the data, make a
>>> backup, and then try your further extended suggestions.
>>>
>>> Best regards,
>>> Mark Brady
>>> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady
>> <https://amazon.com/author/markjbrady
> <https://amazon.com/author/markjbrady>>>_
>>
>>
>> --
>> Adrian Klaver
>> [email protected]
>>
>
> --
> Adrian Klaver
> [email protected]
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-11 18:52 ` mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: mark bradley @ 2025-03-11 18:52 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general
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.
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;
________________________________
From: Adrian Klaver <[email protected]>
Sent: Tuesday, March 11, 2025 12:00 PM
To: mark bradley <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
On 3/11/25 08:16, mark bradley wrote:
A)
1) Please do not top post. Either inline or bottom post. It makes
things like the below easier to follow. I wrote the post you responded
to and it took me a bit to catch the b as a reference to:
> b
>
b) Or for any node_ids that where duplicated did reindexing eliminate
all rows with that node_id.
B)
From a previous post of yours:
"
1.
Originally, the key in the node table was a sequence, but I changed it
to a non-sequence.
2.
There is no index on the primary key node_id, and I understand there
should be one.
3.
I didn't explicitly use Postgres inheritance but there are two tables
that are subclasses of node. There are dataset nodes and
processing_node [s] tables. Each is a type of node and have primary
keys that are foreign keys from the node table. This key is node_id.
"
As to point 2, from this message:
https://www.postgresql.org/message-id/75b33741-ee99-4524-b63a-edad21c1266d%40aklaver.com
there is an index on node_id as it is the Primary Key.
Why do you think there is not?
Can you elaborate more on point 3.
Are you calling the Foreign Key relationships subclassing?
C)
> REINDEX TABLE node;
>
> Also reindexed table with node_id as a foreign key in the same way.
Is there anything in Postgres log at the time you did the above that
showed it did more then a REINDEX?
>
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <[email protected]>
> *Sent:* Tuesday, March 11, 2025 11:12 AM
> *To:* mark bradley <[email protected]>
> *Cc:* pgsql-general <[email protected]>
> *Subject:* Re: Duplicate Key Values
> On 3/11/25 08:05, mark bradley wrote:
>> The rows that were preserved in the nodes table were the ones that were
>> not dups originally.
>
> 1) To be specific:
>
> a) If there where two or more rows with a node_id, after the reindexing
> was there only one left?
>
> b) Or for any node_ids that where duplicated did reindexing eliminate
> all rows with that node_id.
>
>
> 2) Per post from Greg Sabino Mullane, you need to show us the steps you
> took to reindex the table.
>
>>
>>
>> Best regards,
>> Mark Brady
>> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady
> <https://amazon.com/author/markjbrady>>_
>> ------------------------------------------------------------------------
>> *From:* Adrian Klaver <[email protected]>
>> *Sent:* Tuesday, March 11, 2025 10:56 AM
>> *To:* mark bradley <[email protected]>
>> *Cc:* pgsql-general <[email protected]>
>> *Subject:* Re: Duplicate Key Values
>> On 3/11/25 07:28, mark bradley wrote:
>>> An "interesting" effect of reindexing is that all the records that were
>>> dups in the nodes table were deleted, both copies.
>>
>> I am trying to understand above.
>>
>> Was there at least one row of each node_id left?
>>
>>>
>>> Also, all rows having node_id as a foreign key in other tables were
>>> deleted, which means all rows in these tables were deleted.
>>>
>>> Fortunately these are not huge tables. I will reenter the data, make a
>>> backup, and then try your further extended suggestions.
>>>
>>> Best regards,
>>> Mark Brady
>>> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady
>> <https://amazon.com/author/markjbrady
> <https://amazon.com/author/markjbrady>>>_
>>
>>
>> --
>> Adrian Klaver
>> [email protected]
>>
>
> --
> Adrian Klaver
> [email protected]
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-11 19:37 ` Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Adrian Klaver @ 2025-03-11 19:37 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: pgsql-general
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]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-11 19:55 ` mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: mark bradley @ 2025-03-11 19:55 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: 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 <[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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-11 20:24 ` Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Adrian Klaver @ 2025-03-11 20:24 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: pgsql-general
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 <[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
> <https://amazon.com/author/markjbrady>>_
>
>
> --
> Adrian Klaver
> [email protected]
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-12 15:46 ` Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Adrian Klaver @ 2025-03-12 15:46 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: pgsql-general
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:
Universal Metadata Schema=# \d node
Table "public.node"
Column | Type | Collation | Nullable | Default
-----------+-----------+-----------+----------+---------
node_id | integer | | not null |
node_type | node_type | | not null |
Indexes:
"node_pkey" PRIMARY KEY, btree (node_id)
"node_id" UNIQUE CONSTRAINT, btree (node_id) INCLUDE (node_id)
Referenced by:
TABLE "user_role" CONSTRAINT "a" FOREIGN KEY (node_id) REFERENCES
node(node_i
d) NOT VALID
TABLE "dataset" CONSTRAINT "node_id" FOREIGN KEY (node_id) REFERENCES
node(no
de_id) NOT VALID
Number of child tables: 2 (Use \d+ to list them.)
Universal Metadata Schema=# \d dataset
Table "public.dataset"
Column | Type | Collation | Nullable | Default
---------------------------+---------------------------+-----------+----------+--
-------
node_id | integer | | not null |
dataset_name | character varying(25) | | not null |
notes | text | | |
dataset_type | database_type | | |
dataset_maturity | database_maturity_type | | |
disposition | disposition_type | | |
start_date | date | | |
end_date | date | | |
most_recent_update | date | | |
update_periodicity | interval | | |
system_of_record | text | | |
point_of_contact | integer | | |
dataset_url | text | | |
classification_level | classification_level_type | | |
physical_location | text | | |
quality_control | yes_no_type | | |
dataset_documentation_url | text | | |
description | text | | |
node_type | node_type | | |
Indexes:
"dataset_pkey" PRIMARY KEY, btree (node_id)
Foreign-key constraints:
"node_id" FOREIGN KEY (node_id) REFERENCES node(node_id) NOT VALID
"poc" FOREIGN KEY (point_of_contact) REFERENCES poc(poc_id) NOT VALID
Referenced by:
TABLE "dataset_table" CONSTRAINT "dataset" FOREIGN KEY (node_id)
REFERENCES d
ataset(node_id) NOT VALID
TABLE "system_dataset" CONSTRAINT "system_dataset_node_id_fkey" FOREIGN
KEY (
node_id) REFERENCES dataset(node_id) NOT VALID
Inherits: node
Universal Metadata Schema=# \d processing_node
Table "public.processing_node"
Column | Type | Collation | Nullable | Default
-----------------------+-----------------------+-----------+----------+---------
node_id | integer | | not null |
processing_node_name | character varying(25) | | |
description | text | | |
notes | text | | |
point_of_contact | integer | | not null |
is_a_user_application | yes_no_type | | not null |
node_type | node_type | | |
Indexes:
"processing_node_pkey" PRIMARY KEY, btree (node_id)
Foreign-key constraints:
"processing_node_point_of_contact_fkey" FOREIGN KEY (point_of_contact)
REFERE
NCES poc(poc_id)
Referenced by:
TABLE "system_processing_node" CONSTRAINT
"system_processing_node_processing_
node_id_fkey" FOREIGN KEY (processing_node_id) REFERENCES
processing_node(node_id
) NOT VALID
Inherits: node
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=# ALTER TABLE dataset VA
LIDATE CONSTRAINTnode_id;
ERROR: syntax error at or near "CONSTRAINTnode_id
"
LINE 1: ALTER TABLE dataset VALIDATE CONSTRAINTnod
e_id;
^
> Did you ever run VALIDATE CONSTRAINT against them?
Here is the run
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
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-12 16:01 ` Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Adrian Klaver @ 2025-03-12 16:01 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-13 14:56 ` mark bradley <[email protected]>
2025-03-13 15:05 ` Re: Duplicate Key Values Ron Johnson <[email protected]>
2025-03-13 15:23 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 15:29 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
0 siblings, 3 replies; 25+ messages in thread
From: mark bradley @ 2025-03-13 14:56 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general
>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
That would make sense except that I never explicitly use the inherits option in the node_1 (my dataset) table. Postgres seems to be assuming that.
Also, the second column in node and in dataset are two different columns. However, Postgres insists on the node_type attribute being included (last column) in table dataset and won't let me delete it. This is redundant because every dataset is a dataset type of node.
[cid:3cfa9757-23cc-4afe-bde7-55483afc91c4]
[cid:c7421587-6949-4198-9d6e-62f2bf0827d1]
...
[cid:0de27593-d4a0-43a3-ab4b-8f37b4a8b3a9]
The reason for the current design is that I also have a dataflow table as shown below.
A dataflow record describes the flow of data between two nodes. Now, if there are 3 types of node: dataset, processing, and user, then there are 9 types of dataflow. Hence, 9 tables are needed to represent the dataflows instead of 1. In the below, source_id and destination_id are both node_ids. If I want to know if a node is a dataset, processing node, or a user, I just look that up in the nodes table.
[cid:8be13982-7a2b-47e7-bf4e-555ebea1f8f2]
So, I think the crux of the problem is that Postgres assumes that inheritance is declared when it is not.
More answers to your questions coming.
Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady;
________________________________
From: Adrian Klaver <[email protected]>
Sent: Wednesday, March 12, 2025 12:01 PM
To: mark bradley <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
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
[email protected]
Attachments:
[image/png] image.png (106.6K, 3-image.png)
download | view image
[image/png] image.png (39.4K, 4-image.png)
download | view image
[image/png] image.png (71.7K, 5-image.png)
download | view image
[image/png] image.png (24.8K, 6-image.png)
download | view image
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-13 15:05 ` Ron Johnson <[email protected]>
2025-03-13 15:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2 siblings, 1 reply; 25+ messages in thread
From: Ron Johnson @ 2025-03-13 15:05 UTC (permalink / raw)
To: pgsql-general
Postgresql does not assume / default to inheritance. In text-mode clients
where you type in "raw" SQL, you have to explicitly add an explicit
"INHERITS <parent_table>" clause to the "CREATE TABLE foo" statement.
Are you creating the tables via PgAdmin point-and-click?
On Thu, Mar 13, 2025 at 10:56 AM mark bradley <[email protected]>
wrote:
> >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
>
> That would make sense except that I never explicitly use the *inherits* option
> in the *node_1* (my *dataset*) table. Postgres seems to be assuming
> that.
>
> Also, the second column in *node* and in *dataset* are two different
> columns. However, Postgres insists on the *node_type* attribute being
> included (last column) in table *dataset *and won't let me delete it. This
> is redundant because every dataset is a dataset type of node.
>
> ...
> The reason for the current design is that I also have a *dataflow* table
> as shown below.
>
> A dataflow record describes the flow of data between two nodes. Now, if
> there are 3 types of node: dataset, processing, and user, then there are 9
> types of dataflow. Hence, 9 tables are needed to represent the dataflows
> instead of 1. In the below, *source_id* and *destination_id* are both
> *node_id*s. If I want to know if a node is a dataset, processing node,
> or a user, I just look that up in the nodes table.
>
>
> So, I think the crux of the problem is that Postgres assumes that
> inheritance is declared when it is not.
>
> More answers to your questions coming.
>
> Best regards,
> Mark Brady
> *amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>*
>
> ------------------------------
> *From:* Adrian Klaver <[email protected]>
> *Sent:* Wednesday, March 12, 2025 12:01 PM
> *To:* mark bradley <[email protected]>
> *Cc:* pgsql-general <[email protected]>
> *Subject:* Re: Duplicate Key Values
>
> 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
> [email protected]
>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Attachments:
[image/png] image.png (106.6K, 3-image.png)
download | view image
[image/png] image.png (39.4K, 4-image.png)
download | view image
[image/png] image.png (71.7K, 5-image.png)
download | view image
[image/png] image.png (24.8K, 6-image.png)
download | view image
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 15:05 ` Re: Duplicate Key Values Ron Johnson <[email protected]>
@ 2025-03-13 15:56 ` mark bradley <[email protected]>
2025-03-13 16:05 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: mark bradley @ 2025-03-13 15:56 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; pgsql-general
>Postgresql does not assume / default to inheritance. In text-mode clients where you type >in "raw" SQL, you have to explicitly add an explicit "INHERITS <parent_table>" clause to the >"CREATE TABLE foo" statement.
>Are you creating the tables via PgAdmin point-and-click?
I am using PgAdmin 4 v9.1.
I think the problem may also be related to the fact that I had node_id and node_type were in both tables from an earlier design and Postgres would not let me delete node_type from the dataset table.
As an experiment, I created a simple version of the same tables from scratch without node_type in the dataset table. So far, no dups are appearing.
Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady;
________________________________
From: Ron Johnson <[email protected]>
Sent: Thursday, March 13, 2025 11:05 AM
To: pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
Postgresql does not assume / default to inheritance. In text-mode clients where you type in "raw" SQL, you have to explicitly add an explicit "INHERITS <parent_table>" clause to the "CREATE TABLE foo" statement.
Are you creating the tables via PgAdmin point-and-click?
On Thu, Mar 13, 2025 at 10:56 AM mark bradley <[email protected]<mailto:[email protected]>> wrote:
>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
That would make sense except that I never explicitly use the inherits option in the node_1 (my dataset) table. Postgres seems to be assuming that.
Also, the second column in node and in dataset are two different columns. However, Postgres insists on the node_type attribute being included (last column) in table dataset and won't let me delete it. This is redundant because every dataset is a dataset type of node.
[cid:ii_19590066a3fcb971f162]
[cid:ii_19590066a3fcb971f163]
...
[cid:ii_19590066a3fcb971f164]
The reason for the current design is that I also have a dataflow table as shown below.
A dataflow record describes the flow of data between two nodes. Now, if there are 3 types of node: dataset, processing, and user, then there are 9 types of dataflow. Hence, 9 tables are needed to represent the dataflows instead of 1. In the below, source_id and destination_id are both node_ids. If I want to know if a node is a dataset, processing node, or a user, I just look that up in the nodes table.
[cid:ii_19590066a3ecb971f161]
So, I think the crux of the problem is that Postgres assumes that inheritance is declared when it is not.
More answers to your questions coming.
Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady;
________________________________
From: Adrian Klaver <[email protected]<mailto:[email protected]>>
Sent: Wednesday, March 12, 2025 12:01 PM
To: mark bradley <[email protected]<mailto:[email protected]>>
Cc: pgsql-general <[email protected]<mailto:[email protected]>>
Subject: Re: Duplicate Key Values
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
[email protected]<mailto:[email protected]>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Attachments:
[image/png] image.png (106.6K, 3-image.png)
download | view image
[image/png] image.png (39.4K, 4-image.png)
download | view image
[image/png] image.png (71.7K, 5-image.png)
download | view image
[image/png] image.png (24.8K, 6-image.png)
download | view image
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 15:05 ` Re: Duplicate Key Values Ron Johnson <[email protected]>
2025-03-13 15:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-13 16:05 ` Adrian Klaver <[email protected]>
2025-03-13 16:25 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 16:35 ` Re: Duplicate Key Values mark bradley <[email protected]>
0 siblings, 2 replies; 25+ messages in thread
From: Adrian Klaver @ 2025-03-13 16:05 UTC (permalink / raw)
To: mark bradley <[email protected]>; Ron Johnson <[email protected]>; pgsql-general
On 3/13/25 08:56, mark bradley wrote:
> >Postgresql does not assume / default to inheritance. In text-mode
> clients where you type >in "raw" SQL, you have to explicitly add an
> explicit "INHERITS <parent_table>" clause to the >"CREATE TABLE foo"
> statement.
>
> >Are you creating the tables via PgAdmin point-and-click?
>
> I am using PgAdmin 4 v9.1.
>
> I think the problem may also be related to the fact that I had
> *node_id*Â and *node_type *were in both tables from an earlier design and
> Postgres would not let me delete*Â node_type*Â from the*Â dataset*Â table.
Because it was inherited:
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);
alter table node_1 drop column fld1;
ERROR: cannot drop inherited column "fld1"
>
> As an experiment, I created a simple version of the same tables from
> scratch without *node_type* in the *dataset* table. So far, no dups are
> appearing.
I'm assuming that by 'simple version' you mean no inheritance.
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 15:05 ` Re: Duplicate Key Values Ron Johnson <[email protected]>
2025-03-13 15:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 16:05 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-13 16:25 ` mark bradley <[email protected]>
2025-03-13 17:03 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
1 sibling, 1 reply; 25+ messages in thread
From: mark bradley @ 2025-03-13 16:25 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; Ron Johnson <[email protected]>; pgsql-general
> I'm assuming that by 'simple version' you mean no inheritance.
Inheritance was not specified by me in either case. By simple, I mean that I used fewer columns in the test version, like so:
[cid:c8b9cd7d-1fe4-496a-be63-cbb8f658a785]
[cid:b4c79bd5-7eac-4dbf-bf79-750adc941b74]
Where node_id is a foreign key in dataset, and node_type is not.
Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady;
________________________________
From: Adrian Klaver <[email protected]>
Sent: Thursday, March 13, 2025 12:05 PM
To: mark bradley <[email protected]>; Ron Johnson <[email protected]>; pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
On 3/13/25 08:56, mark bradley wrote:
> >Postgresql does not assume / default to inheritance. In text-mode
> clients where you type >in "raw" SQL, you have to explicitly add an
> explicit "INHERITS <parent_table>" clause to the >"CREATE TABLE foo"
> statement.
>
> >Are you creating the tables via PgAdmin point-and-click?
>
> I am using PgAdmin 4 v9.1.
>
> I think the problem may also be related to the fact that I had
> *node_id* and *node_type *were in both tables from an earlier design and
> Postgres would not let me delete* node_type* from the* dataset* table.
Because it was inherited:
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);
alter table node_1 drop column fld1;
ERROR: cannot drop inherited column "fld1"
>
> As an experiment, I created a simple version of the same tables from
> scratch without *node_type* in the *dataset* table. So far, no dups are
> appearing.
I'm assuming that by 'simple version' you mean no inheritance.
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
--
Adrian Klaver
[email protected]
Attachments:
[image/png] image.png (41.7K, 3-image.png)
download | view image
[image/png] image.png (51.3K, 4-image.png)
download | view image
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 15:05 ` Re: Duplicate Key Values Ron Johnson <[email protected]>
2025-03-13 15:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 16:05 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 16:25 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-13 17:03 ` Adrian Klaver <[email protected]>
2025-03-14 12:10 ` Re: Duplicate Key Values mark bradley <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Adrian Klaver @ 2025-03-13 17:03 UTC (permalink / raw)
To: mark bradley <[email protected]>; Ron Johnson <[email protected]>; pgsql-general
On 3/13/25 09:25, mark bradley wrote:
>
> > I'm assuming that by 'simple version' you mean no inheritance.
>
> Inheritance was not specified by me in either case. By simple, I mean
Unless someone else is working on this code, it was done by you.
Pretty sure it had to do with from this message:
https://www.postgresql.org/message-id/SJ2PR22MB4328CEB1B47FC1AC4A996CB3BAD12%40SJ2PR22MB4328.namprd2...
"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."
I'm guessing you actually did do the subclassing(inheritance) as it was
an option presented in the pgAdmin4 CREATE TABLE screen.
Anyway at this point the problem has been identified and a solution devised.
> that I used fewer columns in the test version, like so:
>
>
>
> Where *node_id*Â is a foreign key in dataset, and *node_type*Â is not.
>
> Best regards,
> Mark Brady
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 15:05 ` Re: Duplicate Key Values Ron Johnson <[email protected]>
2025-03-13 15:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 16:05 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 16:25 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 17:03 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-14 12:10 ` mark bradley <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: mark bradley @ 2025-03-14 12:10 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; Ron Johnson <[email protected]>; pgsql-general
Adrian & Ron,
Thank you for your assistance.
Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady;
________________________________
From: Adrian Klaver <[email protected]>
Sent: Thursday, March 13, 2025 1:03 PM
To: mark bradley <[email protected]>; Ron Johnson <[email protected]>; pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
On 3/13/25 09:25, mark bradley wrote:
>
> > I'm assuming that by 'simple version' you mean no inheritance.
>
> Inheritance was not specified by me in either case. By simple, I mean
Unless someone else is working on this code, it was done by you.
Pretty sure it had to do with from this message:
https://www.postgresql.org/message-id/SJ2PR22MB4328CEB1B47FC1AC4A996CB3BAD12%40SJ2PR22MB4328.namprd2...
"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."
I'm guessing you actually did do the subclassing(inheritance) as it was
an option presented in the pgAdmin4 CREATE TABLE screen.
Anyway at this point the problem has been identified and a solution devised.
> that I used fewer columns in the test version, like so:
>
>
>
> Where *node_id* is a foreign key in dataset, and *node_type* is not.
>
> Best regards,
> Mark Brady
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 15:05 ` Re: Duplicate Key Values Ron Johnson <[email protected]>
2025-03-13 15:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-13 16:05 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
@ 2025-03-13 16:35 ` mark bradley <[email protected]>
1 sibling, 0 replies; 25+ messages in thread
From: mark bradley @ 2025-03-13 16:35 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; Ron Johnson <[email protected]>; pgsql-general
> I'm assuming that by 'simple version' you mean no inheritance.
Anyway, inheritance can be undone via
ALTER TABLE dataset NO INHERIT node;
Now, there are no dups and hopefully it will stay that way.
Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady;
________________________________
From: Adrian Klaver <[email protected]>
Sent: Thursday, March 13, 2025 12:05 PM
To: mark bradley <[email protected]>; Ron Johnson <[email protected]>; pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
On 3/13/25 08:56, mark bradley wrote:
> >Postgresql does not assume / default to inheritance. In text-mode
> clients where you type >in "raw" SQL, you have to explicitly add an
> explicit "INHERITS <parent_table>" clause to the >"CREATE TABLE foo"
> statement.
>
> >Are you creating the tables via PgAdmin point-and-click?
>
> I am using PgAdmin 4 v9.1.
>
> I think the problem may also be related to the fact that I had
> *node_id* and *node_type *were in both tables from an earlier design and
> Postgres would not let me delete* node_type* from the* dataset* table.
Because it was inherited:
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);
alter table node_1 drop column fld1;
ERROR: cannot drop inherited column "fld1"
>
> As an experiment, I created a simple version of the same tables from
> scratch without *node_type* in the *dataset* table. So far, no dups are
> appearing.
I'm assuming that by 'simple version' you mean no inheritance.
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-13 15:23 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 25+ messages in thread
From: Adrian Klaver @ 2025-03-13 15:23 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: pgsql-general
On 3/13/25 07:56, mark bradley wrote:
> >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
>
> That would make sense except that I never explicitly use the
> *inherits* option in the *node_1* (my *dataset*) table. Postgres seems
> to be assuming that.
1) Well it is there from \d node:
Number of child tables: 2 (Use \d+ to list them.)
and from \d dataset and \d processing_node
Inherits: node
Also it explains the behavior.
2) Postgres does not assume that, it was done explicitly by some command.
>
> Also, the second column in *node*Â and in *dataset*Â are two different
> columns. However, Postgres insists on the *node_type* attribute being
> included (last column) in table *dataset *and won't let me delete
> it.**This is redundant because every dataset is a dataset type of node.
That is what inheritance does:
https://www.postgresql.org/docs/current/sql-createtable.html
"
The optional INHERITS clause specifies a list of tables from which
the new table automatically inherits all columns. Parent tables can be
plain tables or foreign tables.
Use of INHERITS creates a persistent relationship between the new
child table and its parent table(s). Schema modifications to the
parent(s) normally propagate to children as well, and by default the
data of the child table is included in scans of the parent(s).
If the same column name exists in more than one parent table, an
error is reported unless the data types of the columns match in each of
the parent tables. If there is no conflict, then the duplicate columns
are merged to form a single column in the new table. If the column name
list of the new table contains a column name that is also inherited, the
data type must likewise match the inherited column(s), and the column
definitions are merged into one. If the new table explicitly specifies a
default value for the column, this default overrides any defaults from
inherited declarations of the column. Otherwise, any parents that
specify default values for the column must all specify the same default,
or an error will be reported.
CHECK constraints are merged in essentially the same way as
columns: if multiple parent tables and/or the new table definition
contain identically-named CHECK constraints, these constraints must all
have the same check expression, or an error will be reported.
Constraints having the same name and expression will be merged into one
copy. A constraint marked NO INHERIT in a parent will not be considered.
Notice that an unnamed CHECK constraint in the new table will never be
merged, since a unique name will always be chosen for it.
Column STORAGE settings are also copied from parent tables.
If a column in the parent table is an identity column, that
property is not inherited. A column in the child table can be declared
identity column if desired.
"
> So, I think the crux of the problem is that Postgres assumes that
> inheritance is declared when it is not.
No it does not, as the screenshot shows. There is an explicit setting
for 'Inherited from table(s)'
>
> More answers to your questions coming.
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Duplicate Key Values
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:56 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:05 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 15:12 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 15:16 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 16:00 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 18:52 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 19:37 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-11 19:55 ` Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 20:24 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Re: Duplicate Key Values Adrian Klaver <[email protected]>
2025-03-13 14:56 ` Re: Duplicate Key Values mark bradley <[email protected]>
@ 2025-03-13 15:29 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 25+ messages in thread
From: Adrian Klaver @ 2025-03-13 15:29 UTC (permalink / raw)
To: mark bradley <[email protected]>; +Cc: pgsql-general
On 3/13/25 07:56, mark bradley wrote:
> >Mark, to illustrate:
> So, I think the crux of the problem is that Postgres assumes that
> inheritance is declared when it is not.
Follow up.
From pgAdmin end:
https://www.pgadmin.org/docs/pgadmin4/9.1/table_dialog.html
"Use the drop-down listbox next to Inherited from table(s) to specify
any parent table(s); the table will inherit columns from the selected
parent table(s)."
This is an explicit action.
>
> More answers to your questions coming.
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2025-03-14 12:10 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-11 14:28 Re: Duplicate Key Values mark bradley <[email protected]>
2025-03-11 14:33 ` Greg Sabino Mullane <[email protected]>
2025-03-11 14:40 ` Ron Johnson <[email protected]>
2025-03-11 14:56 ` Adrian Klaver <[email protected]>
2025-03-11 15:05 ` mark bradley <[email protected]>
2025-03-11 15:12 ` Adrian Klaver <[email protected]>
2025-03-11 15:16 ` mark bradley <[email protected]>
2025-03-11 15:52 ` Greg Sabino Mullane <[email protected]>
2025-03-11 16:00 ` Adrian Klaver <[email protected]>
2025-03-11 18:52 ` mark bradley <[email protected]>
2025-03-11 19:37 ` Adrian Klaver <[email protected]>
2025-03-11 19:55 ` mark bradley <[email protected]>
2025-03-11 20:24 ` Adrian Klaver <[email protected]>
2025-03-12 15:46 ` Adrian Klaver <[email protected]>
2025-03-12 16:01 ` Adrian Klaver <[email protected]>
2025-03-13 14:56 ` mark bradley <[email protected]>
2025-03-13 15:05 ` Ron Johnson <[email protected]>
2025-03-13 15:56 ` mark bradley <[email protected]>
2025-03-13 16:05 ` Adrian Klaver <[email protected]>
2025-03-13 16:25 ` mark bradley <[email protected]>
2025-03-13 17:03 ` Adrian Klaver <[email protected]>
2025-03-14 12:10 ` mark bradley <[email protected]>
2025-03-13 16:35 ` mark bradley <[email protected]>
2025-03-13 15:23 ` Adrian Klaver <[email protected]>
2025-03-13 15:29 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox