Thread

1. pg_dump and CHECK constraints

   Curt Sampson <cjs@cynic.net> — 2004-01-26T13:31:16Z

   I notice that pg_dump is still dumping CHECK constraints with the table,
   rather than at the very end, as it does with all the other constraints.
   As discussed in bug report #787, at
   
       http://archives.postgresql.org/pgsql-bugs/2002-09/msg00278.php
   
   this breaks your restore if your CHECK constraint uses a user-defined
   function.
   
       1. Does anybody have any plans to fix this in the very near future?
   
       2. If not, is there something that makes it particularly hard to
       fix? I notice, Tom, that in your reply to that bug report you
       intimated that this wasn't an easy fix, but I don't see why CHECK
       constraints couldn't be added at the end of the dump, just as all
       the other constraints are. Presumably, your message being late 2002,
       this was before pg_dump was modified to re-order stuff?
   
       3. If we created a patch for this at my work, would it be accepted?
   
   I dunno...this looks really easy to me....
   
   cjs
   -- 
   Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.NetBSD.org
       Don't you know, in this new Dark Age, we're all light.  --XTC
   
   

2. Re: pg_dump and CHECK constraints

   Bruce Momjian <pgman@candle.pha.pa.us> — 2004-02-12T16:36:21Z

   Curt Sampson wrote:
   > 
   > I notice that pg_dump is still dumping CHECK constraints with the table,
   > rather than at the very end, as it does with all the other constraints.
   > As discussed in bug report #787, at
   > 
   >     http://archives.postgresql.org/pgsql-bugs/2002-09/msg00278.php
   > 
   > this breaks your restore if your CHECK constraint uses a user-defined
   > function.
   > 
   >     1. Does anybody have any plans to fix this in the very near future?
   > 
   >     2. If not, is there something that makes it particularly hard to
   >     fix? I notice, Tom, that in your reply to that bug report you
   >     intimated that this wasn't an easy fix, but I don't see why CHECK
   >     constraints couldn't be added at the end of the dump, just as all
   >     the other constraints are. Presumably, your message being late 2002,
   >     this was before pg_dump was modified to re-order stuff?
   > 
   >     3. If we created a patch for this at my work, would it be accepted?
   > 
   > I dunno...this looks really easy to me....
   
   Please check CVS.  I think Tom did some big work on dependency dumping. 
   If I do:
   	
   	test=> CREATE FUNCTION func(INT) returns bool AS 'SELECT TRUE'
   	test->  LANGUAGE 'SQL';
   	CREATE FUNCTION
   	test=> CREATE TABLE yy (x INT CHECK (func(x) = TRUE));
   	CREATE TABLE
   	test=> INSERT INTO yy VALUES (1);
   	INSERT 17576 1
   
   and in pg_dump output I see:
   	
   	--
   	-- Name: func(integer); Type: FUNCTION; Schema: public; Owner: postgres
   	--
   	
   	CREATE FUNCTION func(integer) RETURNS boolean
   	    AS 'select true'
   	    LANGUAGE sql;
   	
   	
   	--
   	-- Name: yy; Type: TABLE; Schema: public; Owner: postgres
   	--
   	
   	CREATE TABLE yy (
   	    x integer,
   	    CONSTRAINT yy_x CHECK ((func(x) = true))
   	) WITH OIDS;
   	
   so this might be fixed in CVS already.
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 359-1001
     +  If your life is a hard drive,     |  13 Roberts Road
     +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073