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

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(dot)burgassi(at)gmail(dot)com>:
>
> The following bug has been logged online:
>
> Bug reference:      5569
> Logged by:          Alessio
> Email address:      a(dot)burgassi(at)gmail(dot)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
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>