referential integrity violation - key referenced from xxx not found in yyy

On Wed, 2 Aug 2006, Luiz Henrique wrote:

Hi,

I'm running Postgresql 7.3 in Debian Woody OS.

After some time working properly, insert's statements related this error:

----
referential integrity violation - key referenced from xxx not found in yyy
----

This error would be normal, if the key really doesn't exist, but the key
exist.
The insert's work some times, and some times don't.

Is there any file corrupted?

It's hard to say. I'd have expected that a select * from parent where
key=value would fail for both the fk check and for a check to see if the
key exists assuming they used the same plan. Can you make a standalone
test case?

Removing the fk constraint would resolve the problem?

Well, it'd stop the message, but if there is corruption, it wouldn't fix
that.

Hi, could you tell me how postgresql look for a referenced key? It looks in
table index? Maybe only the index is corrupted?

Thanks!

On Fri, 4 Aug 2006, Luiz Henrique wrote:

Hi, could you tell me how postgresql look for a referenced key? It looks in
table index? Maybe only the index is corrupted?

It basically runs a query like:
SELECT * FROM parenttable WHERE keycol1 = ? [AND keycol2 = ? ...] FOR
SHARE

It should act like a prepared statement would (which might be different
than it would with the ?s replaced by the actual values).

Hi,

my problem was problably corrupted index files. I recreated the index and
it's ok now. The hardware was being unpluged from power source without
shuthing down, I guess that's the cause.
I used the fallowing commands to recreate index:

/etc/init.d/postgresql stop
su postgres
/usr/lib/postgresql/bin/postgres -D /var/lib/postgres/data -O -P db
reindex database db;
/etc/init.d/postgresql start

Thanks!

On Aug 15, 2006, at 8:50 AM, Luiz Henrique wrote:

my problem was problably corrupted index files. I recreated the
index and it's ok now. The hardware was being unpluged from power
source without shuthing down, I guess that's the cause.

What version are you using? In recent versions, indexes are supposed
to be safe from corruption, even through crashes.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

It's Postgresql 7.2.1, installed with stable version of Debian Woody, at the
time.

"Luiz Henrique" <luizhwk@gmail.com> writes:

On 8/21/06, Jim Nasby <jnasby@pervasive.com> wrote:

What version are you using? In recent versions, indexes are supposed
to be safe from corruption, even through crashes.

It's Postgresql 7.2.1, installed with stable version of Debian Woody, at the
time.

7.2.1 is ancient and has a very long list of known bugs. We officially
abandoned support for the 7.2.* branch more than a year ago, upon
finding some bugs that were simply not practical to fix in that branch.

If you must keep using 7.2.*, at least get yourself onto 7.2.8, the last
of that series. But you'd be doing yourself a real favor by expending
a little more effort and moving to a current release series ---
preferably 8.1.*.

regards, tom lane