Procedure/trigger not working after upgrade from 9.5 to 11.7

From: Erika Knihti-Van Driessche <erika(dot)knihti(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Procedure/trigger not working after upgrade from 9.5 to 11.7
Date: 2020-03-25 18:21:34
Message-ID: CALgXhV-US4TVL3RCG2EARDZVtVRkSP9L5pcwKuPJ+GTELSmp8A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

Hope I'm in the correct mail group.. I don't have developer background, and
after upgrade from 9.5 to 11.7 (RHEL7) the users complain about a trigger
not working. Here's the error I find in logs:

ERROR: record "old" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: SQL statement "SELECT OLD.Ffa_Id Is Null"
PL/pgSQL function pg_fct_bus_fax_finance_employer() line 20 at IF

Here's part of the procedure (where it already gives the error)

Declare

Tra_Id Worker.Tra_Id%Type;

Tra_DateEnregDomTVA Worker.Tra_DateEnregDomTVA%Type;

Exercice Alloc_compl_vac_worker.Acv_Exercice%Type;

CodeLangue Worker.Tra_Langue%Type;

r RECORD;

r2 RECORD;

r3 RECORD;

EREC fax_finance_employer%ROWTYPE;

Begin

SET search_path = fs;

If TG_OP = 'INSERT' THEN

OLD = EREC;

END IF;

-- if the number is not specified, then it is automatically provided by
the system

if OLD.Ffa_Id Is Null Then -- Creation

if NEW.Ffa_NoPayement Is Null Then

Exercice := EXTRACT(YEAR FROM NEW.FFA_DATEGENERATION);

if NEW.DAN_ID is not null then

NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice, 'RJA');

end if;

if NEW.DDT_ID is not null then

NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice, 'RDT');

end if;

if NEW.CFS_ID is not null then

NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice, 'RFS');

end if;

if NEW.NDA_ID is not null then

NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice, 'AST');

end if;

If NEW.Ffa_NoPayement is null Then

RAISE EXCEPTION '-20068|There is no more number available...';

End If;

End If;

End IF;

I know it's just a part, but I was hoping someone could immediately say
AHAA :-)

For the upgrade, I used
pg_dumpall -g (first only globals)
psql (import globals)
pg_dumpall (dump all)
psql (import all)

No errors received during dump/import.

Thanks for your help & support!
Erika

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message jim schmidt 2020-03-25 18:40:01 Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
Previous Message Dhandapani Shanmugam 2020-03-25 16:47:11 Migration Help.