Thread: database not enforcing unqiue constriant
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? merlin
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.
Sincerely,
Joshua D. Drake
>
> merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
On 10/27/06, Joshua D. Drake <jd@commandprompt.com> 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. iirc vacuum was set up to run weekly on cron. based on turnover which was high but not super high that seemed appropriate. merlin
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.
On 10/27/06, Alvaro Herrera <alvherre@commandprompt.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