Re: [BUGS] referential integrity violation - key referenced from

#1

On Wed, 2 Aug 2006, Luiz Henrique wrote:

Quote:

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?

Quote:

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

#2

------=_Part_69661_3818596.1154701804817
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 (AT) megazone (DOT) bigpanda.com> wrote:

Quote:

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

------=_Part_69661_3818596.1154701804817
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi, could you tell me how postgresql look for a referenced key? It looks in table index? Maybe only the index is corrupted? Thanks! <div>On 8/2/06, Stephan Szabo
 <<a href="mailto:sszabo (AT) megazone (DOT) bigpanda.com">sszabo (AT) megazone (DOT) bigpanda.com</a>> wrote:<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
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: >

Quote:

---- > 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.
br>> > > 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 </blockquote></div> 

------=_Part_69661_3818596.1154701804817--

#3

On Fri, 4 Aug 2006, Luiz Henrique wrote:

Quote:

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).

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#4

------=_Part_66406_2036689.1155649829699
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 (AT) megazone (DOT) bigpanda.com> wrote:

Quote:

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).

------=_Part_66406_2036689.1155649829699
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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! <div>On 8/4/06, Stephan Szabo <<a href="mailto:sszabo (AT) megazone (DOT) bigpanda.com">sszabo (AT) megazone (DOT) bigpanda.com</a>> wrote:<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
 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).
 </blockquote></div> 

------=_Part_66406_2036689.1155649829699--

#5

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

Quote:

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 (AT) pervasive (DOT) com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

#6

------=_Part_45796_23507524.1156245688851
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

On 8/21/06, Jim Nasby <jnasby (AT) pervasive (DOT) com> wrote:

Quote:

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 (AT) pervasive (DOT) com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

------=_Part_45796_23507524.1156245688851
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

It's Postgresql 7.2.1, installed with stable version of Debian Woody, at the time. <div>On 8/21/06, Jim Nasby <<a href="mailto:jnasby (AT) pervasive (DOT) com">jnasby (AT) pervasive (DOT) com
</a>> wrote:<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">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      <a href="mailto:jnasby (AT) pervasive (DOT) com">jnasby (AT) pervasive (DOT) com</a> Pervasive Software      <a href="http://pervasive.com">http://pervasive.com</a>    work: 512-231-6117
 vcard: <a href="http://jim.nasby.net/pervasive.vcf">http://jim.nasby.net/pervasive.vcf</a>       cell: 512-569-9461 </blockquote></div> 

------=_Part_45796_23507524.1156245688851--

#7

"Luiz Henrique" <luizhwk (AT) gmail (DOT) com> writes:

Quote:

On 8/21/06, Jim Nasby <jnasby (AT) pervasive (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] referential integrity violation - key referenced from

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] referential integrity violation - key referenced from - 08-02-2006 , 11:16 AM

Re: [BUGS] referential integrity violation - key referenced from - 08-04-2006 , 09:32 AM

Re: [BUGS] referential integrity violation - key referenced from - 08-04-2006 , 11:21 AM

Re: [BUGS] referential integrity violation - key referenced from - 08-15-2006 , 08:53 AM

Re: [BUGS] referential integrity violation - key referenced from - 08-22-2006 , 04:26 AM

Re: [BUGS] referential integrity violation - key referenced from - 08-22-2006 , 01:38 PM

Re: [BUGS] referential integrity violation - key referenced from - 08-22-2006 , 04:23 PM