SSL error on install of PEM during Posgres install

[mark bradley]

I am getting the following error message during install of PEM while installing 
Posgres.

[cid:da2b6d5e-c3b0-430a-98e0-dac72d0aba23]

How can I fix this?

PEM install error

[mark bradley]

While installing PostgreSQL I am getting this error message during the PEM 
server portion.  Do I need the PEM server?  If so, what is the solution?

Thanks!

[cid:b414e51e-598a-44bf-951a-ce754c0fa77d]

Re: Intermittent errors when fetching cursor rows on PostgreSQL 16

[mark bradley]

I'm getting a strange error message when I try to insert a date using the 
view/edit grid in pgadmin.  See below.  I've tried quotes, no quotes and 
various formats.  The column type is clearly "date."

[cid:4ff69cfe-2efa-4636-8dde-6230512706f7]

Mark Brady, Ph.D.
Deputy Chief Data Officer, TRMC
amazon.com/author/markjbrady

From: Enrico Schenone 
Sent: Friday, December 20, 2024 10:02 AM
To: Adrian Klaver ; 
pgsql-general@lists.postgresql.org 
Cc: Massimo Catti ; Livio Pizzolo 
Subject: Re: Intermittent errors when fetching cursor rows on PostgreSQL 16

Hi, Adrian.
Today I have collected a tcpdump at client side with communications
between application server and db server while the issue was occurring
one time per second on another program.
I send you two files.
The first one is a zipped tarball (.tgz) containing a text
representation of the tcpdump starting at point where it reports the
declaration of the failing cursor ("cu4" as you can see in the first
line of the file) and subsequent fetch. Consider that the client
application log detected the XX001 error on the first FETCH of the
cursor at 2024-12-20 12:17:35.175
The second file (zipped tarball .tgz) is too big to be sent as
attachment, so I provide a link where it can be downloaded. It is the
fraction of tcpdump recorded during the program failure (occurred
several times). It is in .pcap format so it is possible to open it with
Wireshark or tcpdump -A -r
Anyone interested can download it at
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcleislabs.cleistech.it%2Fdownloads%2Ftcpdump_out009.pcap.tgz&data=05%7C02%7C%7Cfe8da7a507744c7842d608dd210ec77b%7C84df9e7fe9f640afb435%7C1%7C0%7C638703069888918551%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=KrfcnJUpuwV8CqzzkPvOf6SHgewaxFB%2FjuFm8vSDkgM%3D&reserved=0

