The following bug has been logged online:
Bug reference: 5569
Logged by: Alessio
Email address: a.burgassi@gmail.com
PostgreSQL version: 8.4.3 build1400
Operating system: Windows 2008 Web
Description: Select in trigger don't retrive true record
Details:
Hi,
My trigger is executed on insert in a table.
After some check ther is a schema change.
Afeter schema change saltuary and random time the select on set FOUND =
false when the record exist!
if i execute a full analizy and vacum the problem is solved.
var_azienda_schema := 'azienda_'|| var_azienda_codice;
rec:=NULL;
SELECT nspname INTO rec FROM pg_catalog.pg_namespace WHERE nspname =
var_azienda_schema LIMIT 1;
IF NOT FOUND THEN
-- SE NON TROVO LO SCHEMA ESCO
var_err:= var_trigger_name || ' ' || 'ERR4 schema '||
COALESCE(CAST(var_azienda_schema as TEXT),'NULL') ||' inesistente.
CODICE='|| NEW.codice;
INSERT INTO public.log(data, errore, pagina, priorita) VALUES ( NOW(),
var_err, '', -1);
UPDATE public.chiamata_evento_temp SET data_elaborazione = NOW(),note =
var_err WHERE codice = NEW.codice;
EXECUTE 'SET search_path = public';
RETURN NEW;
END IF;
--CAMBIO SCHEMA
EXECUTE 'SET search_path = '||var_azienda_schema;
----------------------------------------------------------
-- FIND INFO CODICEID
----------------------------------------------------------
-- SE codiceid E' SETTATO CERCO LE INFORMAZIONI
rec:=NULL;
SELECT
account_codiceid.codice_protocollo,account_codiceid.descrizione,account_codi
ceid.area,account_codiceid.codice, account_codiceid.codice_account AS
codice_account FROM public.account_codiceid INTO rec WHERE
account_codiceid.codice = NEW.codiceid AND account_codiceid.codice_azienda =
var_azienda_codice LIMIT 1
IF FOUND THEN