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