Consider that during the dump several different cursor was declared with
the name "cu4", but the one failing is the one of the first line.
Maybe an expert (I'm not so expert) can see if the disconnection is
really made by the client and/or if the data returned by the server are
really corrupted as per XX001 SQLSTATE.

Best regards.
Enrico

Il 19/12/24 22:47, Adrian Klaver ha scritto:
>
>
> On 12/19/24 11:40 AM, Enrico Schenone wrote:
>> Hello, my answers in line along your message ...
>> Thanks a lot again.
>>
>> Enrico
>>
>
>>> On 12/19/24 10:11, Enrico Schenone wrote:
 Good day, Adrian.
 I get the error inside the program by catching the exception and
 logging it with diagnostic info provided by the DVM (a runtime
 interpreter similar in concept to a JVM) that embed the PG driver.
>>>
>
>> The 4Js DVM (Dynamic Virtual Machine) is that one
>> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2F4js.com%2Fonline_documentation%2Ffjs-gas-manual-html%2Findex.html%23gas-topics%2Fc_gas_what_is_dvm.html&data=05%7C02%7C%7Cfe8da7a507744c7842d608dd210ec77b%7C84df9e7fe9f640afb435%7C1%7C0%7C638703069888937436%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=Hy8B3dQj2ugrOZOy0sLlCZDRPlsSsfGPrpwz1Jjbxwg%3D&reserved=0
>>
>>> In other words an Android client?
>>>
>> No, it is a runtime interpreter for Linux, Windows, IBM AIX, macOS
>> and other unix-like OSs. It ensures the portability of 4Js Genero
>> compiled programs (p-code) on several OS platforms.
>> 4Js Genero is a Low Code Application Platform. The programming
>> language, named "BDL - Business Development Language", is an
>> evolution of the Informix-4gl.
>> Compiled programs needs a runtime interpreter (DVM) to be executed.
>> The DVM embeds at low-level the DB drivers provided by several vendors,
>
> From previous post you mentioned:
>
> "Four Js support said   vendor. In the case of PostgreSQL, we use the C API client "
>
> So are they building their own driver over libpq?
>
>> and at BDL high level the application program can easily connect to
>> the major DBs on the market thanks to its ODI (Open Database Interface).
 I can't give you info on what the DVM does at low level, but I can
 send you the distinct full session log fragment at server side,
 where it is quite easy to understand how the DVM translates the
 program's SQL queries end what PostgreSQL does.
>>>
>>> That might be useful.
>>>
>> Please take a look to the attached text file, that is the full
>> failing session log (filtered from the debug5 PostgreSQL server log).
>
> This is where it falls off the rails, but I can't see why?:
>
> 2024-12-16 17:27:14.406 CET [2214722] cleistech@hh24odds_prod -
> 192.168.16.1790676054e0.21cb42 LOCATION: ShowTra

Re: Duplicate Key Values

[mark bradley]

Adrian & Ron,

Thank you for your assistance.

Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>

From: Adrian Klaver 
Sent: Thursday, March 13, 2025 1:03 PM
To: mark bradley ; Ron Johnson 
; pgsql-general 
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.namprd22.prod.outlook.com

"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
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

Here are some of the references from Copilot

https://dba.stackexchange.com/questions/62675/why-does-my-table-hold-duplicate-primary-keys

https://stackoverflow.com/questions/1461/having-duplicate-rows-on-a-primary-key-and-unique-constraints-in-postgres


Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>

From: mark bradley 
Sent: Friday, March 7, 2025 9:34 AM
To: Adrian Klaver 
Cc: pgsql-general 
Subject: Re: Duplicate Key Values

This is what MS Copilot has to say about this apparent bug where Postgres 
inserts extra rows violating a primary keys uniqueness constraint:

Yes, this issue has been encountered by others. There are a few potential 
reasons why this might happen:

  1.
Sequence Out of Sync: Sometimes, the sequence that generates unique values for 
the primary key can become out of sync, especially after a bulk import or a 
database restore. You can check if the sequence is out of sync and reset it if 
necessary.
  2.
Index Corruption: Index corruption can occur due to various reasons, such as 
hardware failures or bugs in earlier versions of PostgreSQL. This can lead to 
duplicate primary keys being inserted.
  3.
Table Inheritance: If you are using table inheritance, primary keys are not 
enforced among inherited tables. This can lead to duplicates if not handled 
correctly.
  4.
Application Logic: Sometimes, the application logic might inadvertently insert 
duplicate records. Reviewing the application code and insert statements can 
help identify and resolve such issues.

To resolve the issue, you can:

  *
Check and reset the sequence if it's out of sync.
  *
Rebuild the index if it's corrupted.

Any of the first 3 could be involved.  There isn't an application involved 
other than pgAdmin.


  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.

What to do?  I hesitate to just delete my tables and start over because this 
error will reoccur.

Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>

From: Adrian Klaver 
Sent: Thursday, March 6, 2025 3:34 PM
To: mark bradley 
Cc: pgsql-general 
Subject: Re: Duplicate Key Values

On 3/6/25 12:06, mark bradley wrote:

My mistake I forgot to Cc list on my previous post, which was:

That would be an issue and also would mean it is not a PK.

In psql  do:

\d dataset

and show the results as text in your reply.

Ccing list


The below shows there is an index("dataset_pkey") on node_id.

Note, reindexing will take a lock on the table that prevents changing
data while the operation is running. See the below for more information:

https://www.postgresql.org/docs/current/sql-reindex.html

If the table is not to big and you can interrupt access to it then the
simplest command to run would be:

REINDEX  TABLE dataset;

> Universal Metadata Schema=# \d dataset
>   Table "public.dataset"
>Column   |   Type| Collation |
> Nullable | Defau
> lt
> ---+---+---+--+--
> ---
>   node_id   | integer   |   |
> not null |
>   dataset_name  | character varying(25) |   |
> not null |
>   notes | text  |   |
> |
>   dataset_type  | database_type |   |
> not null |
>   dataset_maturity  | database_maturity_type|   |
> not null |
>   disposition   | disposition_type  |   |
> not null |
>   start_date| date  |   |
> |
>   end_date  | date  |   |
> |
>   most_recent_update| date  |   |
> |
>   update_periodicity| interval  |   |
> |
>   system_of_record  | text  |   |
> |
>   point_of_contact  | integer   |   |
> not null |
>   dataset_url   | text  |   |
> |
>   classification_level  | classification_level_type |   |
> not null |
>   physical_location | text  |   |
> |

Re: Duplicate Key Values

[mark bradley]

This is what MS Copilot has to say about this apparent bug where Postgres 
inserts extra rows violating a primary keys uniqueness constraint:

Yes, this issue has been encountered by others. There are a few potential 
reasons why this might happen:

  1.
Sequence Out of Sync: Sometimes, the sequence that generates unique values for 
the primary key can become out of sync, especially after a bulk import or a 
database restore. You can check if the sequence is out of sync and reset it if 
necessary.
  2.
Index Corruption: Index corruption can occur due to various reasons, such as 
hardware failures or bugs in earlier versions of PostgreSQL. This can lead to 
duplicate primary keys being inserted.
  3.
Table Inheritance: If you are using table inheritance, primary keys are not 
enforced among inherited tables. This can lead to duplicates if not handled 
correctly.
  4.
Application Logic: Sometimes, the application logic might inadvertently insert 
duplicate records. Reviewing the application code and insert statements can 
help identify and resolve such issues.

To resolve the issue, you can:

  *
Check and reset the sequence if it's out of sync.
  *
Rebuild the index if it's corrupted.

Any of the first 3 could be involved.  There isn't an application involved 
other than pgAdmin.


  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.

What to do?  I hesitate to just delete my tables and start over because this 
error will reoccur.

Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>

From: Adrian Klaver 
Sent: Thursday, March 6, 2025 3:34 PM
To: mark bradley 
Cc: pgsql-general 
Subject: Re: Duplicate Key Values

On 3/6/25 12:06, mark bradley wrote:

My mistake I forgot to Cc list on my previous post, which was:

That would be an issue and also would mean it is not a PK.

In psql  do:

\d dataset

and show the results as text in your reply.

Ccing list


The below shows there is an index("dataset_pkey") on node_id.

Note, reindexing will take a lock on the table that prevents changing
data while the operation is running. See the below for more information:

https://www.postgresql.org/docs/current/sql-reindex.html

If the table is not to big and you can interrupt access to it then the
simplest command to run would be:

REINDEX  TABLE dataset;

> Universal Metadata Schema=# \d dataset
>   Table "public.dataset"
>Column   |   Type| Collation |
> Nullable | Defau
> lt
> ---+---+---+--+--
> ---
>   node_id   | integer   |   |
> not null |
>   dataset_name  | character varying(25) |   |
> not null |
>   notes | text  |   |
> |
>   dataset_type  | database_type |   |
> not null |
>   dataset_maturity  | database_maturity_type|   |
> not null |
>   disposition   | disposition_type  |   |
> not null |
>   start_date| date  |   |
> |
>   end_date  | date  |   |
> |
>   most_recent_update| date  |   |
> |
>   update_periodicity| interval  |   |
> |
>   system_of_record  | text  |   |
> |
>   point_of_contact  | integer   |   |
> not null |
>   dataset_url   | text  |   |
> |
>   classification_level  | classification_level_type |   |
> not null |
>   physical_location | text  |   |
> |
>   quality_control   | yes_no_type   |   |
> not null |
>   dataset_documentation_url | text  |   |
> not null |
>   description   | text  |   |
> |
>   node_type | node_type |   |
> |
>   dummy | integer   |   |
> |
> Indexes:
>  "dataset_pkey" PRIMARY KEY, btree 

Re: Duplicate Key Values

[mark bradley]

Wouldn't that be nice 🙂


Best regards,
Mark Brady
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>

From: Adrian Klaver 
Sent: Friday, March 7, 2025 10:55 AM
To: mark bradley 
Cc: pgsql-general 
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
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

I tried adding a dummy column with different values and then deleting one, as 
you suggested.  Got the same message.


Mark Brady, Ph.D.
Deputy Chief Data Officer, TRMC
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>

From: mark bradley 
Sent: Thursday, March 6, 2025 1:11 PM
To: Adrian Klaver ; Ron Johnson 
; pgsql-general 
Subject: Re: Duplicate Key Values

Here is the table definition:

[cid:ecac8e92-826b-45c8-95a8-aaf0e55c4f9c]

And here is the error message I get when I try to delete a duplicate:

[cid:a0f5f298-984d-4f89-abd2-475c02e65b9d]

Mark Brady,
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>

From: Adrian Klaver 
Sent: Wednesday, March 5, 2025 6:49 PM
To: Ron Johnson ; pgsql-general 

Subject: Re: Duplicate Key Values

On 3/5/25 15:37, Ron Johnson wrote:
> What does "and unique identifier" mean?
>
> (Creating a Primary Key constraint automatically creates a unique
> no-nulls-allowed index on the column(s), so no other index is needed.)
>
> Can you show the table definition?
>
> Anyway, you /should/ be able to delete one of the duplicate node_id
> records by specifying another column with a different value;
>
> On Wed, Mar 5, 2025 at 6:14 PM mark bradley  <mailto:markbrad...@outlook.com>> wrote:
>
> Hi Ron,
>
> The key is an integer.  I'm using pGAdmin4 and recently updated to
> the latest version.

1) Did you not see duplicates with the old version of pgAdmin4?

