Thread: Corrupted sequences
Got 2 corrupted sequences, that I have discovered so far, and fixed them with 'setval'. Is there a way to check all sequences in a database? Something simmilar to reindex but for sequences? Running 7.2.4 (at my ISP so don't have a choice there..)
Francisco Reyes <lists@natserv.com> writes:
> Got 2 corrupted sequences, that I have discovered so far, and fixed them
> with 'setval'. Is there a way to check all sequences in a database?
?? Define "corrupted" ...
regards, tom lane
On Fri, 14 Mar 2003, Francisco Reyes wrote: > Got 2 corrupted sequences, that I have discovered so far, and fixed them > with 'setval'. Is there a way to check all sequences in a database? > Something simmilar to reindex but for sequences? > > > Running 7.2.4 (at my ISP so don't have a choice there..) You need to demand that your service providers test their hardware. When things randomly get corrupted in Postgresql it is about 95% of the time or more that you have either a bad disk or bad memory. Postgresql is good, but it can't overcome broken hardware.
On Fri, 14 Mar 2003, Tom Lane wrote: > Francisco Reyes <lists@natserv.com> writes: > > Got 2 corrupted sequences, that I have discovered so far, and fixed them > > with 'setval'. Is there a way to check all sequences in a database? > > ?? Define "corrupted" ... One returned a number, 12, much lower than the next value it should have used 3,706. Another set of sequences are returning sequence_name.currval is not yet defined in this session. If I use setcurrval(sequence,nextvalue) it fixes the problem.
On Fri, 14 Mar 2003, scott.marlowe wrote: > > Got 2 corrupted sequences, that I have discovered so far, and fixed them > > with 'setval'. Is there a way to check all sequences in a database? > > Something simmilar to reindex but for sequences? > > > > > > Running 7.2.4 (at my ISP so don't have a choice there..) > > You need to demand that your service providers test their hardware. When > things randomly get corrupted in Postgresql it is about 95% of the time or > more that you have either a bad disk or bad memory. Postgresql is good, > but it can't overcome broken hardware. :-) Ok I will tell Marc. (using hub.org)
On Fri, 14 Mar 2003, scott.marlowe wrote:
> On Fri, 14 Mar 2003, Francisco Reyes wrote:
>
> > Got 2 corrupted sequences, that I have discovered so far, and fixed them
> > with 'setval'. Is there a way to check all sequences in a database?
> > Something simmilar to reindex but for sequences?
> >
> >
> > Running 7.2.4 (at my ISP so don't have a choice there..)
>
> You need to demand that your service providers test their hardware. When
> things randomly get corrupted in Postgresql it is about 95% of the time or
> more that you have either a bad disk or bad memory. Postgresql is good,
> but it can't overcome broken hardware.
The first could be a problem. Are you sure the sequence was being used to
update the table you were inserting it into? I.e. was the table loaded by
bulk load, so the sequence was set right? or has it been updated properly
by "insert into table (field1,field2,id) values ('a','b',DEFAULT)"
The second is normal. currval has no value within a tranaction until
after setval or nextval.
In those cases, your sequences aren't corrupted, they're just incorrect
because they weren't accessed the right way to stay in sync with the
table.
On Fri, 14 Mar 2003, scott.marlowe wrote:
> The first could be a problem. Are you sure the sequence was being used to
> update the table you were inserting it into?
Yes. This site has been working for some time. I got a note from the ISP
that they were moving databases. I think it has to do with that. Opened a
ticket with them asking if that domain's DB was moved.
> bulk load, so the sequence was set right? or has it been updated properly
> by "insert into table (field1,field2,id) values ('a','b',DEFAULT)"
I am leaning towards the thought that they moved the database and did it
wrong somehow. Did a 'select currval()' for every sequence and all, except
the ones I have fixed, gave the error 'is not yet defined in this
session'.
All these sequences are from primary keys. I am thinking maybe the easiest
way to fix it may be to drop and re-create the constraint. It's only 25
tables and have only fixed 4 manually so I am just looking for a way to
automate the rest.
I would back up things whenever my ISP says something like, "We're going
to ..."
Francisco Reyes wrote:
> On Fri, 14 Mar 2003, scott.marlowe wrote:
>
>
>>The first could be a problem. Are you sure the sequence was being used to
>>update the table you were inserting it into?
>
>
> Yes. This site has been working for some time. I got a note from the ISP
> that they were moving databases. I think it has to do with that. Opened a
> ticket with them asking if that domain's DB was moved.
>
>
>>bulk load, so the sequence was set right? or has it been updated properly
>>by "insert into table (field1,field2,id) values ('a','b',DEFAULT)"
>
>
>
> I am leaning towards the thought that they moved the database and did it
> wrong somehow. Did a 'select currval()' for every sequence and all, except
> the ones I have fixed, gave the error 'is not yet defined in this
> session'.
>
> All these sequences are from primary keys. I am thinking maybe the easiest
> way to fix it may be to drop and re-create the constraint. It's only 25
> tables and have only fixed 4 manually so I am just looking for a way to
> automate the rest.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
On Fri, 14 Mar 2003, Dennis Gearon wrote: > I would back up things whenever my ISP says something like, "We're going > to ..." I would have done a backup if I had gotten the note before they did it. :-( They had some serious crash about a week ago so i figure they may be doing certain things on a "fast-track" due to that. However I think I will make a case that more warning would have been great.