[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Bug in pg_dump/pg_dumpall handling of CONSTRAINTS?
From:       "Michael A. Koerber" <mak () ll ! mit ! edu>
Date:       1998-10-22 13:03:56
[Download RAW message or body]

When using "pg_dumpall" and a subsequent "psql -e" to rebuild the data base
the process failed.  The failure was due to the "pg_dumpall" (and "pg_dump") handling
of CONSTRAINTS.  Details are provided below.

Is this a known bug?  Is there a patch for it?

tnx,

mike

----------------------------------------------------------------------
Example 1:

The table was created as follows:
create table tew (
                        -- Add check to ensure we are in a valid TEWID range.
        tewid           int2 check (0 <= tewid and tewid <= 32767),

                        -- Limit the sequence number to the range of 0..2047
        seq             int2 check (0 <= seq and seq <= 2047),
        real            int4,
        imag            int4,

                        -- this is the original data before scaling and modulation
        realo           float8,
        imago           float8,

        -- Add a constraint to ensure that there is only one set of weights
        -- entered against a given tewid
        CONSTRAINT tew_one_set_constraint UNIQUE(tewid, seq)
);

The "pg_dumpall" output is as follows (I added the formating for readability):
CREATE TABLE tew (
	tewid int2, seq int2, real int4, imag int4, realo float8, imago float8
	) 
	CONSTRAINT tew_tewid CHECK 0 <= tewid 
	AND tewid <= 32767,  CONSTRAINT tew_seq CHECK 0 <= seq AND seq <= 2047;

----------------------------------------------------------------------
Example 2:

The table was created as follows:
create table tew_ped (

                        -- Integrity check is for radar being hax or lrir (lower case)
        radar           char8 not null check (char8regexeq(radar,'(hax|lrir)')),

        rel_date        timestamp default current_timestamp,
        test_date       datetime not null,

                        -- Note that the sequence is int4, need to cast to int2
        tewid           int2 primary key default i4toi2(nextval('tewid_seq')),

                        -- Object number used to create these TEWs or the type
                        -- of weight, e.g., ham, uni, tst
        obj             char8 not null,

                        -- check that the wf is between 0 and 31
        wf              int not null check (0 <= wf and wf <= 31),

                        -- include a weight type, (e.g., uni, ham, tew, tst, kaiser)
        typ             char8 not null,
        fft_len         int not null check (fft_len > 0),
        no_valid        int not null check (no_valid <= fft_len)
        );

The "pg_dumpall" output is as follows (I added formating for readability):
CREATE TABLE tew_ped (
	radar char8 NOT NULL, rel_date timestamp DEFAULT now(), test_date datetime NOT NULL, 
	tewid int2 DEFAULT i4toi2 ( nextval ( 'tewid_seq' ) ) NOT NULL, obj char8 NOT NULL, 
	wf int4 NOT NULL, typ char8 NOT NULL, fft_len int4 NOT NULL, no_valid int4 NOT NULL
	) 
	CONSTRAINT tew_ped_radar CHECK char8regexeq ( radar , '(hax|lrir)' ),  
	CONSTRAINT tew_ped_wf CHECK 0 <= wf AND wf <= 31,  
	CONSTRAINT tew_ped_fft_len CHECK fft_len > 0,  
	CONSTRAINT tew_ped_no_valid CHECK no_valid <= fft_len;

[prev in list] [next in list] [prev in thread] [next in thread]