Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 528689FBAED for ; Wed, 17 Oct 2007 16:11:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 66091-07 for ; Wed, 17 Oct 2007 16:11:33 -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 8480F9FBAEB for ; Wed, 17 Oct 2007 16:11:33 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by shrek.krogh.cc (Postfix) with ESMTP id 11BF33BF2AE for ; Wed, 17 Oct 2007 21:11:32 +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 a-stpDS8D6Vu for ; Wed, 17 Oct 2007 21:11:21 +0200 (CEST) Received: from [90.184.13.233] (unknown [90.184.13.233]) by shrek.krogh.cc (Postfix) with ESMTP id A7F073BF2A8 for ; Wed, 17 Oct 2007 21:11:21 +0200 (CEST) Message-ID: <47165E55.4020807@krogh.cc> Date: Wed, 17 Oct 2007 21:11:17 +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> In-Reply-To: <7031.1192637434@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/157 X-Sequence-Number: 27071 Tom Lane wrote: > Jesper Krogh writes: >> The tables are running a "home-made" timetravelling feature where a >> contraint on the table implements the foreing keys on the table. > > You mean you have check constraints that do selects on other tables? Yes. We do.. we have to .. in order to perserve the foreing-keys. What we have in short is: Added two timestamps on each columns, starttime,endtime. When endtime is "null" the record is "current" and a view exposes this from the table. The foreing keys are then implemented using contraints that look up the id in the foreing table and ensures that there is a record where the endtime is null present > You have just found out (one reason) why that's a bad idea. Do you have any other reasons? >> How can I instruct pg_dumpall to turn off these constriants during >> dump/restore? > > 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? Jesper -- Jesper Krogh, jesper@krogh.cc