2) What do you see if you use psql?

3) Did you upgrade/move the Postgres server or the underlying OS?

4) Have you tried reindexing the node_id field?

>
> The records are not all identical, some have NULL values of the
> non-key for some unknown reason.  Here is a screenshot.  The key is
> node_id, which I have specified as a key and unique identifier.
>
>
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

Here is the table definition:

[cid:ecac8e92-826b-45c8-95a8-aaf0e55c4f9c]

And here is the error message I get when I try to delete a duplicate:

[cid:a0f5f298-984d-4f89-abd2-475c02e65b9d]

Mark Brady,
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>

From: Adrian Klaver 
Sent: Wednesday, March 5, 2025 6:49 PM
To: Ron Johnson ; pgsql-general 

Subject: Re: Duplicate Key Values

On 3/5/25 15:37, Ron Johnson wrote:
> What does "and unique identifier" mean?
>
> (Creating a Primary Key constraint automatically creates a unique
> no-nulls-allowed index on the column(s), so no other index is needed.)
>
> Can you show the table definition?
>
> Anyway, you /should/ be able to delete one of the duplicate node_id
> records by specifying another column with a different value;
>
> On Wed, Mar 5, 2025 at 6:14 PM mark bradley  <mailto:markbrad...@outlook.com>> wrote:
>
> Hi Ron,
>
> The key is an integer.  I'm using pGAdmin4 and recently updated to
> the latest version.

