Thread: URGENT: referential integrity problem
Hi, I have 2 tables, in the first one I have field that points to the table key from the second. I the forst I have ~ 1M records and in the second 100K. I start one update over the first table on anoder field ( not on the reference field). After few min. pg drops with: ERROR: <unnamed> referential integrity violation - key referenced from a_table1 not found in a_table2 ! My questions: How is it possible, that pg do not check the references by inserts? How can I check the db integrity for all tables at once ( I need to be sure, that do not exists any problems. It is production server). regards, ivan.
On Wednesday 29 Jan 2003 12:18 pm, pginfo wrote: > I have 2 tables, in the first one I have field that points to the table > key from the second. [snip] > After few min. pg drops with: > ERROR: <unnamed> referential integrity violation - key referenced from > a_table1 not found in a_table2 ! > > My questions: > How is it possible, that pg do not check the references by inserts? Might be a damaged index - try a REINDEX (specified in the SQL reference manual). I'd also run some tests against your hardware, make sure you don't have any memory or disk problems. > How can I check the db integrity for all tables at once ( I need to be > sure, that do not exists any problems. It is production server). Simplest way I can think of would be to pg_dump and restore to a test installation. -- Richard Huxton
Richard Huxton wrote: > On Wednesday 29 Jan 2003 12:18 pm, pginfo wrote: > > I have 2 tables, in the first one I have field that points to the table > > key from the second. > [snip] > > After few min. pg drops with: > > ERROR: <unnamed> referential integrity violation - key referenced from > > a_table1 not found in a_table2 ! > > > > My questions: > > How is it possible, that pg do not check the references by inserts? > > Might be a damaged index - try a REINDEX (specified in the SQL reference > manual). I'd also run some tests against your hardware, make sure you don't > have any memory or disk problems. No, I do not have any hardware/memory problems and it is not damaged index.In my forst table I can execute: select T1.ref_field from table1 T1 where T1.ref_field = 'A1'; and it returns 10 records. by executing select T2.id from table2 T2 where T2.id = 'A1'; and it returns 0 rows ! Also T1.ref_field points to T2.id and T2.id is the key for T2. Also T1.ref_field and T2.id are declared as name. > > > > How can I check the db integrity for all tables at once ( I need to be > > sure, that do not exists any problems. It is production server). > > Simplest way I can think of would be to pg_dump and restore to a test > installation. I tryed it many times and pg do not report any errors. My version is 7.3.1 running on r.h. 7.3. I execute : pg_dump > myfile Then create a new db. And execute psql -q newdb < myfile. regards, ivan. > > > -- > Richard Huxton
On Wed, 29 Jan 2003, pginfo wrote: > Hi, > > I have 2 tables, in the first one I have field that points to the table > key from the second. > > I the forst I have ~ 1M records and in the second 100K. > > I start one update over the first table on anoder field ( not on the > reference field). > > After few min. pg drops with: > ERROR: <unnamed> referential integrity violation - key referenced from > a_table1 not found in a_table2 ! > > My questions: > > How is it possible, that pg do not check the references by inserts? It could be a bug, or an old bug if you've upgraded the machine (it doesn't recheck the constraints in most version on dump/restore). Without more information though, I don't think it's possible to speculate. > How can I check the db integrity for all tables at once ( I need to be > sure, that do not exists any problems. It is production server). Try something like: select * from fktable where fcol1 is not null [and ...] and not exists (select * from pktable where pktable.pcol1 = fktable.fcol1 [and ...]); I think that'll find match unspecified cases. For match full, it'd miss partially null fktable rows, but it doesn't sound like that's your problem.
Stephan Szabo wrote: > On Wed, 29 Jan 2003, pginfo wrote: > > > Hi, > > > > I have 2 tables, in the first one I have field that points to the table > > key from the second. > > > > I the forst I have ~ 1M records and in the second 100K. > > > > I start one update over the first table on anoder field ( not on the > > reference field). > > > > After few min. pg drops with: > > ERROR: <unnamed> referential integrity violation - key referenced from > > a_table1 not found in a_table2 ! > > > > My questions: > > > > How is it possible, that pg do not check the references by inserts? > > It could be a bug, or an old bug if you've upgraded the machine (it > doesn't recheck the constraints in most version on dump/restore). > > Without more information though, I don't think it's possible to speculate. > It is possible.The project started on pg 7.2.1. But we executed many times dump/restore to migrate to the new versions. > > How can I check the db integrity for all tables at once ( I need to be > > sure, that do not exists any problems. It is production server). > > Try something like: > select * from fktable where fcol1 is not null [and ...] and not exists > (select * from pktable where pktable.pcol1 = fktable.fcol1 [and ...]); > > I think that'll find match unspecified cases. For match full, it'd miss > partially null fktable rows, but it doesn't sound like that's your > problem. I wrote the sql for checking this table ( with this sql code I fond the problem), but I am not sure for all other tables (in case pg do not check the integrity by dump/restore) and I will to write script ot commandfor checking all my tables and references. regards, ivan.
On Wed, 29 Jan 2003, pginfo wrote: > Stephan Szabo wrote: > > > On Wed, 29 Jan 2003, pginfo wrote: > > > > > Hi, > > > > > > I have 2 tables, in the first one I have field that points to the table > > > key from the second. > > > > > > I the forst I have ~ 1M records and in the second 100K. > > > > > > I start one update over the first table on anoder field ( not on the > > > reference field). > > > > > > After few min. pg drops with: > > > ERROR: <unnamed> referential integrity violation - key referenced from > > > a_table1 not found in a_table2 ! > > > > > > My questions: > > > > > > How is it possible, that pg do not check the references by inserts? > > > > It could be a bug, or an old bug if you've upgraded the machine (it > > doesn't recheck the constraints in most version on dump/restore). > > > > Without more information though, I don't think it's possible to speculate. > > > > It is possible.The project started on pg 7.2.1. > But we executed many times dump/restore to migrate to the new versions. If you still have the dump you last used to import to 7.3 you might want to see if a new load of that data has integrity problems. In any case, since most of the bugs that I know about went the other direction (disallowing valid changes), I'm still interested in seeing if we can find out what happened. The table definitions for both tables and the constraint definition would be useful, and if you happen to know whether you were likely to have changed the pk row before inserting the fk rows or if you had the fk rows and then changed the pk row since that'd narrow down the possible places to look.
Stephan Szabo wrote: > On Wed, 29 Jan 2003, pginfo wrote: > > > Stephan Szabo wrote: > > > > > On Wed, 29 Jan 2003, pginfo wrote: > > > > > > > Hi, > > > > > > > > I have 2 tables, in the first one I have field that points to the table > > > > key from the second. > > > > > > > > I the forst I have ~ 1M records and in the second 100K. > > > > > > > > I start one update over the first table on anoder field ( not on the > > > > reference field). > > > > > > > > After few min. pg drops with: > > > > ERROR: <unnamed> referential integrity violation - key referenced from > > > > a_table1 not found in a_table2 ! > > > > > > > > My questions: > > > > > > > > How is it possible, that pg do not check the references by inserts? > > > > > > It could be a bug, or an old bug if you've upgraded the machine (it > > > doesn't recheck the constraints in most version on dump/restore). > > > > > > Without more information though, I don't think it's possible to speculate. > > > > > > > It is possible.The project started on pg 7.2.1. > > But we executed many times dump/restore to migrate to the new versions. > > If you still have the dump you last used to import to 7.3 you might want > to see if a new load of that data has integrity problems. As I checked the problem exists also in the dump from 7.2.3 ( before it was on 7.2.1).I have this file backedup. By importing in 7.3.1 the pg do not reported errors. Only for example: If I import data in oracle after importin it start integrity check and only if no errors exeists it commits data. And I was very supprised that pg do not check this thinks. > In any case, > since most of the bugs that I know about went the other direction > (disallowing valid changes), I'm still interested in seeing if we can find > out what happened. The table definitions for both tables and the > constraint definition would be useful, and if you happen to know whether > you were likely to have changed the pk row before inserting the fk rows or > if you had the fk rows and then changed the pk row since that'd narrow > down the possible places to look. I have also interest to detect and fix the problem. We plane ( and did it) to use pg for many projects and we need stable db. In case you are interested I can give you access to the test server and you will be able to see the problem alone (It will take a little time to configure the server, but it is not problem). In any case I have interest first to fix the problem and second to fix the data. regards, ivan.
On Wed, 29 Jan 2003, pginfo wrote: > Stephan Szabo wrote: > > > If you still have the dump you last used to import to 7.3 you might want > > to see if a new load of that data has integrity problems. > > As I checked the problem exists also in the dump from 7.2.3 ( before it was on > 7.2.1).I have this file backedup. > By importing in 7.3.1 the pg do not reported errors. > Only for example: > If I import data in oracle after importin it start integrity check and only if no > errors exeists it > commits data. And I was very supprised that pg do not check this thinks. It's a speed of load issue. I think 7.3's dumps use alter table and will check. Probably it'll eventually become optional. > > In any case, > > since most of the bugs that I know about went the other direction > > (disallowing valid changes), I'm still interested in seeing if we can find > > out what happened. The table definitions for both tables and the > > constraint definition would be useful, and if you happen to know whether > > you were likely to have changed the pk row before inserting the fk rows or > > if you had the fk rows and then changed the pk row since that'd narrow > > down the possible places to look. > > I have also interest to detect and fix the problem. We plane ( and did it) to > use pg for many projects and we need stable db. > > In case you are interested I can give you access to the test server and you will > be able to see > the problem alone (It will take a little time to configure the server, but it is > not problem). > > In any case I have interest first to fix the problem and second to fix the data. Well, the problem is that if it's in the dump then we've probably lost any of the information to track down what happened from the data (actually, probably vacuum would have destroyed it as well anyway, but...) but with a little info I can at least try to go over the code. The schema portion of the dump and any info on your usage patterns for modifying the tables would probably help (you can send it to just me if you don't want it on list). Also, if you use any functions to modify the tables, there were some bugs that did get fixed between 7.2 and 7.3 regarding foreign keys in that case, although I'd thought that they all were of the variety of disallowing valid sequences.
Stephan Szabo wrote: > On Wed, 29 Jan 2003, pginfo wrote: > > > Stephan Szabo wrote: > > > > > If you still have the dump you last used to import to 7.3 you might want > > > to see if a new load of that data has integrity problems. > > > > As I checked the problem exists also in the dump from 7.2.3 ( before it was on > > 7.2.1).I have this file backedup. > > By importing in 7.3.1 the pg do not reported errors. > > Only for example: > > If I import data in oracle after importin it start integrity check and only if no > > errors exeists it > > commits data. And I was very supprised that pg do not check this thinks. > > It's a speed of load issue. I think 7.3's dumps use alter table and will > check. Probably it'll eventually become optional. Hmm, as default option pg_dump on 7.3.1 do not check for reference integrity.I do not know about any option for this case. Which is the option? If (for the same data ) on 7.3.1 I make pg_dump and after it import, pg do not report any errors, do not stop and inserts the same data. For me it is big problem (no mather if I spear time my import) that I am not sure for data integrity. Pls., if this option do not exists in 7.3 include it in dev. list for 7.4. I think it is very important. > > > > > In any case, > > > since most of the bugs that I know about went the other direction > > > (disallowing valid changes), I'm still interested in seeing if we can find > > > out what happened. The table definitions for both tables and the > > > constraint definition would be useful, and if you happen to know whether > > > you were likely to have changed the pk row before inserting the fk rows or > > > if you had the fk rows and then changed the pk row since that'd narrow > > > down the possible places to look. > > > > I have also interest to detect and fix the problem. We plane ( and did it) to > > use pg for many projects and we need stable db. > > > > In case you are interested I can give you access to the test server and you will > > be able to see > > the problem alone (It will take a little time to configure the server, but it is > > not problem). > > > > In any case I have interest first to fix the problem and second to fix the data. > > Well, the problem is that if it's in the dump then we've probably lost any > of the information to track down what happened from the data (actually, > probably vacuum would have destroyed it as well anyway, but...) but with a > little info I can at least try to go over the code. > > The schema portion of the dump and any info on your usage patterns for > modifying the tables would probably help (you can send it to just me if > you don't want it on list). Ok, I will send it to you. > Also, if you use any functions to modify the > tables, there were some bugs that did get fixed between 7.2 and 7.3 > regarding foreign keys in that case, although I'd thought that they all > were of the variety of disallowing valid sequences. No, we do not have any functions for table modify. regards, ivan
On Thu, 30 Jan 2003, pginfo wrote: > > > Stephan Szabo wrote: > > > On Wed, 29 Jan 2003, pginfo wrote: > > > > > Stephan Szabo wrote: > > > > > > > If you still have the dump you last used to import to 7.3 you might want > > > > to see if a new load of that data has integrity problems. > > > > > > As I checked the problem exists also in the dump from 7.2.3 ( before it was on > > > 7.2.1).I have this file backedup. > > > By importing in 7.3.1 the pg do not reported errors. > > > Only for example: > > > If I import data in oracle after importin it start integrity check and only if no > > > errors exeists it > > > commits data. And I was very supprised that pg do not check this thinks. > > > > It's a speed of load issue. I think 7.3's dumps use alter table and will > > check. Probably it'll eventually become optional. > > Hmm, as default option pg_dump on 7.3.1 do not check for reference integrity.I do not > know about any option for this case. > Which is the option? If you make a new constraint in 7.3, the dump from a 7.3 server should use ALTER TABLE to build the constraint. That will check the data. If you've imported a constraint from an earlier version it doesn't have the new constraint info and will still just dump them as triggers. You can make the constraint information for those triggers (I think someone made a script to do that) but I'm not 100% sure what's involved.
Stephan Szabo wrote: > On Thu, 30 Jan 2003, pginfo wrote: > > > > > > > Stephan Szabo wrote: > > > > > On Wed, 29 Jan 2003, pginfo wrote: > > > > > > > Stephan Szabo wrote: > > > > > > > > > If you still have the dump you last used to import to 7.3 you might want > > > > > to see if a new load of that data has integrity problems. > > > > > > > > As I checked the problem exists also in the dump from 7.2.3 ( before it was on > > > > 7.2.1).I have this file backedup. > > > > By importing in 7.3.1 the pg do not reported errors. > > > > Only for example: > > > > If I import data in oracle after importin it start integrity check and only if no > > > > errors exeists it > > > > commits data. And I was very supprised that pg do not check this thinks. > > > > > > It's a speed of load issue. I think 7.3's dumps use alter table and will > > > check. Probably it'll eventually become optional. > > > > Hmm, as default option pg_dump on 7.3.1 do not check for reference integrity.I do not > > know about any option for this case. > > Which is the option? > > If you make a new constraint in 7.3, the dump from a 7.3 server should use > ALTER TABLE to build the constraint. That will check the data. If you've > imported a constraint from an earlier version it doesn't have the new > constraint info and will still just dump them as triggers. You can make > the constraint information for those triggers (I think someone made a > script to do that) but I'm not 100% sure what's involved. > No, I do not make any new constraint. All constraints was declared early.As I understande 7.3 remember the new constraints and by dumping it make check for integrity only if this new constraints exists. Right? I will search archive for such info. I trayed for 'integrity', but do not find the needet. regards, ivan. > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
pginfo wrote: > Hi, > > I have 2 tables, in the first one I have field that points to the table > key from the second. > > I the forst I have ~ 1M records and in the second 100K. > > I start one update over the first table on anoder field ( not on the > reference field). > > After few min. pg drops with: > ERROR: <unnamed> referential integrity violation - key referenced from > a_table1 not found in a_table2 ! > Hi Ivan, I will try to help you with some common sense remarks because I am Postgresql newbie, and you stated you need help quick. If you are lucky a guru drops in. > My questions: > > How is it possible, that pg do not check the references by inserts? > Maybe the constraint (FK) was activated when the tables were populated already? Is this possible? > How can I check the db integrity for all tables at once ( I need to be > sure, that do not exists any problems. It is production server). I do not know how to check the whole database at once. AAFAIK it isn't possible. But if you want to check between 2 tables if the FK works, just make a query like: SELECT table1_FK_Field,bla,bla FROM table1 WHERE (table1_FK_Field NOT IN (SELECT PK_Field FROM table2)) something like that should work. I hope you solve the problem quickly. Good luck and regards, Erwin > regards, > ivan.