pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProcedure/trigger not working after upgrade from 9.5 to 11.7
7+ messages / 4 participants
[nested] [flat]
* Procedure/trigger not working after upgrade from 9.5 to 11.7
@ 2020-03-25 18:21 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
2020-03-25 18:40 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 jim schmidt <txherper@gmail.com>
2020-03-25 18:41 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 jim schmidt <txherper@gmail.com>
2020-03-25 18:56 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 7+ messages in thread
From: Erika Knihti-Van Driessche @ 2020-03-25 18:21 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
2020-03-25 18:21 Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
@ 2020-03-25 18:40 ` jim schmidt <txherper@gmail.com>
2020-03-25 18:41 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: jim schmidt @ 2020-03-25 18:40 UTC (permalink / raw)
To: Erika Knihti-Van Driessche <erika.knihti@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
2020-03-25 18:21 Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
2020-03-25 18:40 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 jim schmidt <txherper@gmail.com>
@ 2020-03-25 18:41 ` Erika Knihti-Van Driessche <erika.knihti@gmail.com>
2020-03-25 18:52 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Erika Knihti-Van Driessche @ 2020-03-25 18:41 UTC (permalink / raw)
To: jim schmidt <txherper@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
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 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
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
2020-03-25 18:21 Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
2020-03-25 18:40 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 jim schmidt <txherper@gmail.com>
2020-03-25 18:41 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
@ 2020-03-25 18:52 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2020-03-25 18:52 UTC (permalink / raw)
To: Erika Knihti-Van Driessche <erika.knihti@gmail.com>; +Cc: jim schmidt <txherper@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
2020-03-25 18:21 Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
@ 2020-03-25 18:41 ` jim schmidt <txherper@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: jim schmidt @ 2020-03-25 18:41 UTC (permalink / raw)
To: Erika Knihti-Van Driessche <erika.knihti@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
2020-03-25 18:21 Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
@ 2020-03-25 18:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-03-25 19:03 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2020-03-25 18:56 UTC (permalink / raw)
To: Erika Knihti-Van Driessche <erika.knihti@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
2020-03-25 18:21 Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
2020-03-25 18:56 ` Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-03-25 19:03 ` Erika Knihti-Van Driessche <erika.knihti@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Erika Knihti-Van Driessche @ 2020-03-25 19:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-03-25 19:03 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-25 18:21 Procedure/trigger not working after upgrade from 9.5 to 11.7 Erika Knihti-Van Driessche <erika.knihti@gmail.com>
2020-03-25 18:40 ` jim schmidt <txherper@gmail.com>
2020-03-25 18:41 ` Erika Knihti-Van Driessche <erika.knihti@gmail.com>
2020-03-25 18:52 ` David G. Johnston <david.g.johnston@gmail.com>
2020-03-25 18:41 ` jim schmidt <txherper@gmail.com>
2020-03-25 18:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-03-25 19:03 ` Erika Knihti-Van Driessche <erika.knihti@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox