From: Tom Lane <tgl@sss.pgh.pa.us>
To: Erika Knihti-Van Driessche <erika.knihti@gmail.com>
cc: pgsql-admin@lists.postgresql.org
Subject: Re: Procedure/trigger not working after upgrade from 9.5 to 11.7
In-reply-to: 
 <CALgXhV-US4TVL3RCG2EARDZVtVRkSP9L5pcwKuPJ+GTELSmp8A@mail.gmail.com>
References: 
 <CALgXhV-US4TVL3RCG2EARDZVtVRkSP9L5pcwKuPJ+GTELSmp8A@mail.gmail.com>
Comments: In-reply-to Erika Knihti-Van Driessche <erika.knihti@gmail.com>
	message dated "Wed, 25 Mar 2020 19:21:34 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <9391.1585162597.1@sss.pgh.pa.us>
Date: Wed, 25 Mar 2020 14:56:37 -0400
Message-ID: <9392.1585162597@sss.pgh.pa.us>
Precedence: bulk

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