Skip site navigation (1) Skip section navigation (2)

Re: Schema/Trigger help

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Cody Konior" <cody(dot)konior(at)reynolds(dot)com(dot)au>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Schema/Trigger help
Date: 2008-05-05 14:08:25
Message-ID: 28515.1209996505@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"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

In response to

pgsql-novice by date

Next:From: John GuntherDate: 2008-05-05 14:53:31
Subject: why am I told "subquery must return only one column"
Previous:From: Ken AllenDate: 2008-05-05 12:05:17
Subject: SQl Server Linked Server-Cant write to table with OID

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group