From: | Erika Knihti-Van Driessche <erika(dot)knihti(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 |
Date: | 2020-03-25 19:03:11 |
Message-ID: | CALgXhV-6GuMqGVeUsqoBn794gb7gM+V8z2hrUK_Fi52E5Pod-A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Erika Knihti-Van Driessche <erika(dot)knihti(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2020-03-26 01:43:36 | Re: PG12 autovac issues |
Previous Message | Tom Lane | 2020-03-25 18:56:37 | Re: Procedure/trigger not working after upgrade from 9.5 to 11.7 |