1) Did you not see duplicates with the old version of pgAdmin4?

2) What do you see if you use psql?

3) Did you upgrade/move the Postgres server or the underlying OS?

4) Have you tried reindexing the node_id field?

>
> The records are not all identical, some have NULL values of the
> non-key for some unknown reason.  Here is a screenshot.  The key is
> node_id, which I have specified as a key and unique identifier.
>
>
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

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 
Sent: Friday, March 7, 2025 3:25 PM
To: mark bradley 
Cc: pgsql-general 
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%2aXGN2xPHhXywkwb72UWzinWu2wQ5WadcMw3_57rQ%40mail.gmail.com

>
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> 
> *From:* Adrian Klaver 
> *Sent:* Friday, March 7, 2025 10:55 AM
> *To:* mark bradley 
> *Cc:* pgsql-general 
> *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
> adrian.kla...@aklaver.com
>

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

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 
Sent: Tuesday, March 11, 2025 10:56 AM
To: mark bradley 
Cc: pgsql-general 
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
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

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 
Sent: Tuesday, March 11, 2025 11:12 AM
To: mark bradley 
Cc: pgsql-general 
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 
> *Sent:* Tuesday, March 11, 2025 10:56 AM
> *To:* mark bradley 
> *Cc:* pgsql-general 
> *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
> adrian.kla...@aklaver.com
>

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

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 
Sent: Tuesday, March 11, 2025 12:00 PM
To: mark bradley 
Cc: pgsql-general 
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 
> *Sent:* Tuesday, March 11, 2025 11:12 AM
> *To:* mark bradley 
> *Cc:* pgsql-general 
> *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 
>> *Sent:* Tuesday, March 11, 2025 10:56 AM
>> *To:* mark bradley 
>> *Cc:* pgsql-general 
>> *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.kla...@aklaver.com
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

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

Also in from previous \d dataset there where NOT VALID FK definitions.

Did you ever run VALIDATE CONSTRAINT against them?

>
>
>
> Is there anything in Postgres log at the time you did the above that
> showed it did more then a REINDEX?
>
>
> Not that I can tell.
>
>
>
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

>  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 
Sent: Thursday, March 13, 2025 12:05 PM
To: mark bradley ; Ron Johnson 
; pgsql-general 
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 " 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
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[mark bradley]

> 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 
Sent: Thursday, March 13, 2025 12:05 PM
To: mark bradley ; Ron Johnson 
; pgsql-general 
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 " 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
adrian.kla...@aklaver.com

Duplicate Key Values

[mark bradley]

Although I did not enter them, somehow duplicate primary key values have 
appeared in one of my tables.  I can't remove the offending rows because the 
key is a foreign key in another table.

What to do?

Mark Brady,
amazon.com/author/markjbrady