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'