Обсуждение: 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