Thread: Constraint question
Hi All, I am currently using Postgres 8.1.2 and am in the process of restructuring our entire DB. The current structure - table "X" is the central/main table in the DB and has approx 100 tables which reference it in the form of foreign key constaints. I am now required the remove thousands of obsolete records from table "X" and even though I have removed all related rows in all related tables, the deletion of rows in table "X" is taking WAY TOO LONG. I am assuming that this is because the deletion of each row in table "X" requires that all the related tables(a number of them are huge) need to be first checked. Is there a way I can temporarily disable this checking and then re-enable it after the rows in table "X" are deleted. Thanks ENZO...
Enzo Daddario <enzo@pienetworks.com> writes: > I am now required the remove thousands of obsolete records from table > "X" and even though I have removed all related rows in all related > tables, the deletion of rows in table "X" is taking WAY TOO LONG. You need to put indexes on the referencing columns. Then start a fresh session for doing the actual deletes (FK check plans are usually cached per-session). regards, tom lane
On Sun, May 14, 2006 at 10:50:50PM -0400, Tom Lane wrote: > Enzo Daddario <enzo@pienetworks.com> writes: > > I am now required the remove thousands of obsolete records from table > > "X" and even though I have removed all related rows in all related > > tables, the deletion of rows in table "X" is taking WAY TOO LONG. > > You need to put indexes on the referencing columns. Then start a fresh > session for doing the actual deletes (FK check plans are usually cached > per-session). Would SET CONSTRAINTS ... DEFERRED not help, or does it still use the same machinery to do the checking, regardless of how much data there is to check? -- 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
"Jim C. Nasby" <jnasby@pervasive.com> writes: > On Sun, May 14, 2006 at 10:50:50PM -0400, Tom Lane wrote: >> Enzo Daddario <enzo@pienetworks.com> writes: >>> I am now required the remove thousands of obsolete records from table >>> "X" and even though I have removed all related rows in all related >>> tables, the deletion of rows in table "X" is taking WAY TOO LONG. >> >> You need to put indexes on the referencing columns. Then start a fresh >> session for doing the actual deletes (FK check plans are usually cached >> per-session). > Would SET CONSTRAINTS ... DEFERRED not help, or does it still use the > same machinery to do the checking, regardless of how much data there is > to check? It's the same machinery. We've speculated about having the thing switch over to doing a full-table recheck (comparable to what ADD CONSTRAINT FOREIGN KEY does) once the number of pending individual row checks exceeds some threshold, but that's not done yet --- and it's not clear how to do it in a concurrent fashion, so don't hold your breath ... regards, tom lane
On Mon, May 15, 2006 at 11:17:31AM -0400, Tom Lane wrote: > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > On Sun, May 14, 2006 at 10:50:50PM -0400, Tom Lane wrote: > >> Enzo Daddario <enzo@pienetworks.com> writes: > >>> I am now required the remove thousands of obsolete records from table > >>> "X" and even though I have removed all related rows in all related > >>> tables, the deletion of rows in table "X" is taking WAY TOO LONG. > >> > >> You need to put indexes on the referencing columns. Then start a fresh > >> session for doing the actual deletes (FK check plans are usually cached > >> per-session). > > > Would SET CONSTRAINTS ... DEFERRED not help, or does it still use the > > same machinery to do the checking, regardless of how much data there is > > to check? > > It's the same machinery. We've speculated about having the thing switch > over to doing a full-table recheck (comparable to what ADD CONSTRAINT > FOREIGN KEY does) once the number of pending individual row checks > exceeds some threshold, but that's not done yet --- and it's not clear > how to do it in a concurrent fashion, so don't hold your breath ... I'm assuming that in order for deferred constraints to work we must have a list of what's changed... couldn't that list be fed into an appropriate query and then planned accordingly? This would allow the backend to use the best validation method possible; merge join, hash join, etc. If the number of changed rows is small, the planner would probably pick nested loop (I'm assuming that's roughly how the IMMEDIATE case works); if the number of changed rows is large it'd favor something else, but at least it wouldn't be re-checking the entire source table. -- 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