| Lists: | pgsql-bugs |
|---|
| From: | "Piergiorgio Buongiovanni" <p(dot)buongiovanni(at)net-international(dot)com> |
|---|---|
| To: | pgsql-bugs(at)postgresql(dot)org |
| Subject: | BUG #5476: sequence corruption |
| Date: | 2010-05-27 09:10:20 |
| Message-ID: | 201005270910.o4R9AKDd038676@wwwmaster.postgresql.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5476
Logged by: Piergiorgio Buongiovanni
Email address: p(dot)buongiovanni(at)net-international(dot)com
PostgreSQL version: 8.3.4
Operating system: CentOS (Redhat 5.4)
Description: sequence corruption
Details:
We experimented a sequence corruption on a table:
If we now look at the sequence data using pgAdmin version 1.8.4, we see the
following:
CREATE SEQUENCE business.subject_isid_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 58827944
CACHE 1;
ALTER TABLE business.subject_isid_seq OWNER TO netdw_owner;
The problem is that now the search on the table business.Subject (where the
sequence is used) performs very slowly. It seems the data are corrupted but
at the end I can see that there is a record created with the number 463663
instead of 57. As you can see the next value will be used is 58827944.
Which is the problem? Can I recover this situation and restore the right
sequence value? I tried to set the START value to 58 but, as you can see,
the value changed.
| From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
|---|---|
| To: | pgsql-bugs(at)postgresql(dot)org |
| Subject: | Re: BUG #5476: sequence corruption |
| Date: | 2010-05-28 10:26:03 |
| Message-ID: | hto5nr$qf1$2@reversiblemaps.ath.cx |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
On 2010-05-27, Piergiorgio Buongiovanni <p(dot)buongiovanni(at)net-international(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5476
> Logged by: Piergiorgio Buongiovanni
> Email address: p(dot)buongiovanni(at)net-international(dot)com
> PostgreSQL version: 8.3.4
> Operating system: CentOS (Redhat 5.4)
> Description: sequence corruption
> Details:
>
> We experimented a sequence corruption on a table:
> If we now look at the sequence data using pgAdmin version 1.8.4, we see the
> following:
>
> CREATE SEQUENCE business.subject_isid_seq
> INCREMENT 1
> MINVALUE 1
> MAXVALUE 9223372036854775807
> START 58827944
> CACHE 1;
> ALTER TABLE business.subject_isid_seq OWNER TO netdw_owner;
>
> The problem is that now the search on the table business.Subject (where the
> sequence is used) performs very slowly. It seems the data are corrupted but
> at the end I can see that there is a record created with the number 463663
> instead of 57. As you can see the next value will be used is 58827944.
> Which is the problem? Can I recover this situation and restore the right
> sequence value? I tried to set the START value to 58 but, as you can see,
> the value changed.
have you tried vacuum full on the table? I get the feeling that it
may have several million deleted rows.
VACUUM FULL subject;