Re: database not enforcing unqiue constriant

["Merlin Moncure" <mmoncure(at)gmail(dot)com>]

On 10/27/06, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> > 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).

right, i think he was talking about the wraparound issue. definately
does not apply here.

> 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'm hoping this is the case.  When 8.2 comes out I'm going to upgrade
their servers to that version and hope for the best.

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

right.  Since these are gererally not 24 hour operations, vacuum is
performed regularly on a schedule.  Also, I am going to implement a
sweep which checks each table for duplicates on each constraint.

Since this is a converted ISAM system, the query volume is enormous
but the data turnover is not.  Pessimistic locks are enforced with the
userlock module.  Statements executed over ExecPrepared 100% of the
time.

merlin