Thread: Problem with foreign keys and inheritance
[Version: CVS as of yesterday] When I create a table that inherits from another table that uses foreign keys, I get something like this: ERROR: cache lookup of attribute 10 in relation 124171 failed This is happening in get_attribute_name() of backend/utils/adt/ruleutils.c -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "For the LORD is good; his mercy is everlasting; and his truth endureth to all generations." Psalms 100:5
"Oliver Elphick" wrote: >When I create a table that inherits from another table that uses foreign >keys, I get somethinglike this: > > ERROR: cache lookup of attribute 10 in relation 124171 failed > >This is happening in get_attribute_name()of backend/utils/adt/ruleutils.c I'm still trying to track this down; it seems to be happening when the backend is trying to fetch details of the ancestor class, in deparse_expression(). However, I cannot find relation 124171; is there any way to find out where a relation is, given only its oid? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "For I know that my redeemer liveth, and that he shall stand at the latter day upon the earth" Job 19:25
Oliver Elphick wrote: > > However, I cannot find relation 124171; is there any way to find out > where a relation is, given only its oid? This might give you a pretty good hint... select * from pg_attribute where attrelid = 124171; Cheers, Ed Loehr
Ed Loehr wrote: >Oliver Elphick wrote: >> >> However, I cannot find relation 124171; is there any way to find out >> wherea relation is, given only its oid? > >This might give you a pretty good hint... > > select * from pg_attribute whereattrelid = 124171; Nothing. I tried looking for the oid in every system table listed by \dS - no joy :-( -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "For I know that my redeemer liveth, and that he shall stand at the latter day upon the earth" Job 19:25
"Oliver Elphick" <olly@lfix.co.uk> writes:
>>> However, I cannot find relation 124171; is there any way to find out
>>> where a relation is, given only its oid?
>>
>> This might give you a pretty good hint...
>>
>> select * from pg_attribute where attrelid = 124171;
Actually, "select * from pg_class where oid = 124171" is the canonical
answer; if that doesn't produce anything, you have no such table.
> I tried looking for the oid in every system table listed by \dS - no joy :-(
Is it possible that you dropped the table in question since that try?
If you recreated it, it wouldn't have the same OID the second time.
Another possibility is that the rule dumper is picking up a completely
wrong number for some reason. I thought that code was pretty solid by
now, but it might still have some glitches left.
If you provided an SQL script that reproduces the problem, more people
might be motivated to look for it...
regards, tom lane
"Oliver Elphick" wrote: >[Version: CVS as of yesterday] >When I create a table that inherits from another table that
usesforeign >keys, I get something like this: > > ERROR: cache lookup of attribute 10 in relation 124171 failed >
>Thisis happening in get_attribute_name() of backend/utils/adt/ruleutils.c
Here is an SQL script that makes this happen:
========================================================
create database newj with encoding = 'SQL_ASCII';
\connect newj
create table person
( id char(10) primary key, name text not null, address
int, salutation text default 'Dear Sir', envelope text, email
text, www text
);
create table individual
( gender char(1) check (gender = 'M' or gender = 'F'
or gender is null), born datetime check ((born >= '1 Jan 1880'
and born <= 'today') or born is null), surname text, forenames text, title
text, old_surname text, mobile text, ni_no text, constraint
is_namedcheck (not (surname isnull and forenames isnull))
) inherits (person);
create table organisation
( contact char(10) references individual (id) match full, structure char(1)
check(structure='L' or structure='C' or structure='U' or structure='O')
) inherits (person);
create table customer
( acs_code char(8), acs_addr int, class char(1) default '',
type char(2), area char(2), country char(2), vat_class char(1),
vat_number char(12), discount numeric(6,3) check (discount >= -50.0::numeric(6,3)
and discount <= 50.0)::numeric(6,3), commission bool default 'f',
status char(1) default '', deliver_to int, factor_code text
) inherits (organisation);
========================================================
Table customer does not get created; instead, I get:
ERROR: cache lookup of attribute 10 in relation <some_oid> failed
--
"Oliver Elphick" <olly@lfix.co.uk> writes:
>> [Version: CVS as of yesterday]
>> When I create a table that inherits from another table that uses foreign
>> keys, I get something like this:
>>
>> ERROR: cache lookup of attribute 10 in relation 124171 failed
Ah, I see it. It's got nothing to do with foreign keys, just inherited
constraints. We're trying to deparse the inherited constraint
expressions at a time that the relation-in-process-of-being-created
isn't yet officially visible. So trying to look up its attributes is
failing. Need another CommandCounterIncrement() in there to make it
work.
This must have been busted for a good while, I think. I rewrote that
module months ago and probably broke it then. Probably should add
a regress test case that uses inherited constraints...
regards, tom lane