Procedure/trigger not working after upgrade from 9.5 to 11.7 - Mailing list pgsql-admin
| From | Erika Knihti-Van Driessche |
|---|---|
| Subject | Procedure/trigger not working after upgrade from 9.5 to 11.7 |
| Date | |
| Msg-id | CALgXhV-US4TVL3RCG2EARDZVtVRkSP9L5pcwKuPJ+GTELSmp8A@mail.gmail.com Whole thread Raw |
| Responses |
Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
(jim schmidt <txherper@gmail.com>)
Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 (jim schmidt <txherper@gmail.com>) Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | 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
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;
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
pgsql-admin by date: