Hi,
I have a very strange behavior on 14.10.
smrdbprod=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 row)
This is the statement which is executed:
insert into smrr_mgr.formula_composition_with_codes
select crart_id_par,
product_par.code_fin code_fin_par,
crfrm_chemin,
is_primary,
seq,
crart_id_comp,
product_comp.code_fin code_fin_comp,
qty_per
from smrr_mgr.formula_composition,
smrr_mgr.formula,
smrr_mgr.product product_par,
smrr_mgr.product product_comp
where formula_composition.crart_id_par = formula.crart_id
and formula_composition.crfrm_chemin = formula.chemin
and formula_composition.crart_id_par = product_par.id
and formula_composition.crart_id_comp = product_comp.id
order by seq;
Here are the table definitions:
smrdbprod=# \d pg_class
Table "pg_catalog.pg_class"
Column | Type | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
oid | oid | | not null |
relname | name | | not null |
relnamespace | oid | | not null |
reltype | oid | | not null |
reloftype | oid | | not null |
relowner | oid | | not null |
relam | oid | | not null |
relfilenode | oid | | not null |
reltablespace | oid | | not null |
relpages | integer | | not null |
reltuples | real | | not null |
relallvisible | integer | | not null |
reltoastrelid | oid | | not null |
relhasindex | boolean | | not null |
relisshared | boolean | | not null |
relpersistence | "char" | | not null |
relkind | "char" | | not null |
relnatts | smallint | | not null |
relchecks | smallint | | not null |
relhasrules | boolean | | not null |
relhastriggers | boolean | | not null |
relhassubclass | boolean | | not null |
relrowsecurity | boolean | | not null |
relforcerowsecurity | boolean | | not null |
relispopulated | boolean | | not null |
relreplident | "char" | | not null |
relispartition | boolean | | not null |
relrewrite | oid | | not null |
relfrozenxid | xid | | not null |
relminmxid | xid | | not null |
relacl | aclitem[] | | |
reloptions | text[] | C | |
relpartbound | pg_node_tree | C | |
Indexes:
"pg_class_oid_index" PRIMARY KEY, btree (oid)
"pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace)
"pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
smrdbprod=# \d smrr_mgr.formula_composition_with_codes
Table "smrr_mgr.formula_composition_with_codes"
Column | Type | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+---------
crart_id_par | numeric | | |
code_fin_par | character varying(18) | | |
crfrm_chemin | character varying(2) | | |
is_primary | character varying(1) | | |
seq | numeric(6,0) | | |
crart_id_comp | numeric | | |
code_fin_comp | character varying(18) | | |
qty_per | numeric | | |
Indexes:
"formula_composition_with_codes_crart_id_comp_index" btree (crart_id_comp)
"formula_composition_with_codes_unique_index" UNIQUE, btree (crart_id_par, is_primary, crfrm_chemin, seq, crart_id_comp)
smrdbprod=# \d smrr_mgr.formula_composition
Table "smrr_mgr.formula_composition"
Column | Type | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------
crart_id_par | numeric | | not null |
crfrm_chemin | character varying(2) | | not null |
seq | numeric(6,0) | | not null |
qty | numeric(7,0) | | |
assay | numeric(6,2) | | |
crart_id_comp | numeric | | |
crart_id_solv | numeric | | |
qty_per | numeric(21,20) | | |
recid | numeric | | |
update_date_dl | timestamp without time zone | | |
Indexes:
"formula_composition_pkey" PRIMARY KEY, btree (crart_id_par, crfrm_chemin, seq)
"formula_composition_crart_id_comp2_index" btree (crart_id_comp)
"formula_composition_crart_id_par2_index" btree (crart_id_par)
"formula_composition_update_date_dl2_index" btree (update_date_dl)
smrdbprod=# \d smrr_mgr.formula
Table "smrr_mgr.formula"
Column | Type | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------
crart_id | numeric | | not null |
chemin | character varying(2) | | not null |
is_primary | character varying(1) | | not null |
nb_part | numeric(7,0) | | not null |
nb_art | numeric(3,0) | | not null |
lower_level | numeric(2,0) | | not null |
is_ch_ok | character varying(1) | | not null |
dt_creat | timestamp without time zone | | |
dt_modif | timestamp without time zone | | |
dt_finalized | timestamp without time zone | | |
recid | numeric | | |
a_dt_ins | timestamp without time zone | | not null |
a_dt_upd | timestamp without time zone | | |
a_fc_ins | character varying(30) | | not null |
a_fc_upd | character varying(30) | | |
a_us_ins | character varying(30) | | not null |
a_us_upd | character varying(30) | | |
dt_validity | timestamp without time zone | | |
r_mem_id | numeric | | |
o_mem_id | numeric | | |
is_active_dl | character varying(1) | | not null |
inactive_ts | timestamp without time zone | | |
Indexes:
"formula_pkey" PRIMARY KEY, btree (crart_id, chemin)
"formula_last_upd_dt_index" btree (COALESCE(a_dt_upd, a_dt_ins))
Table "smrr_mgr.product"
Column | Type | Collation | Nullable | Default
--------------------------+-----------------------------+-----------+----------+---------
id | numeric | | not null |
code_fin | character varying(18) | | not null |
code_lab | character varying(18) | | |
fiipc_code | numeric(2,0) | | |
is_experimental | character varying(1) | | not null |
is_fl | character varying(1) | | not null |
is_pe | character varying(1) | | not null |
is_ch | character varying(1) | | not null |
prod_src | character varying(1) | | not null |
status | character varying(1) | | |
grp_cat | character varying(1) | | |
gcp | character varying(1) | | |
collection | character varying(1) | | |
pct_pe_prom | numeric(3,0) | | not null |
pct_fl_prom | numeric(3,0) | | not null |
pct_fab | numeric(3,0) | | not null |
std_avail | numeric(10,0) | | not null |
brand | character varying(60) | | |
costc_code | numeric(2,0) | | not null |
coton_code | numeric(6,0) | | |
is_fl_for_blend | character varying(1) | | not null |
dt_creat | timestamp without time zone | | |
recid | numeric | | |
a_dt_ins | timestamp without time zone | | not null |
a_dt_upd | timestamp without time zone | | |
a_fc_ins | character varying(30) | | not null |
a_fc_upd | character varying(30) | | |
a_us_ins | character varying(30) | | not null |
a_us_upd | character varying(30) | | |
remark | character varying(200) | | |
dt_transf | timestamp without time zone | | |
is_cnc | character varying(1) | | not null |
r_mem_id | numeric | | |
is_frm | character varying(1) | | not null |
is_rest_pe | character varying(1) | | |
is_rest_fl | character varying(1) | | |
o_mem_id | numeric | | |
prod_nat | character varying(1) | | not null |
is_food_ingredient | character varying(1) | | not null |
is_heart | character varying(1) | | not null |
fl_solubility | character varying(20) | | |
scipc_code | numeric(2,0) | | |
mail_msg | character varying(200) | | |
is_oc | character varying(1) | | not null |
is_oc_dt_upd | timestamp without time zone | | |
is_oc_fc_upd | character varying(30) | | |
is_oc_us_upd | character varying(30) | | |
ton_s_perception | character varying(2) | | not null |
dt_finalized | timestamp without time zone | | |
rd_coll_number | character varying(10) | | |
fl_rm_intro_pid | character varying(4) | | |
fl_rm_intro_zone | character varying(10) | | |
fl_rm_intro_cat | character varying(9) | | |
fl_rm_intro_sub_cat | character varying(9) | | |
fl_rm_intro_is_sensitive | character varying(1) | | |
is_active_dl | character varying(1) | | not null |
inactive_ts | timestamp without time zone | | |
Indexes:
"product_pkey" PRIMARY KEY, btree (id)
"product_code_fin" btree (code_fin)
"product_code_fin_prefix" btree (substr(code_fin::text, 1, 6))
"product_code_fin_prefix_new_serie" btree (substr(code_fin::text, 1, 9))
"product_last_upd_dt_index" btree (COALESCE(a_dt_upd, a_dt_ins))
The target table is empty and when the statement is executed there is a unique key violation:
smrdbprod=# truncate smrr_mgr.formula_composition_with_codes;
TRUNCATE TABLE
smrdbprod=# insert into smrr_mgr.formula_composition_with_codes
smrdbprod-# select crart_id_par,
smrdbprod-# product_par.code_fin code_fin_par,
smrdbprod-# crfrm_chemin,
smrdbprod-# is_primary,
smrdbprod-# seq,
smrdbprod-# crart_id_comp,
smrdbprod-# product_comp.code_fin code_fin_comp,
smrdbprod-# qty_per
smrdbprod-# from smrr_mgr.formula_composition,
smrdbprod-# smrr_mgr.formula,
smrdbprod-# smrr_mgr.product product_par,
smrdbprod-# smrr_mgr.product product_comp
smrdbprod-# where formula_composition.crart_id_par = formula.crart_id
smrdbprod-# and formula_composition.crfrm_chemin = formula.chemin
smrdbprod-# and formula_composition.crart_id_par = product_par.id
smrdbprod-# and formula_composition.crart_id_comp = product_comp.id
smrdbprod-# order by seq;
ERROR: duplicate key value violates unique constraint "formula_composition_with_codes_unique_index"
DETAIL: Key (crart_id_par, is_primary, crfrm_chemin, seq, crart_id_comp)=(4775290000, Y, @, 0, 1765660000) already exists.
Asking for the conditions in the key violation error message gives only one row:
smrdbprod=# select crart_id_par,
smrdbprod-# product_par.code_fin code_fin_par,
smrdbprod-# crfrm_chemin,
smrdbprod-# is_primary,
smrdbprod-# seq,
smrdbprod-# crart_id_comp,
smrdbprod-# product_comp.code_fin code_fin_comp,
smrdbprod-# qty_per
smrdbprod-# from smrr_mgr.formula_composition,
smrdbprod-# smrr_mgr.formula,
smrdbprod-# smrr_mgr.product product_par,
smrdbprod-# smrr_mgr.product product_comp
smrdbprod-# where formula_composition.crart_id_par = formula.crart_id
smrdbprod-# and formula_composition.crfrm_chemin = formula.chemin
smrdbprod-# and formula_composition.crart_id_par = product_par.id
smrdbprod-# and formula_composition.crart_id_comp = product_comp.id
smrdbprod-# and crart_id_par = 4775290000
smrdbprod-# and is_primary = 'Y'
smrdbprod-# and crfrm_chemin = '@'
smrdbprod-# and seq = 0
smrdbprod-# and crart_id_comp = 1765660000
smrdbprod-# order by seq;
crart_id_par | code_fin_par | crfrm_chemin | is_primary | seq | crart_id_comp | code_fin_comp | qty_per
--------------+--------------+--------------+------------+-----+---------------+---------------+------------------------
4775290000 | 475860 KN | @ | Y | 0 | 1765660000 | 908290 | 0.00078864353312302800
(1 row)
Doing the same with enable_hashjoin = off, runs successfully:
smrdbprod=# truncate smrr_mgr.formula_composition_with_codes;
TRUNCATE TABLE
smrdbprod=# set enable_hashjoin to off;
SET
smrdbprod=# insert into smrr_mgr.formula_composition_with_codes
select crart_id_par,
product_par.code_fin code_fin_par,
crfrm_chemin,
is_primary,
seq,
crart_id_comp,
product_comp.code_fin code_fin_comp,
qty_per
from smrr_mgr.formula_composition,
smrr_mgr.formula,
smrr_mgr.product product_par,
smrr_mgr.product product_comp
where formula_composition.crart_id_par = formula.crart_id
and formula_composition.crfrm_chemin = formula.chemin
and formula_composition.crart_id_par = product_par.id
and formula_composition.crart_id_comp = product_comp.id
order by seq;
INSERT 0 20756629
I've tried to reproduce this locally and dumped those tables. While importing them I got errors like this:
psql:restore.sql:242: ERROR: could not create unique index "formula_pkey"
DETAIL: Key (crart_id, chemin)=(12383610000, @) is duplicated.
Checking the primary key on the live database gave this:
smrdbprod=# reindex index CONCURRENTLY smrr_mgr.formula_pkey;
ERROR: could not create unique index "formula_pkey_ccnew"
DETAIL: Key (crart_id, chemin)=(21507180000, @) is duplicated.
smrdbprod=#
But:
smrdbprod=# select count(*) from smrr_mgr.formula where crart_id = 21507180000 and chemin = '@';
count
-------
1
(1 row)
smrdbprod=# select crart_id, chemin, count(*) from smrr_mgr.formula group by crart_id, chemin having count(*) > 1;
crart_id | chemin | count
----------+--------+-------
(0 rows)
What do I see here? Corruption?
Thanks for any help
Regards
Daniel
