Обсуждение: BUG #5569: Select in trigger don't retrive true record
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
Hello your trigger is AFTER or BEFORE trigger? Because BEFORE trigger is executed before new value is propagated to table. Regards Pavel Stehule 2010/7/23 Alessio <a.burgassi@gmail.com>: > > The following bug has been logged online: > > Bug reference: =C2=A0 =C2=A0 =C2=A05569 > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Alessio > Email address: =C2=A0 =C2=A0 =C2=A0a.burgassi@gmail.com > PostgreSQL version: 8.4.3 build1400 > Operating system: =C2=A0 Windows 2008 Web > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Select in trigger don't retrive t= rue 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 =3D > false when the record exist! > if i execute a full analizy and vacum the problem is solved. > > var_azienda_schema :=3D 'azienda_'|| var_azienda_codice; > rec:=3DNULL; > SELECT nspname INTO rec FROM pg_catalog.pg_namespace WHERE nspname =3D > var_azienda_schema LIMIT 1; > IF NOT FOUND THEN > =C2=A0-- SE NON TROVO LO SCHEMA ESCO > =C2=A0var_err:=3D var_trigger_name || ' ' || 'ERR4 schema '|| > COALESCE(CAST(var_azienda_schema as TEXT),'NULL') ||' inesistente. > CODICE=3D'|| NEW.codice; > =C2=A0INSERT INTO public.log(data, errore, pagina, priorita) VALUES ( NOW= (), > var_err, '', -1); > =C2=A0UPDATE public.chiamata_evento_temp SET data_elaborazione =3D NOW(),= note =3D > var_err WHERE codice =3D NEW.codice; > =C2=A0EXECUTE 'SET search_path =3D public'; > =C2=A0RETURN NEW; > END IF; > --CAMBIO SCHEMA > EXECUTE 'SET search_path =3D '||var_azienda_schema; > > ---------------------------------------------------------- > -- FIND INFO CODICEID > ---------------------------------------------------------- > -- SE codiceid E' SETTATO CERCO LE INFORMAZIONI > =C2=A0rec:=3DNULL; > =C2=A0SELECT > account_codiceid.codice_protocollo,account_codiceid.descrizione,account_c= odi > ceid.area,account_codiceid.codice, account_codiceid.codice_account AS > codice_account =C2=A0FROM public.account_codiceid INTO rec WHERE > account_codiceid.codice =3D NEW.codiceid AND account_codiceid.codice_azie= nda =3D > var_azienda_codice LIMIT 1 > IF FOUND THEN > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >