Re: Schema/Trigger help

"Cody Konior" <cody.konior@reynolds.com.au> writes:
> I've pasted the whole thing because I don't know how much is important.  The
> problem is that it seems whenever we do the INSERT, the trigger causes an
> error because it says parts_purchasing table doesn't exist.  Naturally... it
> does exist!

The three standard answers for this type of problem are:

1. case-folding mismatch (you quoted a mixed-case name when creating the
table and tried to reference it without quotes, or vice versa);

2. wrong schema search path;

3. obsolete cached plan.

It sounds like you already eliminated #2, and if the entire example is
shown exactly then it's not #1 either.  #3 could be eliminated by
starting a fresh database session.

The real question in my mind is how this code could've ever worked at
all, though.  The trigger creates a fresh update event (maybe more than
one) on its own table every time through, which will fire the same
trigger again, which means that this absolutely *should* be an infinite
loop.  The only way it isn't is if the "parts_purchasing" table affected
by its UPDATE isn't the same one the trigger itself is attached to.
So I'm thinking there probably is a schema search path issue hidden
in here somewhere, but you've not given us enough information to
understand what is supposed to be happening.

            regards, tom lane