Thread: BUG #5569: Select in trigger don't retrive true record

BUG #5569: Select in trigger don't retrive true record

From
"Alessio"
Date:
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

Re: BUG #5569: Select in trigger don't retrive true record

From
Pavel Stehule
Date:
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
>