Re: database not enforcing unqiue constriant

Joshua D. Drake wrote:
> Merlin Moncure wrote:
> > My previous employer contacted me today. Apparently they are having
> > an increasing frequency of occurances where they disocover violations
> > of the primary key constraint not being caught by the database. This
> > system is an ISAM emulation system, and it relies heavily on the
> > database throwing unique constraint violations to approximately
> > provide a cobol rewrite statement.
> >
> > These errors are never caught when they occur. One way they are
> > caught is during a dump/reload, the record fails to reinsert.
> >
> > I brought this up around a year ago, and the issue was never
> > successfully resolved. These are windows systems running PostgreSQL
> > 8.0.x.
> >
> > any thoughts?
>
> Do they vacuum enough? I have seen problems with PostgreSQL (albeit not
> since 7.3) where a unique constraint would not enforce because of index
> bloat.

Huh?? This would qualify as a serious bug. Failure to vacuum should
bring performance loss, but not functionality loss (modulo the Xid
wraparound issue).

I do remember vaguely the failure Merlin alludes to, and IIRC it has
been reported a couple of times by other people but has never been
resolved because it was awfully difficult to reproduce. Maybe it has
something to do with the btree bug that Tom diagnosed on Wednesday? The
uniqueness-checking code is ... weird.

I guess if it was the same bug, you could not vacuum the table, which I
assume you do regularly.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.