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
adrian.klaver@xxxxxxxxxxx
