Обсуждение: Procedure/trigger not working after upgrade from 9.5 to 11.7
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
Old is not assigned on inserts.
Is this occurring on updates?
On Wed, Mar 25, 2020, 1:22 PM Erika Knihti-Van Driessche <erika.knihti@gmail.com> wrote:
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 IFHere'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 usedpg_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
IF TG_OP = 'UPDATE' THEN -- some code involving OLD
ELSE -- other code
Old is not assigned on inserts.On Wed, Mar 25, 2020, 1:22 PM Erika Knihti-Van Driessche <erika.knihti@gmail.com> wrote:
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 IFHere'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 usedpg_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
Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
От
Erika Knihti-Van Driessche
Дата:
Hi,
Is this something new that came after 9.5, because it DID work on 9.5.. So far oly noticed on inserts.
Thanks,
erika
erika
On Wed, 25 Mar 2020 at 19:40, jim schmidt <txherper@gmail.com> wrote:
Old is not assigned on inserts.Is this occurring on updates?On Wed, Mar 25, 2020, 1:22 PM Erika Knihti-Van Driessche <erika.knihti@gmail.com> wrote: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 IFHere'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 usedpg_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
On Wednesday, March 25, 2020, Erika Knihti-Van Driessche <erika.knihti@gmail.com> wrote:
Hi,Is this something new that came after 9.5, because it DID work on 9.5.. So far oly noticed on inserts.
Version 11 release notes include a seemingly pertinent compatibility break.
David J.
Erika Knihti-Van Driessche <erika.knihti@gmail.com> writes:
> 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
This is unsurprising in an ON-INSERT trigger, because the OLD record
isn't defined. It looks like this code was hoping to dodge that
problem with
> Declare
> EREC fax_finance_employer%ROWTYPE;
> ...
> If TG_OP = 'INSERT' THEN
> OLD = EREC;
> END IF;
However, if you never assigned any value to the EREC variable, it's
not defined either, so I don't think this helps. There were some
behavioral changes in this area in plpgsql v11, so it looks like you
got bit by a corner case there. (Specifically, there's now a
difference between a null value and a row-of-nulls value for variables
of named composite types, as well as records, whereas before v11
record variables distinguished those cases while named-composite
variables didn't.) Maybe there's something there we should fix,
but even if we do it won't help you immediately.
I think the most reliable way to fix this is to recast the trigger
so that it does not attempt to access OLD if TG_OP indicates that
that's not relevant. So you'd need something like
IF TG_OP != 'INSERT' THEN
if OLD.Ffa_Id Is Null Then
...
regards, tom lane
Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
От
Erika Knihti-Van Driessche
Дата:
Hi Tom, group,
Thanks so much for your fast and great answer! I'll see what we can do, I don't know who wrote that piece of code, probably ages ago.. but I hope this workaround does it (and that I can reach a colleague tomorrow with some more plsql knowledge :-)
Take care!
Erika
On Wed, 25 Mar 2020 at 19:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Erika Knihti-Van Driessche <erika.knihti@gmail.com> writes:
> 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
This is unsurprising in an ON-INSERT trigger, because the OLD record
isn't defined. It looks like this code was hoping to dodge that
problem with
> Declare
> EREC fax_finance_employer%ROWTYPE;
> ...
> If TG_OP = 'INSERT' THEN
> OLD = EREC;
> END IF;
However, if you never assigned any value to the EREC variable, it's
not defined either, so I don't think this helps. There were some
behavioral changes in this area in plpgsql v11, so it looks like you
got bit by a corner case there. (Specifically, there's now a
difference between a null value and a row-of-nulls value for variables
of named composite types, as well as records, whereas before v11
record variables distinguished those cases while named-composite
variables didn't.) Maybe there's something there we should fix,
but even if we do it won't help you immediately.
I think the most reliable way to fix this is to recast the trigger
so that it does not attempt to access OLD if TG_OP indicates that
that's not relevant. So you'd need something like
IF TG_OP != 'INSERT' THEN
if OLD.Ffa_Id Is Null Then
...
regards, tom lane