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 :-)
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 ...