Odd error with FK referencing another FK column? (was Re: java.sql.SQLException: ERROR: Relation 38868974 does not exist)

Alain M. Gaudrault wrote:
> We had a foreign key which was referencing a column in table which
> itself was a foreign key (something that was new to our system). We've
> made mods to prevent the two levels of foreign keys, and so far, so
> good. Not sure how this would explain the behaviour we were seeing,
> though, do you have any idea?

Not unless you'd dropped/recreated the second table. I'd expect a
foreign-key trigger function to use the OID of the referenced table.

I've taken the liberty of cc-ing the list on this with a changed
subject, since someone might have a better idea than me.

> Richard Huxton wrote:
>
>> Alain M. Gaudrault wrote:
>>
>>> I've come across a problem which is only happening intermittently,
>>> and having a difficult time finding information to help me determine
>>> what is the source of my frustration.
>>>
>>> The symptom is the error reported in the subject. We invoke SQL
>>> statements from a Java servlet via pg74jdbc3.jar. The relation
>>> number in the error will be the same for several occurrences, then
>>> change.
>>
>> At a guess, something's OID is being cached - probably that of a
>> TEMPORARY TABLE. If you have a plpgsql function (or perhaps a PREPAREd
>> query) that refers to an object and that object is dropped (usually a
>> temporary table) then you can get errors like this.

--
Richard Huxton
Archonet Ltd