From: Andrew Dunstan <andrew@dunslane.net>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Decibel! <decibel@decibel.org>
Cc: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-hackers@postgresql.org
Subject: Re: pg_dump restore time and Foreign Keys
Date: Mon, 09 Jun 2008 11:23:19 -0400
Message-ID: <484D4AE7.2050209@dunslane.net> (raw)
In-Reply-To: <1213024379.12046.111.camel@ebony.site>
References: <1212647003.19964.19.camel@ebony.site>
<4847D4A7.70309@dunslane.net>
<1212670595.19964.55.camel@ebony.site>
<200806071308.00845.xzilla@users.sourceforge.net>
<1212860515.12046.78.camel@ebony.site>
<484ADAD4.2080701@dunslane.net>
<E2AE91BB-0FCE-44CE-AC3B-200D6F1DD0BC@decibel.org>
<19362.1213023432@sss.pgh.pa.us>
<1213024379.12046.111.camel@ebony.site>
Simon Riggs wrote:
> On Mon, 2008-06-09 at 10:57 -0400, Tom Lane wrote:
>
>> Decibel! <decibel@decibel.org> writes:
>>
>>> Actually, in the interest of stating the problem and not the
>>> solution, what we need is a way to add FKs that doesn't lock
>>> everything up to perform the key checks.
>>>
>> Ah, finally a useful comment. I think it might be possible to do an
>> "add FK concurrently" type of command that would take exclusive lock
>> for just long enough to add the triggers, then scan the tables with just
>> AccessShareLock to see if the existing rows meet the constraint, and
>> if so finally mark the constraint "valid". Meanwhile the constraint
>> would be enforced against newly-added rows by the triggers, so nothing
>> gets missed. You'd still get a small hiccup in system performance
>> from the transient exclusive lock, but nothing like as bad as it is
>> now. Would that solve your problem?
>>
>
> That's good, but it doesn't solve the original user complaint about
> needing to re-run many, many large queries to which we already know the
> answer.
>
>
But we don't know it for dead sure, we only think we do. What if the
data for one or other of the tables is corrupted? We'll end up with data
we believe is consistent but in fact is not, ISTM. If you can somehow
guarantee the integrity of data in both tables then we might be
justified in assuming that the FK constraint will be consistent - that's
why I suggested some sort of checksum mechanism might serve the purpose.
cheers
andrew
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andrew@dunslane.net, simon@2ndquadrant.com, tgl@sss.pgh.pa.us, decibel@decibel.org, xzilla@users.sourceforge.net
Subject: Re: pg_dump restore time and Foreign Keys
In-Reply-To: <484D4AE7.2050209@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox