Re: Facing error while restoring the database

On Tue, Mar 27, 2012 at 7:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> > pg_dump does not resolve dependencies, it avoids problems by adding
> > constraints after inserting the data.
>
> > It seems that this is not done for CHECK constraints, however - they are
> > added when the table is defined.
>
> > I think that this is a bug.
>
> It is not a bug; it is an unsafe and unsupported use of CHECK
> constraints.
>
> Using a CHECK to enforce a cross-row constraint is fundamentally broken,
> because there is no way for the database to know that the constraint
> might be violated after the *other* row is modified. In the example
> at hand, a change in sample_one.param_names could leave the constraint
> unsatisfied for some rows in sample, but the database wouldn't detect
> that.
>

In my case I won't allow anyone to insert/modify the rows of sample_one
table. I have already inserted some rows in sample_one table where I
want one constraint is number of array elements of sample_one.param_names
and sample.params must be same. That's why I have created
CHECK constraint in sample table. User can insert, modify and delete the
rows of sample table, so I don't want any mismatch in the number of
array elements of sample_one.param_names and sample.params table.

> I think the right fix here would be to redesign the table schema so that
> the required cross-table constraint could be expressed as a foreign key.
> We don't have enough context to guess at what a better design would
> look like, though.
>
> regards, tom lane
>

--
*Akshay Joshi
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20-3058-9522
Mobile: +91 976-788-8246*