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

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?
Removing the fk constraint would resolve the problem?

Thanks!!

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 8/2/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

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 8/4/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
>
> 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).
>

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.


On 8/21/06, Jim Nasby <jnasby@pervasive.com> wrote:
>
> 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
>
>
>

"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