Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 283F69FA5BA for ; Thu, 18 Oct 2007 02:53:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 18758-01 for ; Thu, 18 Oct 2007 02:53:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from shrek.krogh.cc (2605ds1-ynoe.1.fullrate.dk [90.184.12.24]) by postgresql.org (Postfix) with ESMTP id B8F4F9FB47E for ; Thu, 18 Oct 2007 02:53:25 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by shrek.krogh.cc (Postfix) with ESMTP id C35F43BF28F for ; Thu, 18 Oct 2007 07:53:23 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at shrek.krogh.cc Received: from shrek.krogh.cc ([127.0.0.1]) by localhost (shrek.krogh.cc [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id dnDSCgGA-57I for ; Thu, 18 Oct 2007 07:53:14 +0200 (CEST) Received: from [90.184.13.233] (unknown [90.184.13.233]) by shrek.krogh.cc (Postfix) with ESMTP id 172F83BF28D for ; Thu, 18 Oct 2007 07:53:14 +0200 (CEST) Message-ID: <4716F4BE.8050809@krogh.cc> Date: Thu, 18 Oct 2007 07:53:02 +0200 From: Jesper Krogh User-Agent: Thunderbird 1.5.0.13 (X11/20070824) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Upgrade time, dump+restore trouble. References: <471619AD.9020903@krogh.cc> <7031.1192637434@sss.pgh.pa.us> <47165E55.4020807@krogh.cc> <12597.1192662093@sss.pgh.pa.us> In-Reply-To: <12597.1192662093@sss.pgh.pa.us> X-Enigmail-Version: 0.94.2.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/160 X-Sequence-Number: 27074 Tom Lane wrote: > Jesper Krogh writes: >> Tom Lane wrote: >>> Drop the constraints in the source database. > >> That would be my workaround for the problem. But isn't it somehow >> desirable that pg_dumpall | psql "allways" would work? > > Well, sure. The reason why this sort of thing is deprecated is exactly > that the database can't promise it will work all the time. The DB has > no way to know that your constraints do something they're not supposed > to, and in particular no way to infer that there's a specific data > loading order needed to keep the constraint from failing. No, it should just prospone, both constraints and triggers to the end of the dump (no magic here). This is not different from triggers in general. When you need to dump with --disable-triggers, you by yourself ensure that your contraint was fulfilled before you dumped / disabled triggeres. Then load the data and enabled the triggers again. The database cant enable usual foreing-key constraints before it have the complete dataset either. In this case the DB cant promise anything either. (unless it actually runs the complete set of checks at "enable time", which it might as well should be able to do with the stuff in the triggers. The only difference is that this is per-table(disable-dump-enable) and not dump-wide. > We do allow you to do it, but if it breaks you get to keep both pieces. What would be the preferred way of enabling some sort of timetravelling on a database, where the usual foreing-key constraints (data-integrity) should be preserved? Our setup is inspired by: http://www.varlena.com/GeneralBits/122.php But it nicely jumps straight over foreing-keys. -- Jesper Krogh, jesper@krogh.cc