error : bingo buffer: internal error: corrupted block 8999 data len is -8

[lqiaoi]

Error occurs while I update the postgresql database, how to solve it ? Thank you !

[zhengzq]

same error

[Pandaaaa906]

same error

[Pandaaaa906]

I have tried pg12 bingo1.9 in windows
(it also have similar problem in sql server, but it will be gone by doing flush operation in doc)

it usually occur after pg crash with 0xC0000409
i had to drop index and rebuild it in pg
that's very annoying when table become big

what causing 0xC0000409
it seem occured when i execute some big query with bingo

SELECT
chembl.id,
chembl_id,
-- raw_json,
jsonb_path_query_first(chembl.raw_json, '$._metadata.unichem[*] ? (@.src_name=="ACToR")') ->> 'id' cas,
-- chem.cas,
chembl.raw_json ->> 'max_phase' max_phase,
chembl.raw_json ->> 'molecule_type' molecule_type,
chembl.raw_json #>> '{molecule_synonyms, 1, molecule_synonym}' molecule_synonym,
chembl.raw_json #>> '{molecule_structures, canonical_smiles}' canonical_smiles,
chembl.raw_json #>> '{molecule_structures, molfile}' molfile,
rxs_chem.reaxys_id,
rxys_rxn.*,
reactant_cas.cas

FROM "chemblmolecule" chembl

LEFT JOIN reaxys_chemical rxs_chem
ON (rxs_chem.raw_json #>> '{molImage,structure}') @ (1, 1, bingo.smiles(chembl.raw_json #>> '{molecule_structures, molfile}'), 'tanimoto')::bingo.sim

LEFT JOIN LATERAL (
    SELECT 
	id,
	reaxys_id,
	raw_json,
	raw_json ->> 'rxnFile' rxnfile,
-- 	reactant_cas.cas reactant_cas,
	reactants.*
	FROM "reaxys_reaction" rxn

	LEFT JOIN LATERAL (
		select 
		value reactant_json,
		value ->> 'id' reactant_reaxy_id,
		coalesce(value ->> 'name', value #>> '{details,chemicalNames,0}') reactant_name,
		bingo.smiles(value #>> '{molImage,structure}') reactant_smiles,
		value #>> '{supplierNumber}' supplier_num
		FROM jsonb_array_elements(raw_json -> 'reactants')
	) reactants
	ON TRUE

	WHERE 1=1
	-- AND	rxn.reaxys_id = 29612937
	AND rxn.raw_json @? ('$.products[*] ? (@.id == "'|| rxs_chem.reaxys_id::text ||'")')::jsonpath
) rxys_rxn
ON TRUE

-- cause 0xC0000409, table cas have 4M+ records
LEFT JOIN LATERAL (
	SELECT
	cas.cas
	FROM cas
	WHERE cas.iso_smiles @ (1,1,rxys_rxn.reactant_smiles, 'tanimoto')::bingo.sim
	LIMIT 1
)reactant_cas
ON TRUE

WHERE (
chembl.raw_json @? '$.max_phase ? (@== "4.0")'
OR chembl.raw_json @? '$.max_phase ? (@== "3.0")'
)
AND chembl.raw_json @? '$.molecule_structures.molfile'