Re: Schema/Trigger help

"Cody Konior" <cody(dot)konior(at)reynolds(dot)com(dot)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