Re: [BUGS] BUG #5492: Query performs slowly and sequence corrupted

Thank you very much for your answer below.

Just to keep you in the picture, the first problem has been solved with a
FULL VACUUMING of the database.

With reference to the sequence, I experience this problem when I operate
with pgAdmin III. It seems that the sequence START value is replaced every
time I refresh a database object, i.e. the schema containing the mentioned
sequence.
If I open a session with the Query tool and try to update the sequence
with SETVAL function the returned value is correct. When I return back to
pgAdmin III and look at the sequence object I see that the START value is
different from the return value I obtained from the SETVAL function. This
is a nonsense.
I trust now the problem is clearer than yesterday.

Thank you very much in advance for your feedback.

Kind regards

Piergiorgio Buongiovanni

Robert Haas <robertmhaas(at)gmail(dot)com>
09/06/2010 05.04

Per
Greg Stark <gsstark(at)mit(dot)edu>
CC
Piergiorgio Buongiovanni <piergiorgio(dot)buongiovanni(at)netspa(dot)it>,
pgsql-bugs(at)postgresql(dot)org
Oggetto
Re: [BUGS] BUG #5492: Query performs slowly and sequence corrupted

On Mon, Jun 7, 2010 at 5:33 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni
> <piergiorgio(dot)buongiovanni(at)netspa(dot)it> wrote:
>> I reused the previous command to re-set the sequence value to the right
one,
>> but I see that the START value is now 59100. I reused the previous
command
>> another time and the START value is now 30440.
>>
>> I think this is a bug. I have a lot of problems with this sequence.
>
> Sequences wouldn't directly affect retrieval times. But one way you
> could get both of these symptoms is by having an application which
> inserts many rows but aborts and rolls back the inserts without
> committing. Perhaps a large copy which is interrupted. That would fill
> the table with garbage dead records which could slow down retrieval
> depending on the access method and also increase the sequence value.

If this is what happened, CLUSTER on the table might be enough to fix
the problem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- Disclaimer --

This message contains information which may be confidential. Unless you are the addressee (or authorized to receive for the addressee),
you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received the message
in error, please contact the sender by e-mail and delete the message. Many thanks.

Il presente messaggio contiene informazioni di carattere riservato. Qualora non foste il destinatario (o autorizzato dallo stesso al ricevimento)
non usate, copiate o rivelate il presente messaggio o le informazioni contenute. Se avete ricevuto il messaggio per errore, Vi preghiamo di
cancellarlo e avvisare il mittente tramite e-mail. Grazie.