My mistake, I misread the output from \d dataset
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.
Not that I can tell.
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Tuesday, March 11, 2025 12:00 PM To: mark bradley <markbradyju@xxxxxxxxxxx> Cc: pgsql-general <pgsql-general@xxxxxxxxxxxxxx> 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 <adrian.klaver@xxxxxxxxxxx> > *Sent:* Tuesday, March 11, 2025 11:12 AM > *To:* mark bradley <markbradyju@xxxxxxxxxxx> > *Cc:* pgsql-general <pgsql-general@xxxxxxxxxxxxxx> > *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 <adrian.klaver@xxxxxxxxxxx> >> *Sent:* Tuesday, March 11, 2025 10:56 AM >> *To:* mark bradley <markbradyju@xxxxxxxxxxx> >> *Cc:* pgsql-general <pgsql-general@xxxxxxxxxxxxxx> >> *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 >> adrian.klaver@xxxxxxxxxxx >> > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx |
