| Lists: | pgsql-general |
|---|
| From: | "Aaron Harsh" <ajh(at)rentrak(dot)com> |
|---|---|
| To: | <pgsql-general(at)PostgreSQL(dot)org> |
| Subject: | oids and pg_class_oid_index constraint violations |
| Date: | 2005-08-03 19:35:26 |
| Message-ID: | s2f0ba35.043@rtk_mail_1.rentrak.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
We've just recently started seeing sporadic constraint violations on system tables. For example:
duplicate key violates unique constraint "pg_class_oid_index" [for Statement "CREATE TEMPORARY TABLE...
and
duplicate key violates unique constraint "pg_toast_4292803267_index" [for Statement "INSERT INTO...
and the occasional
unexpected chunk number 0 (expected 1) for toast value
I suspect that the problem is due to oid reuse, but I'd like to get a second opinion. We've just recently grown to a billion tuples in our database (with all our tables built with oids), and the volume of activity makes me think it's likely that the majority of records have been updated three or four times.
Our current plan for dealing with the problem is to 'SET WITHOUT OIDS' on our tables, then dump & restore the database.
Does it seem reasonable that oid reuse could cause the constraint violations? Does it seem likely that this would fix the problem? Is there an easier way to solve the problem?
Thanks in advance
--
Aaron Harsh
ajh(at)rentrak(dot)com
503-284-7581 x347
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "Aaron Harsh" <ajh(at)rentrak(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: oids and pg_class_oid_index constraint violations |
| Date: | 2005-08-03 20:33:29 |
| Message-ID: | 3785.1123101209@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
"Aaron Harsh" <ajh(at)rentrak(dot)com> writes:
> We've just recently started seeing sporadic constraint violations on system tables. For example:
> duplicate key violates unique constraint "pg_class_oid_index" [for Statement "CREATE TEMPORARY TABLE...
OID wraparound would explain that ...
> and the occasional
> unexpected chunk number 0 (expected 1) for toast value
... but not that. The latter might possibly be due to a corrupt index
on a toast table. If you have a reproducible way of causing it, I'd
definitely love to see it.
regards, tom lane